Database Reference
In-Depth Information
Figure 19-2. Classic deadlock, Step 2
There is the system task— Deadlock Monitor —that wakes up every five seconds and checks if there are any
deadlocks in the system. When a deadlock is detected, SQL Server rolls back one of the transactions. That releases all
locks held by the session in that transaction, and it allows other sessions to continue.
the Deadlock Monitor wake-up interval goes down in case if there are deadlocks in the system. In some cases
it could wake up as often as ten times per second.
Note
The session chosen to be the deadlock victim depends on a few things. By default, SQL Server rolls back the
session that uses less log space for the transaction. Although you can control it, up to a degree, by setting deadlock
priority for the session with SET DEADLOCK_PRIORITY command.
Deadlock Due to Non-Optimized Queries
While classic deadlock often happens when the data is highly volatile and the same rows are updated by multiple
sessions, there is another common reason for the deadlocks to occur. They can happen due to the scans introduced
by non-optimized queries. Let's look at an example and assume that you have a process that updates an order row in
an Order Entry system and, as a next step, queries how many orders the customer has. Let's see what happens under
the hood when two such sessions are running in parallel, assuming the application is using READ COMMITTED
transaction isolation level.
As the first step, two sessions are running two update statements. Both statements run just fine without
blocking—as you remember, the table has the clustered index on OrderId column so you will have clustered index
seek operations during the updates. Figure 19-3 illustrates this step.
 
 
Search WWH ::




Custom Search