Information Technology Reference
In-Depth Information
Ulockonx, other transactions cannot get any new locks on x, but they can keep
the S locks that they may already have on x.
When a transaction that holds a U lock needs to update the locked data item x,it
must upgrade its U lock to an X lock. To do this, the transaction must wait for all of
the readers of x to release their S locks. Thus, when a transaction T holds a U lock
on x, the operation
lock
.T; x; X;d/possibly causes T to wait for the granting of the
lock.
The automatic acquiring of U locks requires that the query language has a way
of hinting the system about this. In embedded
SQL
, when a cursor has been created
with the attribute
for update
, U locks instead of S locks are acquired on the tuples
touched by the cursor. If the current tuple fetched by the cursor is updated, the
U lock is upgraded to an X lock that is held until the transaction commits. If the
current tuple is not updated and the cursor is advanced to fetch the next tuple, the
U lock is either downgraded to an S lock (when the repeatable-read isolation level
is maintained) or released altogether (at the read-committed isolation level).
The mutual exclusivity order for the lock modes S, U, and X is
S < U < X.
The lock-compatibility matrix for the set of lock modes
f
S; U; X
g
is asymmetric
(see Fig.
9.5
). In the lock-upgrade matrix (Fig.
9.6
), the value “
” means that the
lock upgrade is not permitted.
Theorem 9.5
The locking protocol with S, U, and X locks prevents deadlocks that
would be caused by lock upgrades.
Proof
According to the protocol, for any data item x, only one transaction at a
time can hold a U lock on x. Thus, only one transaction can be waiting to upgrade a
Ulockonx into an X lock. On the other hand, upgrades of S locks are not permitted
at all.
t
Fig. 9.5
Lock-compatibility
matrix for update-mode
locking with the lock modes
S, U, X
Fig. 9.6
Lock-upgrade
matrix for update-mode
locking with the lock modes
S, U, X
