An interesting question came up during a class recently about Deadlocks. The dreaded deadlock in SQL Server produces an error 1205 and causes one transaction to fail and a rollback issued. Deadlocks are different from classic blocking behavior.
Blocking means one transaction is waiting for a resource (e.g. a row in the process of being updated) that is locked by another. This causes a delay, but when the lock is released the blocking will stop and the waiting transaction will complete.
A Deadlock is different. It occurs when two transactions are locking each other out from separate resources that each other needs. There is no way out. SQL Server will detect this and will kill the transaction that is cheapest to rollback (the "victim") and will proceed to issue an error 1205. The other transaction succeeds as it is no longer locked out. You cannot completely avoid deadlocks but if they occur frequently, you may be looking at some suspect code in your app.
To demo a deadlock is fairly easy. Have two scripts with two transactions started by BEGIN TRAN statements. Update two tables in each script but update them in opposite order and separate them with a WAITFOR DELAY statement that waits for a few seconds so you have time to start each script manually in SSMS. The two transactions will lock each other out and you will get an error 1205 - Deadlock time.
The question from my student was: "I heard that Trace Flag 1204 was useful in SQL Server 2000, is it still useful?". Well, the answer is: now in SQL Server 2005 and 2008, the SQL Profiler has a Deadlock Graph event that will capture the information you need to diagnose the deadlock and it's much easier to use than the Trace Flag. The Deadlock Graph draws a neat graphic to describe what happened with a big "X" identifying the victim. It highlights the transaction statements involved, the connections, any indexes involved and other information.
The demo highlights exactly what not to do, so by implication it identifies the likely solution. Update multiple tables in the same order in different transactions (for instance, in different stored procedures). Keep transactions as brief as possible so that the locks are released quickly. Never allow user intervention within a transaction - that's asking for big trouble where deadlocks become just one of your worries. A user's response can be an eternity to a Database server.
So next time you see a series of 1205 errors, go to school on the Deadlock Graph and do your homework. It will be worth the effort.
cheers
Brian
Brad McGehee has a great article that goes into detail while explaining the concepts too. Take a look:
http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/
Brian D. Egler, MCITP/MCSE/MCT 2009, is currently an instructor with Global Knowledge, teaching various Microsoft training courses. He is a SQL specialist with a focus on SQL Server, Windows, .Net and XML. Egler has been a technical instructor for over 20 years and has more than 10 years experience with SQL Server, data modeling, database design, application development including IMS, DB2, Sybase. Every year he runs the Boston Marathon for cancer research.
The Leader in Network Knowledge
Post new comment