Lock-Based Concurrency Control - Transaction Processing - page 123

Chapter 6
Lock-Based Concurrency Control
Locking is the most commonly used method for enforcing transactional isolation.
Most database management systems apply some kind of locking, possibly coupled
with some other mechanism (such as transient versioning). With locking-based
concurrency control, transactions are required to protect their actions by acquiring
appropriate locks on the parts of the database they operate on. A read action on a
data item is usually protected by a shared lock on the data item, which prevents
other transactions from updating the data item, and an update action is protected by
an exclusive lock, which prevents other transactions from reading or updating the
data item.
If a transaction requests a lock on a data item in a situation in which some other
transaction holds an exclusive lock on the data item, the requesting transaction has
to wait for the conflicting lock to be released. Most often this means waiting for the
other transaction to commit; all the locks still held by a transaction at commit time
are released when the transaction has committed.
The isolation anomalies defined in the previous chapter for our key-range
transaction model can be avoided with a locking protocol called key-range locking .
This protocol prevents phantoms by effectively locking a range between two keys
with a shared lock on one key (for a read action) or with exclusive locks on
two successive keys (for an insert or a delete action). We give a proof of the
correctness of key-range locking in the general case in which transactions can
contain partial rollbacks with the feature that commit-duration locks acquired after
setting a savepoint are released after completing a partial rollback to that savepoint.
A problem with locking is that transactions waiting for locks may get into a
deadlock , where none of the transactions can proceed. Deadlocks must be resolved
by aborting and rolling back one or more of the transactions involved. With ordinary
lock requests, which automatically lead to a wait if the lock cannot be immediately
granted, deadlocks involving holds of and waits for latches may also occur. In this
case relying on deadlock resolution is not feasible; the occurrences of deadlocks
between locks and latches must be prevented. The solution is to use a conditional