One of the dreaded error codes we get in the SQL log is the “1205”. It represents the infamous deadlock where two transactions are locking each other out. No way out. (Wasn’t that a Kevin Costner movie? He turned out to be a Russian spy, didn’t he? Whoops…gave that one away…).When there’s no way out, we have to take drastic action. And SQL Server does just that. One of the transactions is “killed” and becomes the “victim”. Sounds like an episode of “CSI:NY” but it’s true. The SQL Profiler has a neat Deadlock Graph feature which is very useful. Let’s take a look….
When SQL Server detects a deadlock, it has to do something about it. It means that multiple resources are being locked by multiple transactions and each transaction is looking for the other to release its lock on the other resource. No way out. SQL Server works out which transaction would be the cheapest to rollback. That usually is the latest starting transaction. It then kills that process which makes it the victim and gives an error 1205. At this point, the victim’s transaction is rolled back, its locks are released and the other transaction is free to proceed and presumably finish successfully. The SQL Profiler allows you to select the Deadlock Graph as a traced event. It captures deadlock information in XML format and displays it graphically in the tool. The victim is clearly depicted; the resources being locked are displayed and the succeeding transaction is shown. The Windows System Monitor also has a “Number of Deadlocks/sec” counter within the “SQLServer:Locks” performance object. You can track this through a Counter Log, import the performance data to the SQL Profiler and view a synchronized graph with your trace. In this way it is easy to spot the deadlocks.
Now, you cannot expect to eliminate deadlocks completely in a high transaction OLTP application but you can employ tactics to minimize them. For instance, keep transaction short and sweet. Then the locks are held for less time and therefore less chance of a deadlock. Never involve user interaction within a transaction. Even if a user reacts quickly, a few seconds to a database server can be an eternity, not to mention if the user goes to lunch or, dare I say it, goes home. Make sure the updates to the same tables within different stored procedures are performed in the same order. The easiest way to create a deadlock intentionally (for instance, for a demo of the deadlock graph) is to update table A then update table B separated by a WAITFOR DELAY statement to simulate a long running transaction. Then execute a transaction that updates table B first and table A second, again separated by a delay. Bingo! Deadlock.
So, now that you know how to do it, you know exactly how to avoid it, right? It takes a thief to catch a thief. A spy to catch a spy. Which brings me back to that movie again: “No Way Out”. There’s always a twist. That’s why we have DBAs. Think of it as job security.
Cheers
Brian
Recent posts…
SSWUG Ultimate Virtual Conference
