研究僵局图……

一个有趣的问题出现在一个类最近死锁。可怕的僵局在SQL服务器产生一个错误1205,使一个事务失败,回滚。死锁不同于经典的阻塞行为。阻塞意味着一个事务正在等待资源(例如一行被更新)的过程中,由另一个锁。这将导致延迟,但是当释放锁阻塞将停止并等待交易将完成。死锁是不同的。它发生在两个事务锁定彼此互相独立的资源需求。没有出路。SQL Server将检测,会杀了便宜的回滚的事务(“受害者”),并将继续发行一个错误1205。其他事务成功,因为它不再是锁定。 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/