Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction Expand / Collapse
Author
Message
Posted Monday, March 17, 2008 1:26 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, March 17, 2008 4:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 13, 2013 10:32 AM
Points: 45, Visits: 182
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
Posted Monday, September 15, 2008 11:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, September 16, 2008 2:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 42,827, Visits: 35,957
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
Posted Tuesday, September 16, 2008 3:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, September 16, 2008 3:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 42,827, Visits: 35,957
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
Posted Tuesday, July 27, 2010 11:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, July 27, 2010 11:34 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 42,827, Visits: 35,957
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
Posted Monday, January 10, 2011 3:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 15, 2014 8:24 AM
Points: 46, Visits: 157
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH }

I resolve same problem with this command.
Post #1045191
Posted Thursday, May 2, 2013 8:04 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 3, 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 »

Add to briefcase

Permissions Expand / Collapse