|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to solve deadlock issueIn our application we are getting a deadlock error “transaction (process id) was deadlocked on (lock) resources with another process and has been chosen as the deadlock victim†When we traced the queries in sql profiler we were able to find out two queries trying to access the same table. The given below are the queries 1. Update customer (rowlock) set column1 = ‘1’ where customercd = ‘xxxx’. start time = ‘2005-12-23 11:43:47:250’ 2. Update customer (rowlock) set routecd = ‘xx’ from customer left outer join table2 csc on Customer.CustomerCD = CSC.CustomerCD where CSC.CustomerCD is Null and Customer.RouteCD is Not Null. Start time = ‘2005-12-23 11:43:53:883 Both the queries are executed from two different machines. After this the first query became the victim of the deadlock. Can any one suggest some way to overcome this issue?? Thanks Deepu Try using the UPDLOCK hint.
And do some more reading on the subject: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp ML --- http://milambda.blogspot.com/ The likelihood of deadlocks increases with poor performance and long running
transactions. Please post complete table DDL, including indexes. If CustomerCD is the primary key of the customer table, I would expect the first query to run very quickly and the rowlock hint not needed since only one row will possibly be retrieved. However, since the second query started abut 5 seconds later than the first, either the query was still running or there is an open transaction on the first connection. Both cases are red flags that need to be investigated. Similarly, a indexes on the columns in the JOIN and WHERE clauses of the second query may help. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Deepu" <De***@discussions.microsoft.com> wrote in message news:892BDC17-4F1A-4715-83EB-006D8A450765@microsoft.com... > Hi, > > > > In our application we are getting a deadlock error "transaction (process > id) was deadlocked on (lock) resources with another process and has been > chosen as the deadlock victim" > > When we traced the queries in sql profiler we were able to find out two > queries trying to access the same table. > > > > The given below are the queries > > > > 1. Update customer (rowlock) set column1 = '1' where customercd = > 'xxxx'. start time = '2005-12-23 11:43:47:250' > > 2. Update customer (rowlock) set routecd = 'xx' from customer left > outer join table2 csc on Customer.CustomerCD = CSC.CustomerCD where > CSC.CustomerCD is Null and Customer.RouteCD is Not Null. > Start time = '2005-12-23 > 11:43:53:883 > > > > Both the queries are executed from two different machines. > > After this the first query became the victim of the deadlock. > > > > Can any one suggest some way to overcome this issue?? > > > > Thanks > > Deepu > > > > >  |
|||||||||||||||||||||||
Other interesting topics