none
Deadlock query

    Question

  • Hi,

    Please see resource list from my deadlock below, I am trying to understand how the deadlock occurs, the lock X becomes IX, is it lock escalation? Can someone give some hints to fix this kind of deadlock. The process 98 reads data using SELECT query while 88 try to update . There is no explicit transaction defined. Read committed mode.

      </process-list>
      <resource-list>
       <keylock hobtid="72057595707392000" dbid="5" objectname="Elements" indexname="PK_xx" id="lock82ca10700" mode="X" associatedObjectId="72057595707392000">
        <owner-list>
         <owner id="process4606188" mode="X" />
        </owner-list>
        <waiter-list>
         <waiter id="process4f1cf498" mode="S" requestType="wait" />
        </waiter-list>
       </keylock>
       <pagelock fileid="1" pageid="10544198" dbid="5" subresource="FULL" objectname="Elements" id="lock3bd6700" mode="S" associatedObjectId="720575819458560">
        <owner-list>
         <owner id="process4f1cf498" mode="S" />
        </owner-list>
        <waiter-list>
         <waiter id="process4606188" mode="IX" requestType="wait" />
        </waiter-list>
       </pagelock>
      </resource-list>


    Thnx,

    Ashru



    MCTS, http://asharafaliptb.wordpress.com

    Thursday, May 10, 2018 12:52 PM

All replies

  • Please show the full deadlock report including the SQL statements.

    Of course this shouldn't happen with just a SELECT on one side.

    But the resource list you show includes the magic word "pagelock".  That is probably the immediate problem.  Whichever statement that comes from is trying to lock a bunch of rows, or at least the estimator thinks it is, and has chosen page locking.  That causes many deadlocks.

    Josh

    Thursday, May 10, 2018 3:25 PM
  • No, X does not become IX. The UPDATE process to hold an X lock on one key value in the table Elements. It is now aiming at updating another key value, and to do this, it wants to take an intent lock on page level.

    The SELECT process already holds a shared lock on this page, which is why the UPDATE process is blocked. At the same time the SELECT process wants to update the key value the UPDATE process already has updated.

    It is not possible to give surefire suggestions on how to resolve this deadlock based on this small amount of information. However, it could be that the SELECT process is taking page locks, because there is no suitable index to seek, or the query is written in such a way that a seek of an existing index is not possible. This leads to scans, which often result in page locks.

    Thursday, May 10, 2018 6:49 PM
  • At the same time the SELECT process wants to update the key value the UPDATE process already has updated.

    Why would a "select" be updating anything?

    Josh

    Thursday, May 10, 2018 10:55 PM
  • Josh, Erl,

    It is actually, SELECT INTO vs UPDATE. The SELECT INTO #Temp is somewhat big query with CTE, but it is not updating anything on base table. I have restrictions in posting complete query here...  

    the update is simple update:

         UPDATE
           Elements
        SET
           Element_Status=@Status ,
           Updte_By = @User,
           Updte_Dt = GETDATE()
         WHERE 
           Id = @_Id -- Id is PK

    Thanks,

    Ashru


    MCTS, http://asharafaliptb.wordpress.com



    • Edited by Ashru Friday, May 11, 2018 6:34 AM
    Friday, May 11, 2018 5:56 AM
  • It is actually, SELECT INTO vs UPDATE. The SELECT INTO #Temp is somewhat big query with CTE, but it is not updating anything on base table.

    Interesting.  And you say you're running in standard read committed isolation?  I didn't think that even SELECT INTO would take a non-shared lock on anything, but it sounds like it is taking your IX locks on pages!  Maybe someone else knows more about that.  I've never run into it before.  I suppose you could add "with (tablockx)" to the select and fix it that way, of course that will block out others but it will do so safely.

    Josh

    Friday, May 11, 2018 7:41 AM
  • At the same time the SELECT process wants to update the key value the UPDATE process already has updated.

    Why would a "select" be updating anything?

    Sorry, I meant to say "read", not update. And it is not necessarily the key value it wants to read, but something access through that key.

    Friday, May 11, 2018 8:33 AM
  • Thank you Josh.

    Read committed only, by default. It sounds good giving tablockx option. Note that the process runs SELECT INTO is having S lock only here. I still wounder how the simple UPDATE query caused to become X to IX, why don't let it keep the UPDATE process's lock till the end of UPDATE task. So SELECT INTO will not be taking S lock inbetween.

    Thaks,

    Ashru


    MCTS, http://asharafaliptb.wordpress.com





    • Edited by Ashru Friday, May 11, 2018 10:45 AM
    Friday, May 11, 2018 8:55 AM
  • I still wounder how the simple UPDATE query caused to become X to IX, why don't let it keep the UPDATE process's lock till the end of UPDATE task.

    Again, that did not happen. As I told you the UPDATE first updates one key and gets an X-lock on that key. In then goes to try to update another key, and do that it needs an IX lock on the page, but is blocked. Your UPDATE is by ID, so that it is a single row. However, if any of the columns you update, there are still two keys to update.

    Friday, May 11, 2018 8:46 PM
  • However, if any of the columns you update, there are still two keys to update.

    Thanks Erland. You mean the two key update happens when we update columns having index on it?

    What you think if we SET SNAPSHOT isolation in UPDATE procedure as a resolution. I have other occurrences also with same X to IX deadlocks. Just see below for FYI.

     

    <resource-list>
       <keylock hobtid="720596477112320" dbid="5" objectname="_FileLog" indexname="PK_FileLog" id="locka055a6580" mode="X" associatedObjectId="720596477112320">
        <owner-list>
         <owner id="processa74801868" mode="X" />
        </owner-list>
        <waiter-list>
         <waiter id="process61cd3ecf8" mode="S" requestType="wait" />
        </waiter-list>
       </keylock>
       <pagelock fileid="1" pageid="11547056" dbid="5" subresource="FULL" objectname="_FileLog" id="lock594712680" mode="S" associatedObjectId="57596768026624">
        <owner-list>
         <owner id="process2d842a188" mode="S" />
        </owner-list>
        <waiter-list>
         <waiter id="processa74801868" mode="IX" requestType="wait" />
        </waiter-list>
       </pagelock>
       <exchangeEvent id="Portb2eee1400" WaitType="e_waitPortOpen" nodeId="1">
        <owner-list>
         <owner id="process935de0188" />
        </owner-list>
        <waiter-list>
         <waiter id="process1da7ad498" />
        </waiter-list>
       </exchangeEvent>
       <exchangeEvent id="Portb2eee1400" WaitType="e_waitPortOpen" nodeId="1">
        <owner-list>
         <owner id="process61cd3ecf8" />
        </owner-list>
        <waiter-list>
         <waiter id="process935de0188" />
        </waiter-list>
       </exchangeEvent>
       <exchangeEvent id="Portb2eee1400" WaitType="e_waitPortOpen" nodeId="1">
        <owner-list>
         <owner id="process1da7ad498" />
        </owner-list>
        <waiter-list>
         <waiter id="process2d842a188" />
        </waiter-list>
       </exchangeEvent>
      </resource-list>
      

    Thanks,

    Ashru


    MCTS, http://asharafaliptb.wordpress.com

    Tuesday, May 15, 2018 1:02 PM
  • Yes, SNAPSHOT isolation should resolve the deadlock, as readers will not block writers and vice versa.
    Whether the semantics of snapshot isolation fits your application in other aspects is nothing I can comment on.

    Wednesday, May 16, 2018 7:27 PM