0

We have problems with deadlocks, judging by the logs, two requests simultaneously want to get exclusive access to the table. However, both queries change one record in the table.

The error occurs at high loads. There was a lack of RAM. In this case, we use the READ COMMITTED SNAPSHOT isolation level. I understand that this isolation level is very dependent on temporary tables and if there is a lack of memory, then it will not lead to anything good. But I'm trying to explain why an exclusive table lock occurs, what is the reason?

Error log:

<deadlock>
  <victim-list>
    <victimProcess id="process15869047848" />
    <victimProcess id="process1508af0f088" />
  </victim-list>
  <process-list>
    <process id="process15869047848" taskpriority="0" logused="0" waitresource="OBJECT: 8:398376934:0 " waittime="344" ownerId="1381853939" transactionname="user_transaction" lasttranstarted="2020-12-29T14:08:38.100" XDES="0x15803072430" lockMode="X" schedulerid="6" kpid="2276" status="suspended" spid="130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-12-29T14:08:38.093" lastbatchcompleted="2020-12-29T14:08:38.093" lastattention="1900-01-01T00:00:00.093" clientapp=".Net SqlClient Data Provider" hostname="KITZBUEHEL" hostpid="12192" loginname="eseddadmin" isolationlevel="read committed (2)" xactid="1381853939" currentdb="8" currentdbname="SEDD3_KIO" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
      <executionStack>
        <frame procname="SEDD3_KIO.dbo.spSaveRouteControlInfo" line="39" stmtstart="1978" stmtend="3056" sqlhandle="0x03000800f5d97d180d9d560172ac000001000000000000000000000000000000000000000000000000000000">
update WF4_Routes set
      IsControlled = @IsControlled
     ,UrgencyType = @UrgencyType 
     ,ImportanceType = @ImportanceType 
     ,ControlDate = @ControlDate
     ,ControlAssignmentType = @ControlAssignmentType
     ,ControlState = ISNULL(@ControlState, ControlState)
     ,CompleteDate = case when CompleteDate is null then null else isnull(@ClosingState, CompleteDate) end
     ,State = ISNULL(@State, State)
     ,ModifyDate = GETDATE() 
     ,UserUID = @OperatorUID
    where DocUID = @DocUID and Type = 1 and PRUZ &lt;&gt;    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 8 Object Id = 410900981]   </inputbuf>
    </process>
    <process id="process1508af0f088" taskpriority="0" logused="0" waitresource="OBJECT: 8:398376934:0 " waittime="344" ownerId="1381845648" transactionname="UPDATE" lasttranstarted="2020-12-29T14:08:34.970" XDES="0x1529c41a430" lockMode="X" schedulerid="11" kpid="6748" status="suspended" spid="112" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-12-29T14:08:34.957" lastbatchcompleted="2020-12-29T14:08:34.947" lastattention="2020-12-29T14:03:46.567" clientapp=".Net SqlClient Data Provider" hostname="KITZBUEHEL" hostpid="12192" loginname="eseddadmin" isolationlevel="read committed (2)" xactid="1381845648" currentdb="8" currentdbname="SEDD3_KIO" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
      <executionStack>
        <frame procname="SEDD3_KIO.dbo.spSetDocumentViewed" line="38" stmtstart="2690" stmtend="2940" sqlhandle="0x0300080066d77227034cbf0091ac000001000000000000000000000000000000000000000000000000000000">
update WF4_Routes set RouteVersion = isnull(RouteVersion, 1) + 1 where DocUID = @DocUID and Type = 2 and State in (1, 2, 7, 8    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 8 Object Id = 661837670]   </inputbuf>
    </process>
    <process id="process1586903f468" taskpriority="0" logused="0" waitresource="OBJECT: 8:398376934:0 " waittime="345" ownerId="1381845814" transactionname="user_transaction" lasttranstarted="2020-12-29T14:08:35.470" XDES="0x155c3ab16f0" lockMode="X" schedulerid="5" kpid="10540" status="suspended" spid="97" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-12-29T14:08:35.877" lastbatchcompleted="2020-12-29T14:08:35.457" lastattention="1900-01-01T00:00:00.457" clientapp=".Net SqlClient Data Provider" hostname="KITZBUEHEL" hostpid="12192" loginname="eseddadmin" isolationlevel="read committed (2)" xactid="1381845814" currentdb="8" currentdbname="SEDD3_KIO" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
      <executionStack>
        <frame procname="SEDD3_KIO.dbo.spSetRouteCompleted" line="183" stmtstart="12918" stmtend="13284" sqlhandle="0x03000800ae876e11b1312c018cac000001000000000000000000000000000000000000000000000000000000">
