SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock on page lock during two simple delete commands


Deadlock on page lock during two simple delete commands

Author
Message
talbot 42708
talbot 42708
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 19
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

Attachments
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93763 Visits: 20663
Can you post the DDL (create table) script including the indices?
Cool
talbot 42708
talbot 42708
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 19
Hi Eirikur, I attached the create script in the edit of my initial post.
talbot 42708
talbot 42708
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 19
The solution of this deadlock victim is here: SOLUTION
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search