How to resolve this deadlock error

I am getting error

SQLError: [40001],[Microsoft][SQL Native Client][SQL Server]
Transaction (Process ID 64) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the...

I am getting this error in high concurrency situation. Please suggested how to figure out the cause?

more ▼

asked Nov 05, 2009 at 11:30 AM in Default

avatar image

11 1 1 3

Did you capture the deadlock graph using 1222 as recommended by Grant Fritchey? If you did, can you update your post with it and we can offer advice for how to solve the problem.

Dec 14, 2009 at 11:09 PM Jonathan Kehayias
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You need to get more information in order to understand what's causing the deadlock. Usually the best way is to enable traceflag 1222 on the server. This can sometimes be difficult, especially on production machines, since it requires a restart. In the mean time, if you can reproduce the problem at will, try turning on a trace and capture the event Locks:Deadlock. That will show a graphical plan detailing the deadlock which will make it much easier to fix.

more ▼

answered Nov 08, 2009 at 10:02 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left

Check John Sansom's answer to a question here for links to aide you.

Deadlocks are generally caused by programming modules that do not perform the steps in the same order as each other. Module 1 locks table1 then table2 whereas module 2 does it in the reverse order. Neither module is able to continue because it has to wait on the resource.

more ▼

answered Nov 05, 2009 at 11:40 AM

avatar image

12.1k 30 36 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 05, 2009 at 11:30 AM

Seen: 2502 times

Last Updated: Nov 05, 2009 at 11:40 AM

Copyright 2017 Redgate Software. Privacy Policy