Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 312

Tuning for Deadlocking and Blocking
Chapter 4 covers the topics of blocking and deadlocking, including techniques for capturing and recog-
nizing blocks and deadlocks are described. This section discusses techniques and practices for handling
these events.

Blocking
Before proceeding a brief review of blocking is needed. Blocking isn't necessarily a problem. It occurs
regularly during the normal operation of a database. A typical scenario for blocking occurs when one
user who's updating a data page prevents another user from accessing that page at the same time. When
this happens the second user is blocked for a few moments until the first user completes the change and
releases the page. This isn't the issue that causes performance degradation. The performance-robbing
blocks are the ones that last for more than a few moments — a lot more. Imagine standing in line at a
grocery store checkout. The line is moving well. The customers are steadily progressing through the line
when the cashier has to stop the process and get a price check on an item. At this point everyone in the
line is stopped or blocked until the price check is completed. This is the type of blocking that needs to be
minimized.

In order to aid your investigation of blocking, SQL Server has provided some DMVs that are use-
ful. Specifically, the sys.dm_os_wait_stats and sys.dm_os_waiting_tasks are particularly handy. See
Chapter 4 for a more thorough description of these DMVs as well as other blocking tools.

Deadlocks
Deadlock is a variation of a block. The issue with a deadlock is that two users are trying to access an
object, such as a data page or row, which the other user has locked. Each user needs to access the other
user's object before completing their respective transactions, but they can't.

The June 1971 issue of Computing Surveys has an article by E.G. Coffman, Jr.; M. J. Elphick; and A.
Shoshani entitled ''System Deadlocks.'' In this article the authors describe four conditions that are neces-
sary in order for a deadlock to occur. The wording of the original conditions has been changed slightly
in order to understand how they apply to database objects.

❑

Database connections acquire exclusive control of the database object(s) (data page, row, index,
etc.) they need. This is known as the mutual exclusion condition.

❑ Database connections hold database object(s) already allocated to them while waiting for addi-

tional database object(s). This is known as the wait for condition.

❑ Database object(s) cannot be removed from the connections holding them until the object(s) are

used to completion. This is known as the preemption condition.

❑ A circular chain of database connections exists in which each connection holds one or more

database object(s) that are requested by the next connection in the chain. This is known as the
circular wait condition:

Note that all four conditions must occur.

You may be wondering if deadlock can be prevented, and if so, why doesn't SQL Server prevent them
instead of letting them occur. In fact, it is possible to prevent or avoid deadlock states. However, it isn't