question

gotqn avatar image
gotqn asked

How to fix deadlock caused by "X" locks on same resource?

I have the following ``: Could anyone tell how such deadlocks can be avoided? ---------- I am using `SQL Server 2012` and the database's isolation level is the default `READ COMMITTED`. Here is the the whole deadlock graph: DELETE FROM TableOne WHERE Col001 = 1 *password---- UPDATE TableTwo SET ... exec dbo.usp_TableTwoEdit ... Proc [Database Id = 7 Object Id = 668762056] DELETE FROM TableOne WHERE Col001 = 1 UPDATE TableOne set Col001 = 1 where TaskID = @TaskID unknown (@TaskID int)UPDATE TableOne set Col001 = 1 where TaskID = @TaskID -------------- ![enter image description here][1] [1]: http://i.stack.imgur.com/E8OWf.png
sql-server-2012deadlock
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
VishalhSingh avatar image
VishalhSingh answered
Looks like the Delete and Update and trying to run in parallel and looping through. Can you check the approach in "myDatabase.dbo.usp_TableTwoEdit" and "myDatabase.dbo.usp_TableTwoImport" I suggest the query logic is the pain here.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.