November 3, 2015 at 4:55 am
I have been reading up a lot about deadlocks the last few days, but wanted to post a quick question in case I have missed anything.
I am getting a number of deadlocks when inserting and deleting items from the same table.
The delete statement has a U lock and awaiting an IX lock on an index that covers the column in the where clause.
The insert statement has a IX lock and awaiting a U lock on the same index.
The delete statement is deleting about 5000 rows, where as the insert statement is inserting a single row.
Both these statements are found in stored procedures being called from LINQ to SQL.
I am wondering if there is a way I can prevent the delete statement taking the U lock out?
My thinking being if the delete didn't take out the U lock then it would not deadlock with the insert.
Are there any hints I could use to avoid the particular lock above?
I have seen various examples of multiple updates causing a deadlock, which can be fixed by adding multiple indexes.
However, as I am inserting and deleting rows I imagine that all the indexes will need to be updated by both operations.
I have inherited the architecture and don't have the time to redesign everything at present.
Unless anyone has any better suggestions my backup plan is to deprioritise the delete and build in a retry mechanism.
However, it would be really good if I could find a more elegant way to handle deleting and inserting rows at the same time.
Deadlock trace information below...
11/02/2015 22:21:26,spid21s,Unknown,waiter id=process1cc9c68558 mode=IX requestType=wait
11/02/2015 22:21:26,spid21s,Unknown,waiter-list
11/02/2015 22:21:26,spid21s,Unknown,owner id=process203f31b498 mode=U
11/02/2015 22:21:26,spid21s,Unknown,owner-list
11/02/2015 22:21:26,spid21s,Unknown,pagelock fileid=1 pageid=721673 dbid=6 subresource=FULL objectname=PerforceReports_Staging.dbo.DebugReport id=lock1663f5d900 mode=U associatedObjectId=72057594052870144
11/02/2015 22:21:26,spid21s,Unknown,waiter id=process203f31b498 mode=U requestType=wait
11/02/2015 22:21:26,spid21s,Unknown,waiter-list
11/02/2015 22:21:26,spid21s,Unknown,owner id=process1cc9c68558 mode=IX
11/02/2015 22:21:26,spid21s,Unknown,owner-list
11/02/2015 22:21:26,spid21s,Unknown,pagelock fileid=1 pageid=760355 dbid=6 subresource=FULL objectname=PerforceReports_Staging.dbo.DebugReport id=lock1fd8c3a500 mode=IX associatedObjectId=72057594052870144
11/02/2015 22:21:26,spid21s,Unknown,resource-list
11/02/2015 22:21:26,spid21s,Unknown,(@p0 uniqueidentifier<c/>@p1 uniqueidentifier<c/>@p2 int<c/>@p3 nvarchar(4000)<c/>@p4 int<c/>@p5 nvarchar(4000)<c/>@p6 nvarchar(4000)<c/>@p7 nvarchar(4000)<c/>@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[usp_DBReplicator_DebugReportInsert] @ID = @p0<c/> @RunID = @p1<c/> @BRN = @p2<c/> @BVNC = @p3<c/> @status = @p4<c/> @Reason = @p5<c/> @NotIntegratedCSs = @p6<c/> @AllFixes = @p7
11/02/2015 22:21:26,spid21s,Unknown,inputbuf
11/02/2015 22:21:26,spid21s,Unknown,unknown
11/02/2015 22:21:26,spid21s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
11/02/2015 22:21:26,spid21s,Unknown,EXEC @RETURN_VALUE = [dbo].[usp_DBReplicator_DebugReportInsert] @ID = @p0<c/> @RunID = @p1<c/> @BRN = @p2<c/> @BVNC = @p3<c/> @status = @p4<c/> @Reason = @p5<c/> @NotIntegratedCSs = @p6<c/> @AllFixes = @p7
11/02/2015 22:21:26,spid21s,Unknown,frame procname=adhoc line=1 stmtstart=320 sqlhandle=0x0100060099ac8d10809b77c50b00000000000000000000000000000000000000000000000000000000000000
11/02/2015 22:21:26,spid21s,Unknown,-- Insert row into the archvive table.
11/02/2015 22:21:26,spid21s,Unknown,VALUES (@ID<c/> @RunID<c/> @BRN<c/> @BVNC<c/> @status<c/> @Reason<c/> @NotIntegratedCSs<c/> @AllFixes)
11/02/2015 22:21:26,spid21s,Unknown,INSERT INTO [DebugReport]([ID]<c/> [RunID]<c/> [BRN]<c/> [BVNC]<c/> [Status]<c/> [Reason]<c/> [NotIntegratedCSs]<c/> [AllFixes])
11/02/2015 22:21:26,spid21s,Unknown,frame procname=PerforceReports_Staging.dbo.usp_DBReplicator_DebugReportInsert line=16 stmtstart=598 stmtend=1074 sqlhandle=0x03000600241ae6143ac902013ea5000001000000000000000000000000000000000000000000000000000000
11/02/2015 22:21:26,spid21s,Unknown,executionStack
11/02/2015 22:21:26,spid21s,Unknown,process id=process1cc9c68558 taskpriority=0 logused=2538856 waitresource=PAGE: 6:1:721673 waittime=4458 ownerId=1073034732 transactionname=user_transaction lasttranstarted=2015-11-02T22:21:15.413 XDES=0x1a517f16a8 lockMode=IX schedulerid=2 kpid=5640 status=suspended spid=238 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-11-02T22:21:22.310 lastbatchcompleted=2015-11-02T22:21:22.310 lastattention=1900-01-01T00:00:00.310 clientapp=.Net SqlClient Data Provider hostname=TEST-PC hostpid=15236 loginname=TESTRMLibrary isolationlevel=read committed (2) xactid=1073034732 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
11/02/2015 22:21:26,spid21s,Unknown,(@p0 nvarchar(4000)<c/>@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[usp_DBReplicator_DeleteLiveBvncData] @BVNC = @p0
11/02/2015 22:21:26,spid21s,Unknown,inputbuf
11/02/2015 22:21:26,spid21s,Unknown,unknown
11/02/2015 22:21:26,spid21s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
11/02/2015 22:21:26,spid21s,Unknown,EXEC @RETURN_VALUE = [dbo].[usp_DBReplicator_DeleteLiveBvncData] @BVNC = @p0
11/02/2015 22:21:26,spid21s,Unknown,frame procname=adhoc line=1 stmtstart=90 sqlhandle=0x01000600e0ece915f0fdeec61c00000000000000000000000000000000000000000000000000000000000000
11/02/2015 22:21:26,spid21s,Unknown,DELETE [DebugReport] WHERE [BVNC] = @BVNC
11/02/2015 22:21:26,spid21s,Unknown,frame procname=PerforceReports_Staging.dbo.usp_DBReplicator_DeleteLiveBvncData line=10 stmtstart=382 stmtend=468 sqlhandle=0x0300060008abb61835c902013ea5000001000000000000000000000000000000000000000000000000000000
11/02/2015 22:21:26,spid21s,Unknown,executionStack
11/02/2015 22:21:26,spid21s,Unknown,process id=process203f31b498 taskpriority=0 logused=1112 waitresource=PAGE: 6:1:760355 waittime=5513 ownerId=1073041626 transactionname=DELETE lasttranstarted=2015-11-02T22:21:21.253 XDES=0x1ceb8fed28 lockMode=U schedulerid=11 kpid=6024 status=suspended spid=218 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-11-02T22:21:20.813 lastbatchcompleted=2015-11-02T22:21:20.813 lastattention=2015-11-02T22:18:31.697 clientapp=.Net SqlClient Data Provider hostname=TEST-PC hostpid=5216 loginname=TESTRMLibrary isolationlevel=read committed (2) xactid=1073041626 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
11/02/2015 22:21:26,spid21s,Unknown,process-list
11/02/2015 22:21:26,spid21s,Unknown,deadlock victim=process203f31b498
11/02/2015 22:21:26,spid21s,Unknown,deadlock-list
November 3, 2015 at 9:09 am
Could you change the delete process so that just the PKey of the row, to be deleted, is stored in a new "ToBeDeleted" table, and then physically delete them out-of-hours?
Depends if they need to be "gone" immediately for referential integrity or reporting purposes. Reporting could be "fixed" by excluding any rows that exist in the ToBeDeleted table, bit more hassle to change the reports though (you could create a VIEW:
CREATE VIEW MyView
AS
SELECT Col1, Col2, ...
FROM MyTable AS T
WHERE NOT EXISTS
(
SELECT *
FROM ToBeDeleted AS D
WHERE D.PKey1 = T.PKey1
...
)
and then change any reports to use MyView instead of MyTable, but its still not exactly zero effort.
November 18, 2015 at 10:03 am
Hi Kristen-173977,
I like the idea of adding the to-be-deleted information to a completely separate table.
I had thought about a to-be-deleted column, but wasn't sure if the UPDATE could then deadlock with the INSERT.
I suspect updating lots of rows instead of delete could still result in a page lock which could result in a deadlock.
However, I can see how inserting rows into a separate table and doing the delete later could work.
Unfortunately, it would have been too much effort for me to implement these changes as you suggest.
I was really after a quick fix and I guess in this case there is no quick fix that just works.
In the end I went for the deprioritise and try again solution I mentioned in my original post.
Although not ideal, it was a quick fix and is manageable until I have time to implement a better solution as you suggested.
November 18, 2015 at 10:27 am
Or you could encapsulate the INSERT and DELETE in something like the code snippet I attached to this post. If someone else would like to copy it directly into a post here, please do. I can't post the code directly from my current location.
November 18, 2015 at 10:46 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply