Deadlock on page lock during two simple delete commands

Question

Post reply

Deadlock on page lock during two simple delete commands

talbot 42708

SSC Veteran

Points: 224
More actions
December 7, 2017 at 12:54 am

#325006

Hi, I fight with deadlocks. I can't understand why this occours. This is my deadlock graph:

Here is my MS SQL server log:
12/07/2017 08:06:08,spid16s,Unknown,waiter id=process314c1f868 mode=S requestType=wait 12/07/2017 08:06:08,spid16s,Unknown,waiter-list 12/07/2017 08:06:08,spid16s,Unknown,owner id=process31f02d868 mode=IX 12/07/2017 08:06:08,spid16s,Unknown,owner-list 12/07/2017 08:06:08,spid16s,Unknown,pagelock fileid=1 pageid=17829 dbid=16 subresource=FULL objectname=WR_TEST.dbo.objekt_pp_subjekt id=lock31ba15a80 mode=IX associatedObjectId=72057618603900928 12/07/2017 08:06:08,spid16s,Unknown,waiter id=process31f02d868 mode=S requestType=wait 12/07/2017 08:06:08,spid16s,Unknown,waiter-list 12/07/2017 08:06:08,spid16s,Unknown,owner id=process314c1f868 mode=SIX 12/07/2017 08:06:08,spid16s,Unknown,owner-list 12/07/2017 08:06:08,spid16s,Unknown,pagelock fileid=1 pageid=17178 dbid=16 subresource=FULL objectname=WR_TEST.dbo.objekt_pp_subjekt id=lock31ba3ac00 mode=SIX associatedObjectId=72057618603900928 12/07/2017 08:06:08,spid16s,Unknown,resource-list 12/07/2017 08:06:08,spid16s,Unknown,(@ID int)DELETE FROM OBJEKT_PP_SUBJEKT WHERE ID = @ID 12/07/2017 08:06:08,spid16s,Unknown,inputbuf 12/07/2017 08:06:08,spid16s,Unknown,unknown 12/07/2017 08:06:08,spid16s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 12/07/2017 08:06:08,spid16s,Unknown,DELETE FROM OBJEKT_PP_SUBJEKT WHERE ID = @ID 12/07/2017 08:06:08,spid16s,Unknown,frame procname=adhoc line=1 stmtstart=18 sqlhandle=0x02000000d057ed0963855e0183104b26bffa78106ac53d390000000000000000000000000000000000000000 12/07/2017 08:06:08,spid16s,Unknown,executionStack 12/07/2017 08:06:08,spid16s,Unknown,process id=process314c1f868 taskpriority=0 logused=1072 waitresource=PAGE: 16:1:17829 waittime=3869 ownerId=2176826 transactionname=user_transaction lasttranstarted=2017-12-07T08:06:03.043 XDES=0x3165df6a8 lockMode=S schedulerid=4 kpid=1552 status=suspended spid=118 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-12-07T08:06:03.050 lastbatchcompleted=2017-12-07T08:06:03.047 lastattention=1900-01-01T00:00:00.047 clientapp=TEST_WRServices hostname=WEBSRVTEST hostpid=6376 loginname=WR_App isolationlevel=read committed (2) xactid=2176826 currentdb=16 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 12/07/2017 08:06:08,spid16s,Unknown,(@ID int)DELETE FROM OBJEKT_PP_SUBJEKT WHERE ID = @ID 12/07/2017 08:06:08,spid16s,Unknown,inputbuf 12/07/2017 08:06:08,spid16s,Unknown,unknown 12/07/2017 08:06:08,spid16s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 12/07/2017 08:06:08,spid16s,Unknown,DELETE FROM OBJEKT_PP_SUBJEKT WHERE ID = @ID 12/07/2017 08:06:08,spid16s,Unknown,frame procname=adhoc line=1 stmtstart=18 sqlhandle=0x02000000d057ed0963855e0183104b26bffa78106ac53d390000000000000000000000000000000000000000 12/07/2017 08:06:08,spid16s,Unknown,executionStack 12/07/2017 08:06:08,spid16s,Unknown,process id=process31f02d868 taskpriority=0 logused=996 waitresource=PAGE: 16:1:17178 waittime=3631 ownerId=2176807 transactionname=user_transaction lasttranstarted=2017-12-07T08:06:00.720 XDES=0x31698b6a8 lockMode=S schedulerid=4 kpid=844 status=suspended spid=114 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-12-07T08:06:00.723 lastbatchcompleted=2017-12-07T08:06:00.723 lastattention=1900-01-01T00:00:00.723 clientapp=TEST_WRServices hostname=WEBSRVTEST hostpid=6376 loginname=WR_App isolationlevel=read committed (2) xactid=2176807 currentdb=16 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 12/07/2017 08:06:08,spid16s,Unknown,process-list 12/07/2017 08:06:08,spid16s,Unknown,deadlock victim=process31f02d868 12/07/2017 08:06:08,spid16s,Unknown,deadlock-list
Two simple and equal commands were run from two concurrent threads via sp_executesql:
exec sp_executesql N'DELETE FROM OBJEKT_PP_SUBJEKT WHERE ID = @ID',N'@ID int',@ID=4612965 go exec sp_executesql N'DELETE FROM OBJEKT_PP_SUBJEKT WHERE ID = @ID',N'@ID int',@ID=4612561 go
The SQL scripts were the part of sequence runned in the transaction. Snapshot isolation and read commited snapshot are off. I have 5 indexes on table OBJEKT_PP_SUBJEKT to speedup searching. I am not an expert in the field of indexes. May be the problem is there. In the attachment, there is the simplified script of table create script. Table contains 1491753 rows. How can I resolve this type of deadlock? Any help would be appreciated.
UPDATE:
I tryed ROWLOCK, but it locked page too:
exec sp_executesql N'DELETE FROM OBJEKT_PP_SUBJEKT WITH (XLOCK, ROWLOCK) WHERE ID = @ID',N'@ID int',@ID=4612983 go exec sp_executesql N'DELETE FROM OBJEKT_PP_SUBJEKT WITH (XLOCK, ROWLOCK) WHERE ID = @ID',N'@ID int',@ID=4614112 go

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 1

Can you post the DDL (create table) script including the indices?
😎

talbot 42708 SSC Veteran Points: 224 More actions · Answer 2

Hi Eirikur, I attached the create script in the edit of my initial post.

talbot 42708 SSC Veteran Points: 224 More actions · Answer 3

The solution of this deadlock victim is here: SOLUTION