Deadlock on Non Clustered Index
0
votes
1
answer
390
views

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
Last activity: Feb 14, 2023, 04:00 PM