1064
Part VIII: Performance Tuning and Optimization
FIGURE 47-6
SQL Server Profi ler can monitor deadlocks using the Locks: Deadlock Graph event and can
display the resource confl ict that caused the deadlock.Automatic Deadlock Detection
As the previous deadlock code demonstrated, SQL Server uses an internal thread named the
Deadlock Monitor, which automatically detects a deadlock situation by examining the wait-
ing lock lists for cyclical locking and rolling back the transaction. By default, this thread
checks for deadlocking characteristics every 5 seconds, but the database engine adjusts
this threshold so that checks are made more often if deadlocks are detected frequently.Typically, the transaction that has performed the least amount of work or is easiest to
roll back is the victim, but this is not always true. As a database developer you do have a
little bit of control in assigning deadlock priorities to your SQL batches.Handling Deadlocks
Before SQL Server 2005, trapping a deadlock could occur only at the client, but fortunately
a try-catch error handling code can trap a 1205 error, and deadlocks can now be handled
in the catch block. If you catch the deadlock within the transaction, your only option is to
roll back the transaction. However, you can then rerun your transaction using T-SQL logic.USE AdventureWorks2012
DECLARE @retry INTc47.indd 1064c47.indd 1064 7/31/2012 10:23:30 AM7/31/2012 10:23:30 AM
http://www.it-ebooks.info