Deadlock error in high cuncurrency env

Question

Post reply

Deadlock error in high cuncurrency env

loveleshg

Old Hand

Points: 384
More actions
November 5, 2009 at 7:32 am

#98263

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 transaction."
Any suggestion to nail down the actual issue and cause of deadlock

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Read this article to understand the subject:

Detecting and Ending Deadlocks

http://msdn.microsoft.com/en-us/library/ms178104.aspx

However, in many cases, changing the database to read_committed_snapshot will solve your deadlocking problems:

use master

alter database [MyDatabase] set allow_snapshot_isolation on

alter database [MyDatabase] set read_committed_snapshot on

Before implementing this, you should read about snapshot isolation in SQL Server 2005 Books Online to understand the implications.

Using Snapshot Isolation:

http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx

Rookee DBA SSCrazy Points: 2188 More actions · Answer 2

If possible pls send the dead lock graph. I am currently troubleshooting the same. There are few important this which are not so easy to know. I will be able to tell you what is going wrong.

You need to first understand why its happening and then look for solution.

cheers

Siddarth

aravind.s SSC-Addicted Points: 449 More actions · Answer 3

Use the profiler with the event selected as deadlock graph alone.

run the profiler and your application instances.once the deadlock occurs the graph will be displayed.from that You can easily identify which is causing the deadlock.then you can tune it.

loveleshg Old Hand Points: 384 More actions · Answer 4

Thanks for response.

I am not sure how the deadlock graph look like. Is it deadlock.trc files?

Rookee DBA SSCrazy Points: 2188 More actions · Answer 5

In the profile, searc for text <deadlock-List>. In the row, rt click -->Extract Event Data> and save the file. Attach the file here.

cheers

Siddarth

aravind.s SSC-Addicted Points: 449 More actions · Answer 6

go thru this article.

msdn.microsoft.com/en-us/library/ms188246.aspx

You will find that analysing deadlocks is a piece of cake.:-)

Rookee DBA SSCrazy Points: 2188 More actions · Answer 7

http://www.mssqltips.com/tip.asp?tip=1234, this would also help.