Database Reference
In-Depth Information
Multiple Sessions and Lock Compatibility
One important point we have yet to cover is what is happening to lock compatibility when more than two sessions are
competing for the same resource. Let's look at a couple of examples.
As you can see in Figure
23-5
, the first session (
SPID=55
) holds the shared (S) lock on the row. The second session
(
SPID=54
) is trying to acquire exclusive (X) lock on the same row, and it is being blocked due to lock incompatibility.
The third session (
SPID=53
) is reading the same row in the
READ COMMITTED
transaction isolation level. This session
has not been blocked.
Figure 23-5.
Lock compatibility with more than two sessions
As you can see in Figure
23-6
, the third session did not even try to acquire the shared (S) lock on the row. There
is already a shared (S) lock on the row held by the first session (
SPID=55)
, which guarantees that the row has not been
modified by uncommitted transactions. In a
READ COMMITTED
isolation level, a shared (S) lock releases immediately
after a row is read. As a result, Session 3 (
SPID=55)
does not need to hold its own shared (S) lock after reading the row,
and it can rely on the lock from Session 1.
