Now, you cannot expect to eliminate deadlocks completely in a high transaction OLTP application but you can employ tactics to minimize them. In this way it is easy to spot the deadlocks. You can track this through a Counter Log, import the performance data to the SQL Profiler and view a synchronized graph with your trace. The Windows System Monitor also has a "Number of Deadlocks/sec" counter within the "SQLServer:Locks" performance object. The victim is clearly depicted the resources being locked are displayed and the succeeding transaction is shown. It captures deadlock information in XML format and displays it graphically in the tool. The SQL Profiler allows you to select the Deadlock Graph as a traced event. 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. It then kills that process which makes it the victim and gives an error 1205. That usually is the latest starting transaction. SQL Server works out which transaction would be the cheapest to rollback. 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. When SQL Server detects a deadlock, it has to do something about it. The SQL Profiler has a neat Deadlock Graph feature which is very useful. Sounds like an episode of "CSI:NY" but it's true. One of the transactions is "killed" and becomes the "victim". (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. It represents the infamous deadlock where two transactions are locking each other out. One of the dreaded error codes we get in the SQL log is the "1205".
0 Comments
Leave a Reply. |