Database Reference
In-Depth Information
Chapter 19
Deadlocks
In a nutshell, a
deadlock
is just a special blocking case when multiple sessions or, sometimes, even multiple execution
threads within a single session block each other.
Classic Deadlock
Classic deadlock occurs when two or more sessions are competing for the same set of resources. Let's look at a
by-the-book example and assume that you have two sessions updating the table. As the first step, session 1 updates
the row R1 and session 2 updates the row R2. You know that at this point both sessions acquire and hold exclusive
(X) lock on the rows. You can see this happening in Figure
19-1
.
Figure 19-1.
Classic deadlock, Step 1
Next, let's assume that session 1 wants to update the row R2. It will try to acquire either update (U) or exclusive
(X) lock on R2 (as mentioned in Chapter 17, “Lock Types,” lock type depends on the execution plan choice) and
would be blocked because of exclusive (X) lock already held by the session 2. If session 2 wants to update R1, the
same thing happens—it would be blocked because of exclusive (X) lock held by the session 1. As you see, at this
point both sessions wait on each other and cannot continue the execution, which represents the classic deadlock,
as shown in Figure
19-2
.
