Advanced Locking Protocols - Transaction Processing - page 227

is found out whether or not the tuple needs to be updated. If the tuple needs to be
updated, the S lock on key x is upgraded to an X lock, and this lock is kept until
the transaction commits. If there is no need for the update, the S lock on x can be
released.
The above solution prevents lost updates. However, other types of unrepeatable
reads can of course still be present. The isolation level that prevents lost updates is
called cursor stability in the literature, and it lies between levels 2 ı and 3 ı .
A locking protocol that ensures cursor stability but otherwise allows unrepeatable
reads can be automatically implemented only if there exists a suitable programming
language structure, such as the cursor mechanism of SQL . There, the key of the
current tuple (current of cursor) is S-locked, and the lock is upgraded to a commit-
duration X lock if the tuple is updated. Otherwise, the S lock is released when the
next tuple is fetched. Reads that are not done using a cursor have to be protected
with commit-duration S locks. To prevent deadlocks caused by upgrading S locks,
update-mode locks (U locks) can be used instead of S locks: the U lock is upgraded
to an X lock if the tuple is updated; otherwise, it is released.

9.6 Reads Without Locks

Assume that transaction T is to be run at isolation level 2 ı (read committed). With
the locking protocols presented above, this level is maintained when a commit-
duration X lock is acquired for each update action and a short-duration S lock for
each read action by T . The only purpose of the S lock is to prevent a dirty read, that
is, make sure that the tuple to be read is in a committed state during the read action.
However, the transactions acquire just as many locks in this scheme as in the
locking protocol that ensures level-3 ı isolation. Besides, releasing short-duration
S locks individually may take more time than releasing the same number of commit-
duration locks all at once at transaction commit.
The following simple idea can be used to reduce the number of short-duration
S locks needed. Let C OMMIT -LSN be the minimum of the LSN s of the begin-
transaction log records of all currently active transactions. That is, C OMMIT -LSN is
the LSN of the begin-transaction log record h T 0 ;B i for the oldest transaction T 0 that
is still active.
For maintaining the C OMMIT -LSN , the active-transaction table needs to include for
each active transaction—in addition to its identifier, state (forward- or backward-
rolling), and U NDO -N EXT -LSN and L AST -L OCK fields—a B EGIN -LSN field that contains
the LSN of the begin-transaction log record of the transaction.
Assume that transaction T , running at isolation level 2 ı , wants to read tuples
from data page p, which has already been fixed and read-latched by the server-
process thread that executes T . Now assume that

P AGE -LSN .p/ < C OMMIT -LSN :