Sample Header Ad - 728x90

Deadlock on Non Clustered Index

0 votes
1 answer
390 views
Deadlock Graph
UPDATE TOP(1000) Transaction WITH(ROWLOCK) 
			SET f_del = 1 , f_editTime = GETDATE() 
			WHERE ISNULL(f_del,0) '') 
			AND DATEDIFF(ss, f_time, GETDATE()) > 15 * 6    
            
            
Proc [Database Id = 7 Object Id = 715201648]   
          
          
            
              


DELETE TOP(1000) Transaction 
       WHERE 
					( 
						(ISNULL(f_accessState,0)2)
						OR
						(ISNULL(f_accessState,0)> 100000000 
         AND DATEDIFF(DAY, f_time, GETDATE())>7)    


              
exec DatatoOld    
            
            
exec DatatoOld
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.
Asked by JieLong (315 rep)
Dec 22, 2022, 11:34 AM
Last activity: Feb 14, 2023, 04:00 PM