Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Question

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Alkesh Khedle

Right there with Babe

Points: 780
More actions
March 17, 2008 at 1:26 am

#71718

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction even using with(nolock) option in select query
HI got the above error in production site during high load .When SQL Server2005 used one select query with some joins in it and all table with nolock hint.
I am looking for some best solutions to avoid those errors .

Viewing 11 posts - 1 through 10 (of 10 total)

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

twalston Mr or Mrs. 500 Points: 505 More actions · Answer 1

We had similar problems at a job I worked. Basically we had a third party software that the dev manager was impatient with the software producer. So he wrote his own applications against the db. Eventually the db hit a critical mass of usage, vs changes, vs triggers to contain the business logic.

Turns out the software in question always wrote data to tables in a consistent table order, found using profiler. Efforts were taken in development to make sure that all triggers and code followed the same order of inserts and updates to minimize the possibility of deadlocks. If you use the Profiler tool during these periods of deadlock you should be able to narrow down the processes in question that are causing the problem. Work out the data flow for these processes and you should be able to find your major culprit.

gourav.j SSC Veteran Points: 275 More actions · Answer 2

The tips to resolve this type of errors can be found in this link.

http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/

Can you please tell me that how can I identify during the production that which process caused the deadlock?

Regards,

Gourav

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Enable traceflag 1222 (DBCC TRACEON (1222.-1))

With that on, a deadlock graph is written to the error log when a deadlock occurs. You can read through the graph to see what processes were involved in the deadlock, what they were doing and what resources they were deadlocked over.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gourav.j SSC Veteran Points: 275 More actions · Answer 4

Hi,

Thanks for the reply but my concern is that the dead lock occured yesterday and I need to answer my client that which process caused the deadlock then how can I find it?

Thanks in advance

Gourav

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Unless you had one of the traceflags on or a profiler trace running with the deadlock events, there's no way to tell now what happened.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kalyan.appdev SSC Enthusiast Points: 160 More actions · Answer 6

"transaction(Process ID 59) was deadlocked on lock resources with another process and has been choosen as the deadlock victim.Return the transaction"

I got the above error in production site during high load Iam using sqlserver 2005 , i dont have sql profiler, what would be the problem , When i restart the Sql service ,application works smothly please Help me out of this Situation ….

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Please post your question in a new thread. Thank you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cuk3m SSC Enthusiast Points: 146 More actions · Answer 8

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH }

I resolve same problem with this command.

Mavi SSC Veteran Points: 210 More actions · Answer 9

@GrassHopper

After running the syntax

"SET DEADLOCK_PRIORITY NORMAL;

GO'

Did the error go away permanently?

pratap.julu SSC Veteran Points: 227 More actions · Answer 10

Hi i had faced the same problem as query was taking lots of time to give the results and ending up in deadlock. I have used "SET DEADLOCK_PRIORITY NORMAL;" before the select query and deadlock error is gone now. The Query still take lots of time to execute but it is giving me the result