x

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 '09 at 11:30 AM in Default

gupta gravatar image

gupta
11 1 1 1

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 '09 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 '09 at 10:02 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(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 '09 at 11:40 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x38

asked: Nov 05 '09 at 11:30 AM

Seen: 1726 times

Last Updated: Nov 05 '09 at 11:40 AM