insert into @RoutesToClose (RouteId, DocUID)
                        select R.ID, @docUID
                        from WF4_Routes R with (UPDLOCK)
                        where R.DocUID = @docUID and R.State in (1, 2, 8) and R.Type =    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 8 Object Id = 292456366]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <objectlock lockPartition="0" objid="398376934" subresource="FULL" dbid="8" objectname="SEDD3_KIO.dbo.WF4_Routes" id="lock154ab1c5d00" mode="IX" associatedObjectId="398376934">
      <owner-list>
        <owner id="process1586903f468" mode="IX" />
        <owner id="process1508af0f088" mode="IX" />
        <owner id="process1586903f468" mode="X" requestType="convert" />
        <owner id="process1508af0f088" mode="X" requestType="convert" />
      </owner-list>
      <waiter-list>
        <waiter id="process15869047848" mode="X" requestType="convert" />
      </waiter-list>
    </objectlock>
    <objectlock lockPartition="0" objid="398376934" subresource="FULL" dbid="8" objectname="SEDD3_KIO.dbo.WF4_Routes" id="lock154ab1c5d00" mode="IX" associatedObjectId="398376934">
      <owner-list>
        <owner id="process15869047848" mode="IX" />
        <owner id="process15869047848" mode="X" requestType="convert" />
      </owner-list>
      <waiter-list>
        <waiter id="process1508af0f088" mode="X" requestType="convert" />
      </waiter-list>
    </objectlock>
    <objectlock lockPartition="0" objid="398376934" subresource="FULL" dbid="8" objectname="SEDD3_KIO.dbo.WF4_Routes" id="lock154ab1c5d00" mode="IX" associatedObjectId="398376934">
      <owner-list>
        <owner id="process15869047848" mode="IX" />
        <owner id="process1508af0f088" mode="IX" />
        <owner id="process15869047848" mode="X" requestType="convert" />
        <owner id="process1508af0f088" mode="X" requestType="convert" />
      </owner-list>
      <waiter-list>
        <waiter id="process1586903f468" mode="X" requestType="convert" />
      </waiter-list>
    </objectlock>
  </resource-list>
</deadlock>

UPDATE 15:07 I think I found the reason, you asked if the UPDLOCK hint is needed, I decided to check how it affects the locking and found that this kind of hint locks the entire table. Can anyone tell me why this is happening? An example of using UPDLOCK in our condition:

select R.ID, R.DocUID from WF4_Routes R with (UPDLOCK) where R.DocUID = @docUID

DocUID index is available.

UPDATE 15:47 Now we have found that if UPDLOCK is used with a search condition on a non-clustered index, then it locks the entire table. If it is clustered, then everything is fine, only those records that are specified in the where clause are blocked. It's strange that the manual for using this hint doesn't say anything about it.

Improve this question
10
  • 1
    You'll have to look into the query plan. Does it do table scans due to lack of indexes?
    – Cedersved
    Dec 29, 2020 at 10:11
  • @Cedersved, is it important at the READ COMMITED SNAPSHOT isolation level? I read that indexes are important only if the level is Serializable.
    – MiXaiL
    Dec 29, 2020 at 11:47
  • @PrebenHuybrechts, Shouldn't lock escalation check if it can occupy the entire table? I read that ms sql tries to lock the entire table and if it works, then only in this case the entire table is locked at once.
    – MiXaiL
    Dec 29, 2020 at 12:01
  • @PrebenHuybrechts, Yes, we use UPDLOCK to lock the records in the table, which will later be changed in the stored procedure
    – MiXaiL
    Dec 29, 2020 at 12:02
  • 1
    process15869037088 is running at serializable isolation level. Is that intentional? What do the execution plans look like? Are ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS both on?
    – Martin Smith
    Dec 29, 2020 at 12:38

1 Answer 1

Reset to default
1

The reason for blocking the entire table was that ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF were disabled on our index. Hope this helps someone.

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.