Post reply

MS SQL Server - Frequent Deadlock Issues

  • November 14, 2017 at 10:36 pm

    #340448

    Target Database : MS SQL Server 2012

    Requirement : Maintain summary of user’s workload in a table. Following is the table structure used to maintain this summary. Table is getting updated on modification of status of workitem assigned to user.

    PARTICIPANT_ID varchar NO PK, Unique non clustered index
    OFFERED_COUNT numeric YES
    ALLOCATED_COUNT numeric YES
    STARTED_COUNT numeric YES
    SUSPENDED_COUNT numeric YES

    Issue : We are facing frequent deadlocks on this table. Herewith I have attached deadlock graph for reference.

    Investigation done so far :

    • Target Rows (Updates) in deadlocks are independent to each other so no question of one transaction waiting for other to relinquish lock. Even inserts to the table becomes part of deadlock.
    • Same transaction is working with similar concurrent load in Oracle.
    • Suspecting that table is getting locked instead of particular row, we even tried to disable escalation of locks at a table using following command but deadlocks persisted.


    ALTER     TABLE Table_name SET (LOCK_ESCALATION DISABLE)


    Deadlock Graph 

    <deadlock>
    <victim-list>
    <victimProcess id="process133f03d498" />
    </victim-list>
    <process-list>
    <process id="process133f03d498" taskpriority="0" logused="3308" waitresource="RID: 11:1:819250:71" waittime="2377" ownerId="29578701" transactionname="implicit_transaction" lasttranstarted="2017-11-09T09:29:43.397" XDES="0x10b0b716a8" lockMode="U" schedulerid="6" kpid="7332" status="suspended" spid="210" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-11-09T09:29:45.977" lastbatchcompleted="2017-11-09T09:29:45.397" lastattention="1900-01-01T00:00:00.397" clientapp="jTDS" hostname="PAWSPPAS02" hostpid="123" loginname="STG_SUITE_SSO" isolationlevel="read committed (2)" xactid="29578701" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
     <executionStack>
      <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000ee409c1fd278abb7f476707399656cbedec9d1960000000000000000000000000000000000000000">
    update [RS_WI_PARTICIPANT_SUMM] set [ALLOCATED_COUNT]=[ALLOCATED_COUNT]-1 where [PARTICIPANT_ID]= @P0  </frame>
     </executionStack>
     <inputbuf>
    (@P0 nvarchar(4000))update [RS_WI_PARTICIPANT_SUMM] set [ALLOCATED_COUNT]=[ALLOCATED_COUNT]-1 where [PARTICIPANT_ID]= @P0  </inputbuf>
    </process>
    <process id="processc70140188" taskpriority="0" logused="85392" waitresource="RID: 11:1:819250:30" waittime="2196" ownerId="29574187" transactionname="implicit_transaction" lasttranstarted="2017-11-09T09:29:16.427" XDES="0x1150f5d6a8" lockMode="U" schedulerid="4" kpid="384" status="suspended" spid="141" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-11-09T09:29:46.157" lastbatchcompleted="2017-11-09T09:29:45.470" lastattention="1900-01-01T00:00:00.470" clientapp="jTDS" hostname="PAWSPPAS02" hostpid="123" loginname="STG_SUITE_SSO" isolationlevel="read committed (2)" xactid="29574187" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
     <executionStack>
      <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x020000001c3c261d27cfa98e13a9cb06f6c30e40cfcaa4a50000000000000000000000000000000000000000">
    update [RS_WI_PARTICIPANT_SUMM] set [ALLOCATED_COUNT]=[ALLOCATED_COUNT]+1 where [PARTICIPANT_ID]= @P0  </frame>
     </executionStack>
     <inputbuf>
    (@P0 nvarchar(4000))update [RS_WI_PARTICIPANT_SUMM] set [ALLOCATED_COUNT]=[ALLOCATED_COUNT]+1 where [PARTICIPANT_ID]= @P0  </inputbuf>
    </process>
    </process-list>
    <resource-list>
    <ridlock fileid="1" pageid="819250" dbid="11" objectname="STG_SUITE_SSO.dbo.RS_WI_PARTICIPANT_SUMM" id="lock1236714900" mode="X" associatedObjectId="72057599198953472">
     <owner-list>
      <owner id="processc70140188" mode="X" />
     </owner-list>
     <waiter-list>
      <waiter id="process133f03d498" mode="U" requestType="wait" />
     </waiter-list>
    </ridlock>
    <ridlock fileid="1" pageid="819250" dbid="11" objectname="STG_SUITE_SSO.dbo.RS_WI_PARTICIPANT_SUMM" id="lockdacb14f00" mode="X" associatedObjectId="72057599198953472">
     <owner-list>
      <owner id="process133f03d498" mode="X" />
     </owner-list>
     <waiter-list>
      <waiter id="processc70140188" mode="U" requestType="wait" />
     </waiter-list>
    </ridlock>
    </resource-list>
    </deadlock>

  • November 15, 2017 at 1:12 am

    #1968121

    To start with, the deadlocks are on row-level locks, not table level, so lock escalation will have no effect here. Please turn it back on.

    The key, I suspect, is this:

    transactionname="implicit_transaction"

    That means that a transaction gets automatically started when a statement runs, and only gets committed when an explicit COMMIT TRANSACTION is run.
    This is not the default in SQL Server, so someone would have had to either set this in the application (session-level setting), or by SET IMPLICIT TRANSACTIONS ON

    I suspect that transactions are getting started, and not committed, and since locks taken from data modifications are held until the end of the transaction, you're having long-running transactions holding locks longer than expected, and hence causing deadlocks.
    I would recommend removing the implicit transaction settings and explicitly creating and committing transactions where necessary, and allowing autocommit where they're not.

    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

  • November 15, 2017 at 11:23 pm

    #1968315

    GilaMonster - Wednesday, November 15, 2017 1:12 AM

    To start with, the deadlocks are on row-level locks, not table level, so lock escalation will have no effect here. Please turn it back on.

    The key, I suspect, is this:

    transactionname="implicit_transaction"

    That means that a transaction gets automatically started when a statement runs, and only gets committed when an explicit COMMIT TRANSACTION is run.
    This is not the default in SQL Server, so someone would have had to either set this in the application (session-level setting), or by SET IMPLICIT TRANSACTIONS ON

    I suspect that transactions are getting started, and not committed, and since locks taken from data modifications are held until the end of the transaction, you're having long-running transactions holding locks longer than expected, and hence causing deadlocks.
    I would recommend removing the implicit transaction settings and explicitly creating and committing transactions where necessary, and allowing autocommit where they're not.

    Thanks GilaMonster for detailed analysis.

    • Actually, we are using jTDS JDBC driver via Java Application to execute this queries. I have verified the code that if autocommit is set to false on JDBC connection, jTDS driver in turn fires SET IMPLICIT TRANSACTIONS ON command to the database. Implicit transaction starts on database for any sql statement fired afterwards. This behavior of jTDS driver is similar to Microsoft SQL Server JDBC driver. 
    • Transaction boundaries are small and should not take more than 1 second between Service-DB layer.
    • One more point to note that we have tested same functionality on Oracle with similar load (50 concurrent threads) and it works well without any deadlocks.

Viewing 3 posts - 1 through 2 (of 2 total)

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