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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Transaction (Process ID 59) was deadlocked on...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Alkesh Khedle

Posted Monday, March 17, 2008 1:26 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 12:25 PM
Points: 16, Visits: 61

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 .

Post #470114

twalston

Posted Monday, March 17, 2008 4:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 3:42 AM
Points: 45, Visits: 180

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.

Post #470149

gourav.j

Posted Monday, September 15, 2008 11:48 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 2:29 PM
Points: 3, Visits: 33

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

Post #569939

GilaMonster

Posted Tuesday, September 16, 2008 2:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 37,680, Visits: 29,936

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 2008, MVP
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

Post #570019

gourav.j

Posted Tuesday, September 16, 2008 3:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 2:29 PM
Points: 3, Visits: 33

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

Post #570025

GilaMonster

Posted Tuesday, September 16, 2008 3:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 37,680, Visits: 29,936

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 2008, MVP
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

Post #570028

kalyan.appdev

Posted Tuesday, July 27, 2010 11:28 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 13, 2010 10:20 PM
Points: 2, Visits: 16

"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 ….

Post #959830

GilaMonster

Posted Tuesday, July 27, 2010 11:34 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 37,680, Visits: 29,936

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #959835

cuk3m

Posted Monday, January 10, 2011 3:16 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 3:47 AM
Points: 24, Visits: 118

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH }

I resolve same problem with this command.

Post #1045191

Mavi

Posted Thursday, May 02, 2013 8:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:52 AM
Points: 2, Visits: 5

@Grasshopper

After running the syntax
"SET DEADLOCK_PRIORITY NORMAL;
GO'

Did the error go away permanently?

Post #1449028

« Prev Topic | Next Topic »

Permissions