Database Reference
In-Depth Information
Chapter 20
Blocking and Blocked Processes
You would ideally like your database application to scale linearly with the number of database users and the volume
of data. However, it is common to find that performance degrades as the number of users increases and as the
volume of data grows. One cause for degradation, especially associated with ever-increasing scale, is blocking. In fact,
database blocking is usually one of the biggest enemies of scalability for database applications.
In this chapter, I cover the following topics:
•
The fundamentals of blocking in SQL Server
•
The ACID properties of a transactional database
•
Database lock granularity, escalation, modes, and compatibility
•
ANSI isolation levels
•
The effect of indexes on locking
•
The information necessary to analyze blocking
•
A SQL script to collect blocking information
•
Resolutions and recommendations to avoid blocking
•
Techniques to automate the blocking detection and information collection processes
Blocking Fundamentals
In an ideal world, every SQL query would be able to execute concurrently, without any blocking by other queries.
However, in the real world, queries
do
block each other, similar to the way a car crossing through a green traffic signal
at an intersection blocks other cars waiting to cross the intersection. In SQL Server, this traffic management takes the
form of the
lock manager,
which controls concurrent access to a database resource to maintain data consistency. The
concurrent access to a database resource is controlled across multiple database connections.
I want to make sure things are clear before moving on. Three terms are used within databases that sound the
same and are interrelated but have different meanings. These are frequently confused, and people often use the
terms incorrectly. These terms are
locking
,
blocking
, and
deadlocking.
Locking is an integral part of the process
of SQL Server managing multiple sessions. When a session needs access to a piece of data, a lock of some type is
placed on it. This is different from blocking, which is when one session, or thread, needs access to a piece of data
and has to wait for another session's lock to clear. Finally, deadlocking is when two sessions, or threads, form what is
sometimes referred to as a
deadly embrace.
They are each waiting on the other for a lock to clear. Deadlocking could
also be referred to as a permanent blocking situation, but it's one that won't resolve by waiting any period of time.
Deadlocking will be covered in more detail in Chapter 21. So, locks can lead to blocks, and both locks and blocks play
