August 27, 2007 at 1:59 pm
We have a new app that is now having transactions deadlock and choose one to roll back. When I run the SELECT @@LOCK_TIMEOUT it returns a -1 which means wait indefinately. The transactions are coming from two different types of apps so it does not appear that both would be setting a temporary milliseconds timeout. Any ideas ?
(SQL 2000 SP3 Win2000 SP4)
August 27, 2007 at 2:22 pm
Not sure your timeout makes a difference with deadlocks. Deadlock are where two queries are trying to get locks on 2 resources, query one locks object A and waits to get a lock on Object B. Query 2 locks object B and tried to get a lock on object A. A won't allow B to finish it's locks and B won't allow A to finish it's locks. Hence the deadlock. You may need to track down the deadlocks by turning on the deadlock Trace flag (trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server 2005 error log). Find the queries that are in conflict. Then either split up the queries to make smaller transactions or alter the lgoic so they lock things in the same order. I've found long running multiple operation queries and triggers to be the most common cause of deadlocks.
August 27, 2007 at 2:25 pm
Sorry missed the bit about it being SQL2000. Not sure trace flags are as useful in 2000 but I have tracked down sql2000 deadlocks in this manner before.
August 27, 2007 at 2:33 pm
Roger beat me to this, but here's my answer too. I'm in agreement with Roger, but I will add that the trace flags in SQL Server 2000 do work!
There’s a major difference between a deadlock and a lock timeout. In fact, they have nothing to do with one another. A lock timeout is exactly what it sounds like, when a lock request reaches the timeout threshold set for that session. A deadlock happens when 2 or more processes are waiting for locks on resources that are locked by a process that is also waiting for a lock on a resource is locked. For example, process A has a lock on Table1 and is requesting a lock on Table2. Process B has a lock on Table2 and is waiting for a lock on Table1. Since both processes are waiting on locks that the other process has locked, a deadlock scenario is reached and SQL Server rolls back whichever process it feels is ‘easiest’ to roll back (unless the deadlock priority is set on one of the processes).
Some of the ways used to avoid deadlocks are:
1) short transactions – keep your transactions as short as possible
2) access tables in the same order when possible
3) Optimize queries/indexes – this helps with #1!
Your challenge here will be to determine which 2 (or more) pieces of code are the likely causes of the deadlock scenario. I recommend setting the trace flags so that SQL Server will give you detailed deadlock information in the error log. See this link for examples: http://qa.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply