Chapter 18 Notes:
Concurrency Control Techniques
Protocols are sets of rules that guarantee serializability (concept from Chap. 17).
Different protocols from this Chapter:
Locking Protocols
Timestamping
Transaction validation/certification
Granularity of locks - one attribute, one row, disk block, table, database
Two-Phase Locking:
Binary Locks: state of 0 if unlocked, state of 1 if locked
2-mode locks: called read/write or shared/exclusive locks
SharedExclusive Locking Scheme:
1) A transaction T must issue either a read_lock(X) or write_lock(X) before any read_item(X) operation is performed in T
2) A transaction T must issue either a write_lock(X) before any write_item(X) operation is performed in T
3) A transaction T must issue the operation unlock(X) after all read_item(X) and write_item(X) operations are completed in T
4) A transaction T will not issue a read_lock(X) operation if it already holds a read or write lock on X
5) A transaction T will not issue a write_lock(X) operation if it already holds a read or write lock on X
6) A transaction T will not issue an unlock(X) operation unless it already holds a read or write lock on X
In steps 4, 5 sometimes we allow lock conversion or upgrades from a read to a write
What is Two-Phase Locking: each transaction must always have a pure growing phase followed by a pure shrinking phase. I.e., acquires all locks needed before releasing any.
Deadlock - Each transaction T in a set of two or more transactions is waiting for some item that is locked by another transaction in the set.
Deadlock prevention protocols:
Conservative 2-Phase - requires that each transaction lock all the items it needs in advance (not practical in general) - if any can't be obtained, none are locked.
Two other deadlock prevention schemes use the concept of a timestamp-
TS(T) unique id assigned to each transaction - the time it started, basically
DPPs based on timestamps -
Suppose Ti tries to lock X but can't since X is locked by some other Transaction Tj
(Note TS(Ti) < TS(Tj) means Ti is older, it has a smaller timestamp)
Wait-die: If TS(Ti) < TS(Tj) (Ti older than Tj) Ti is allowed to wait; otherwise abort Ti (Ti dies) and restart it later with the same timestamp
Wound-wait: If TS(Ti) < TS(Tj) (Ti older than Tj) abort Tj and restart it later with the same timestamp; otherwise Ti is allowed to wait
Cautious waiting (to reduce number of needless aborts): If Tj is not blocked (not waiting for some other locked item), then Ti is blocked and allowed to wait, otherwise abort Ti
Deadlock detection - done with a wait-for graph
Followed by victim selection
Timeouts - simple scheme
Starvation - a transaction keeps getting picked to be aborted and never makes any forward progress while others do
Timestamp Ordering (TO) Algorithm - avoid deadlock altogether
TS (T) - have the timestamps of transactions
read_TS(X) - the largest (youngest) of the timestamps of transactions that have successfully read X
write_TS(X) - the largest (youngest) of the timestamps of transactions that have successfully written X
PLEASE NOTE - THE ONLY TIMESTAMP VALUES ARE THE ONES THAT THE TRANSACTIONS THEMSELVES GET!!!
How does it work?
The Basic TO Algorithm - checks to see whether or not conflicting operations violate the timestamp ordering of the transactions:
Suppose -
1) Transaction T issues a write_item(X) operation:
a) If read_TS(X) > TS(T) or if write_TS(X) > TS(T), then abort and roll back T and reject the operation. This should be done because some younger transaction with a timestamp greater than TS(T) - and hence after T in the timestamp ordering - has already read or written the value of item X before T had a chance to write X, thus violating the timestamp ordering.
b) If the condition in part a) does not occur (i.e. - read_TS(X) <= TS(T) and write_TS(X) <= TS(T) ), then execute the write_item(X) operation of T and set write_TS(X) to TS(T).
2) Transaction T issues a read_item(X) operation:
a) If write_TS(X) > TS(T), then abort and roll back T and reject the operation. This should be done because some younger transaction with a timestamp greater than TS(T) - and hence after T in the timestamp ordering - has already written the value of item X before T had a chance to read X, thus violating the timestamp ordering.
b) If write_TS(X) <= TS(T), then execute the read_item(X) operation of T and set read_TS(X) to the larger of TS(T) and the current read_TS(X).
Basic TO guarantees conflict serializability. It can cause cascading rollback, however.
Multiversion Concurrency Control Techniques -
These techniques keep the old values of a data item when the item is being updated. (note: Oracle does this!)
Multiversion Two-Phase Locking (Multi 2PL) Using Certify Locks -
Here there are 3 locking modes for an item: read, write and certify
Check out the lock compatibility matrix for read, write and certify from the online powerpoints for this chapter.
Multi 2PL allows other transactions T' to read an item X while a single transaction T holds a write lock on X. Here is how it works
There are two versions of each data item X; one must have been written by some committed transaction (the old one).
The second version X' is created when transaction T acquires a write lock on the item. Other transactions can see the committed version (old version) of X while T holds the write lock. T can write (modify) the value of X' as needed without affecting the old version X.
Once T is ready to commit, it must obtain a certify lock on all items that it currently holds write locks on before it can commit. The certify lock is not compatible with read locks, so the transaction may have to delay its commit until all it write-locked items are released by any reading transactions in order to obtain certify locks. Once the certify locks - which are exclusive locks - are acquired, the committed version X is then set to the value of version X', version X' is discarded, and the certify locks are released.
This scheme avoids cascading rollbacks, but deadlock can occur and will have to be dealt with. Note - Oracle does not do this.... it issues a "snapshot too old" error to the reading transactions when it can no longer give a consistent read image.
Work problem 8.25 for "homework"!