A deadlock has to be one of the most frustrating error messages that SQL Server can produce. Little information is given as to what went wrong, and the only advice the error message gives is "Rerun the transaction." It appears that there's no way to find the root cause and to prevent the mysterious error from occurring. In truth, there are ways to find information on exactly what happened to cause a deadlock and, with that information, in most cases it's possible to fix the root cause and prevent the error completely. At its simplest, a deadlock refers to a locking situation that, if no outside action is taken, will never resolve itself.
It occurs when two or more processes have a lock on a resource and then try to acquire a lock on the resource held by the other process, or when two or more processes hold shared locks on a resource and attempt to convert them to exclusive locks. Without intervention, the two processes will wait forever for the resource and they'll never complete. In order to resolve the situation, one of the processes must be killed and any modifications it made must be rolled back, so that the locks it held are released, allowing the other process to continue. This is the job of SQL Server's deadlock detector. It evaluates the locks held and locks requested and, if it finds an existing deadlock condition, it picks one of the deadlocked processes and kills it. The process killed by the deadlock detector is known as the deadlock victim. The selection of the deadlock victim is based on a number of conditions:
The deadlock priority, if set
The number of data modifications that will need to be rolled back
Whether the processes are system processes or user processes
In general, the process with the lowest deadlock priority or the one that's least expensive to roll back is the one that will be picked as the deadlock victim. Deadlocks are typically caused by long-running transactions, or different transactions accessing the same objects in an inconsistent order. A lack of useful indexes, poorly written code, and hardware bottlenecks can all cause transactions to run longer than they should, resulting in locks held for longer than necessary. If statements in different stored procedures access tables in different orders, it's possible for two transactions to be holding a lock on the one object and wanting a lock on the other, resulting in a deadlock. Isolation levels higher than the default (Read Committed) can also cause frequent deadlocks, as the locks are held longer, or more intrusive locks are held. This is true with the Serializable isolation level. A deadlock graph is a representation of the processes involved in a deadlock, the locks that they held, and the resources that they were using. Deadlock graphs contain all the information needed to diagnose the cause of the deadlock, though not necessarily in a form that's easy to understand.
You may get a deadlock graph on SQL Server 2005 and SQL Server 2008 in three main ways. SQL Profiler includes a deadlock graph event, and there are two trace flags: 1204 and 1222. Of these, only trace flag 1204 was available on SQL Server 2000. Trace flag 1204 is one of the oldest ways to view a deadlock graph, and is the only method available on SQL Server 2000 or earlier. To produce the deadlock graph, the trace flag needs to be enabled server-wide either using the DBCC TRACEON command (DBCC TRACEON (1204,-1)), or by adding the trace flag to SQL Server's startup parameters (-T1204). Trace flag 1222 works much the same as 1204 does, in that it writes deadlock information into the error log. The results produced by 1222 are far more detailed and easier to read than those produced by 1204. As such, it's preferred over 1204 for producing deadlock information on SQL Server 2005 or 2008. Trace flag 1222 is enabled the same way as 1204 is, by using the TRACEON command (DBCC TRACEON (1222,-1)), or by adding the trace flag to the startup parameters (-T1222).
In SQL Server 2005, the deadlock graph event was added to SQL Profiler. This event produces a graphical view of a deadlock graph that shows the processes, resources, and locks involved in a deadlock. The graph is saved in XML format and can be viewed in Management Studio. This option requires that a trace be running against the target server. If deadlocks happen frequently, or can be reproduced on demand, this is a good way of getting the deadlock graph. If the deadlocks happen infrequently, it may not be feasible to run the profiler for long periods of time. In that case, trace flags may be a more appropriate option.
Our website is not responsible for the information contained by this article. Articleinput.com is a free articles resource thus practically any visitor can submit an article. However if you notice any copyrighted material, please contact us and we will remove the article(s) in discussion right away.
Note: This article was sent to us by: Norman F. Cannon at 01092010
1. Lambda expressions and generator functions in Python
All articles are property of their respective authors. Please read our Privacy Policy!
© 2009 ArticleInput.com.