SQL Deadlock

  • Hello guys,

    I am facing a deadlock problem in one of my processes.

    We've two jobs JOB1 and JOB2, both executes at the same time and uses the same table.

    JOB1 - Inserts data to the table.

    JOB2 - Updates few records in the table, but this data is different from what JOB1 is inserting i.e. JOB2 updates data where a column1 value is 0 while JOB1 only inserts records where column1 value is 1. (There's another job which inserts data where column1 is 0)

    So, while execution sometimes I am getting Deadlock while executing the JOB2 when it is trying to update the table.

    Error: "Transaction was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

    Does insertion using SSIS create a full table lock?

    Please suggest, how could I avoid such deadlock situation.

    TA

    ____________________________________________________________

    AP
  • Please post the deadlock graph and details of the two queries involved.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sorry, I don't have the deadlock graph available.

    In JOB1 - I have a file to table load via SSIS (fast table load)

    In JOB2 - I am fetching some data from this table with(nolock) using SSIS and after processing I am updating the processed records.

    ____________________________________________________________

    AP
  • You might have it. Check your system_health extended event session. You can use this query [/url]to take a look.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Please can you get the deadlock graph and post it? Can't diagnose a deadlock on vague descriptions of processes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply