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, 2009 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, 2009 at 11:09 PM Jonathan Kehayias
(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 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, 2009 at 11:40 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

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

x39

asked: Nov 05, 2009 at 11:30 AM

Seen: 1950 times

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