Deadlock on Non Clustered Index

Question

<deadlock>
        <victim-list>
          <victimProcess id="process1e4222068c8" />
        </victim-list>
        <process-list>
          <process id="process1e4222068c8" taskpriority="0" logused="0" waitresource="KEY: 7:72057594563854336 (b3102aa0fac2)" waittime="2780" ownerId="36334367487" transactionname="UPDATE" lasttranstarted="2022-12-22T03:00:04.770" XDES="0x1e7e6cd4420" lockMode="U" schedulerid="2" kpid="19656" status="suspended" spid="210" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-12-22T03:00:04.420" lastbatchcompleted="2022-12-22T03:00:04.423" lastattention="1900-01-01T00:00:00.423" clientapp=".Net SqlClient Data Provider" hostname="X99-L414" hostpid="5552" loginname="user" isolationlevel="read committed (2)" xactid="36334367487" currentdb="7" currentdbname="web" lockTimeout="4294967295" clientoption1="536870944" clientoption2="128056">
            <executionStack>
              <frame procname="web.dbo.DeleteExpiredDeposit" line="47" stmtstart="2710" stmtend="3276" sqlhandle="0x03000700701ca12a68b39c0091ae000001000000000000000000000000000000000000000000000000000000">



UPDATE TOP(1000) Transaction WITH(ROWLOCK) 
            SET f_del = 1 , f_editTime = GETDATE() 
            WHERE ISNULL(f_del,0)&lt;1 
            AND f_paymentType = 56 
            AND ISNULL(f_state,0) IN (-1,0)
            AND (f_adminName IS NULL OR f_adminName &lt;&gt; '') 
            AND DATEDIFF(ss, f_time, GETDATE()) &gt; 15 * 6    </frame>
            </executionStack>
            <inputbuf>
Proc [Database Id = 7 Object Id = 715201648]   </inputbuf>
          </process>
          <process id="process1e5ffdf2108" taskpriority="0" logused="6288152" waitresource="KEY: 7:72057594563854336 (d33641a89f5b)" waittime="2764" ownerId="36334367477" transactionname="DELETE" lasttranstarted="2022-12-22T03:00:04.760" XDES="0x1e0f06b4420" lockMode="X" schedulerid="23" kpid="21244" status="suspended" spid="667" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-12-22T03:00:01.327" lastbatchcompleted="2022-12-22T03:00:01.327" lastattention="1900-01-01T00:00:00.327" clientapp="Microsoft SQL Server Management Studio" hostname="AG588" hostpid="23272" loginname="dm" isolationlevel="read committed (2)" xactid="36334367477" currentdb="7" currentdbname="web" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="web.dbo.DatatoOld" line="132" stmtstart="12038" stmtend="12550" sqlhandle="0x03000700d5ba850dd5feb8006caf000001000000000000000000000000000000000000000000000000000000">


DELETE TOP(1000) Transaction 
       WHERE 
                    ( 
                        (ISNULL(f_accessState,0)&lt;= 100000000 
         AND DATEDIFF(DAY, f_time, GETDATE())&gt;2)
                        OR
                        (ISNULL(f_accessState,0)&gt; 100000000 
         AND DATEDIFF(DAY, f_time, GETDATE())&gt;7)    

</frame>
              <frame procname="adhoc" line="1" stmtend="42" sqlhandle="0x01000700204f913790d7bbf7e701000000000000000000000000000000000000000000000000000000000000">
exec DatatoOld    </frame>
            </executionStack>
            <inputbuf>
exec DatatoOld
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594563854336" dbid="7" objectname="web.dbo.Transaction" indexname="IX_Transaction_f_fgzl_f_time_f_State_8FBEB7" id="lock1e5b8461580" mode="X" associatedObjectId="72057594563854336">
            <owner-list>
              <owner id="process1e5ffdf2108" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process1e4222068c8" mode="U" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594563854336" dbid="7" objectname="web.dbo.Transaction" indexname="IX_Transaction_f_fgzl_f_time_f_State_8FBEB7" id="lock1e4314fb880" mode="U" associatedObjectId="72057594563854336">
            <owner-list>
              <owner id="process1e4222068c8" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="process1e5ffdf2108" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>

CREATE NONCLUSTERED INDEX [IX_Transaction_f_fgzl_f_time_f_State_8FBEB7] 
       ON [dbo].[Transaction] 
(
    [f_fgzl] ASC,
    [f_time] ASC,
    [f_state] ASC
) 
INCLUDE 
(
    [f_editTime],
    [f_del],
    [f_money]
) 

The UPDATE statement runs once per minute, while the DELETE statement runs once per day. f_time column logic indicates that the same row would not be UPDATED/DELETED at the same time. Both queries will not seek the non clustered index. What could be the cause of this deadlock?

12/23 Edit: By using extended events to track locks while rerunning the queries, I found out that only X locks are acquired on the index and no U locks are acquired at all during the index update. The update statement should not even have U locks on the index at all during the deadlock.

Answer 1

mess

It looks like whomever wrote those queries was intentionally avoiding decades of blog posts about SQL Server query performance.

For reference, here are a couple of mine:

• How To Do Date Math In Your Where Clause To Avoid SQL Server Performance Problems
• ISNULL, COALESCE, And Performance In SQL Server Queries

A good start would be to rewrite your queries so that SQL Server can make better use of any existing indexes that may help them.

Often, blocking and deadlocking come back to query performance issues. The longer modification queries run for, the larger their surface area is for something bad to happen; blocking other queries, or deadlocking with each other.

These may need a little tinkering to be logically equivalent based on local factors, but I leave that exercise to your capable hands.

UPDATE TOP(1000) 
    Transaction WITH(ROWLOCK) 
SET 
   f_del = 1 , 
   f_editTime = GETDATE() 
WHERE f_del < 1 
AND   f_paymentType = 56 
AND  (f_state IS NULL OR f_state IN (0, 1))
AND  (f_adminName IS NULL OR f_adminName <> '') 
AND   f_time < DATEADD(SECOND, 90, GETDATE());


DELETE TOP(1000) 
    Transaction 
WHERE 
( 
  (    f_accessState <= 100000000 
   AND f_time < DATEADD(DAY, 2, GETDATE())
  )
OR
  (    f_accessState > 100000000 
   AND f_time < DATEADD(DAY, 7, GETDATE())
  )
);

The index definition you provided doesn't look particularly useful for either one to locate data to update or delete, though both queries will modify that index to update or delete rows in it.

After you've rewritten the queries, if you're still hitting deadlocks, feel free to ask a new question with additional details to dig deeper into thing.

Also, be careful with the ROWLOCK hint you're using. It could lead to earlier lock escalation attempts.