Except in some exceptional circumstances deadlocks are a consequence of blocking but deadlocking can be seen as blocking in an infinite loop. The transactions cannot be committed or rolled back because the resources they are waiting on are owned by the other thread. Both threads cannot release resources until their transactions are committed or rolled back. Where blocking occurs where one transaction is waiting on resources in use by another transaction, deadlocking refers to the situation where two or more threads are waiting for resources in a circular blocking chain. This situation should not be confused with blocking as they are dissimilar. It does not relate specifically to SQL Server, or to any RDBMS. Think of it as job security.A deadlock is a condition that can occur on any multi-threaded system. Which brings me back to that movie again: "No Way Out".
#Sql server deadlock error code how to
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. Then execute a transaction that updates table B first and table A second, again separated by a delay. 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.
Make sure the updates to the same tables within different stored procedures are performed in the same order. 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.
Never involve user interaction within a transaction. Then the locks are held for less time and therefore less chance of a deadlock. For instance, keep transaction short and sweet. 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 kommentar(er)
