Advanced Locking Protocols - Transaction Processing - page 217

Chapter 9
Advanced Locking Protocols
The locking protocols presented thus far assume that the lockable units of the
database are single tuples. Such a choice is appropriate to transactions that access
a few tuples only. If a transaction accesses many tuples, it must also acquire
many locks. Each such access incurs the computational overhead of requesting and
perhaps waiting for the granting of the lock, and, in the case of a huge number of
commit-duration locks, the storage overhead on storing the locks in the lock table
until the commit of the transaction.
Using coarser units of locking such as whole relations is probably convenient
for a transaction that accesses many tuples. On the other hand, such coarse locks
discriminate against transactions that only want to lock one or two tuples of the
relation. Obviously, we need a locking protocol that allows a choice between
multiple granularities of lockable units. The problem with locks on units of different
granules is how to detect efficiently lock incompatibilities. The solution is to
introduce additional lock modes, called intention locks .
In this chapter we present the classical multi-granular locking protocol adapted
to our key-range locking protocol. We also argue why it is better to use units of the
logical database (i.e., relations and tuples) rather than units of the physical database
(i.e., pages and records) as lockable units.
For avoiding deadlocks caused by lock upgrades, we present a locking protocol
based on update-mode locks . This protocol is most suitable in implementing SQL
cursors in a deadlock-free way. We also discuss different ways to reduce the number
of commit-duration shared locks. The locking protocol for SQL cursors gives rise to
a new isolation level, located between the read-committed and repeatable-read levels
and called cursor stability.
A technique called C OMMIT -LSN can be used to reduce the number of short-
duration locks for transactions run at the read-committed isolation level. In con-
nection with ARIES recovery, this technique can also be used to reduce the time a
database system recovering from a failure is unable to accept new transactions.