Pessimistic Concurrency Control
April 8, 2021
Garcia-Molina/Ullman/Widom: Ch. 18.3-18.7, 19.2
- Schedule
- A sequence of read and writes from one or more transactions to objects
- Serial Schedule
- A schedule with no interleaving
- ... but with an arbitrary transaction order
- Serializable Schedule
- A schedule that produces the same output as a serial schedule
| Time | T1 | T2 | T3 |
|---|---|---|---|
| | | W(A) |
||
| | | W(A) |
||
| | | W(B) |
||
| | | W(B) |
||
| ↓ | W(B) |
T1's write to A "happens before" T2's write
T2's write to B "happens before" T3's write
T2's write to B "happens before" T1's write
Cycle! No equivalent serial schedule!
An acyclic "Happens Before" or Dependency Graph is conflict serializable.
Forcing Acyclicity
- Locking
- Snapshot Isolation
- Timestamp Concurrency Control*
Goal: Enforce acyclic conflict graphs
Observation: Conflicts only occur on accesses to the same object
Idea: When a second transaction tries to access an object, require the first to COMMIT or ABORT first.
Idea: When a second transaction tries to access an object, require the first to COMMIT or ABORT first agree to never create more conflicts.
2-Phase Locking
Create one lock for each object.
Each transaction operates in two "phases".
- Acquire Phase
- Before accessing an object, the transaction must acquire the object's lock.
- The transaction does not release locks.
- Release Phase
- A transaction can release locks
- A transaction can never again access an object it doesn't have a lock for.
In practice, the release phase happens all at once at the end
| Time | T1 | T2 | T3 |
|---|---|---|---|
| | | R(C) |
||
| | | W(A) |
||
| | | R(B) |
||
| | | W(C) |
||
| | | W(B) |
||
| ↓ | W(C) |
Conflict Serializable.
Can 2PL create this schedule?
L(...) | Acquire (Lock) |
U(...) | Release (Unlock) |
2PL can create this schedule
| Time | T1 | T2 | T3 |
|---|---|---|---|
| | | |
L(C)R(C) |
||
| | | |
L(A)W(A) |
||
| | | |
L(B)R(B) |
||
| | | |
W(C)U(C) |
||
| | | |
L(C)U(B) |
||
| | | |
L(B)W(B) |
||
| | ↓ |
L(C)W(C) |
Optimizations
Observation 1: Read-Read conflicts aren't a problem
Solution: Reader/Writer Locks
(Any number of readers or one writer can hold the lock)
... also called Shared (S)/Exclusive (X) locks
| Requested | |||
| S | X | ||
| H e l d |
S | Allow | Block |
| X | Block | Block | |
An object is ...
- ... a table
- ... a record
- ... a page
- ... a column
Which should be used?
Observation 1: Too coarse locking prevents concurrency
Observation 2: Too fine locking is slow
Idea 1: Separate locks for tables, pages, rows, cells.
Doesn't Work! Need to use the same lock for all conflicts.
Idea 2: Organize locks hierarchically. Set a flag in the parent when a child is locked.
New Lock Modes
- Exclusive (X)
- The holding thread can safely modify the object
- Shared (S)
- A holding thread can safely read the object
- Intent-Exclusive (IX)
- Descendant locks may be held Exclusive
- Intent-Shared (IS)
- Descendant locks may be held Shared
Before acquiring a descendant lock, a thread must first intent-acquire all ancestors (top-down)
| Requested | |||||
| IS | IX | S | X | ||
| H e l d |
None | Allow | Allow | Allow | Allow |
| IS | Allow | Allow | Allow | Block | |
| IX | Allow | Allow | Block | Block | |
| S | Allow | Block | Allow | Block | |
| X | Block | Block | Block | Block | |
Example 1
Example 2
| Time | T1 | T2 | T3 | T4 |
|---|---|---|---|---|
| | | S(A) |
|||
| | | R(A) |
|||
| | | X(B) |
|||
| | | W(B) |
|||
| | | S(B) |
|||
| | | S(C) |
|||
| | | R(C) |
|||
| | | X(C) |
|||
| | | X(B) |
|||
| ↓ | X(A) |
Deadlock
A cycle of transactions waiting on each other's locks
Approach 1 Detect deadlock situations as they occur and abort deadlocked transaction
Approach 2 Anticipate deadlock situations before they happen
The Waits-For Graph
- Nodes
- Every running transaction
- Directed Edges
- From a transaction blocked to the transaction it's waiting for
| Time | T1 | T2 | T3 | T4 |
|---|---|---|---|---|
| | | S(A) |
|||
| | | R(A) |
|||
| | | X(B) |
|||
| | | W(B) |
|||
| | | S(B) |
|||
| | | S(C) |
|||
| | | R(C) |
|||
| | | X(C) |
|||
| | | X(B) |
|||
| ↓ | X(A) |
A cycle is a set of transactions that will never finish
Periodically check for cycles in the waits-for graph
Abort transactions until the cycle is broken
Idea 2: Create an order over the locks (give each a #)
Only allow locks to be acquired in sequence order
| Time | T1 | T2 | T3 | T4 |
|---|---|---|---|---|
| | | S(A) |
|||
| | | R(A) |
|||
| | | X(B) |
|||
| | | W(B) |
|||
| | | S(B) |
|||
| | | S(C) |
|||
| | | R(C) |
|||
| | | X(C) |
|||
| | | X(B) |
|||
| ↓ | X(A) |
T3 can't acquire X(A) because it already has S(C)
Idea 2.B: Acquire all locks at the start of a transaction.
Pro: No deadlocks... ever.
Pro: No (expensive) cycle detection.
Con: Not all transactions are supported
or transactions need to know all necessary locks in advance.
Idea 3: False positive deadlock detections are ok
Trivial solution: Timeouts
... but how long should the timeout be?
Alternative: Create an order over transactions
Only allow a transaction to block on "older" transactions
Variant 1
- T1 holds a lock on A
- T2 tries to acquire the lock on A and blocks
- (ok, because T1 is "older")
- T2 holds a lock on A
- T1 tries to acquire the lock on A
- ABORT T1 and restart it as a "younger" transaction
"Wait-Die"
Variant 2
- T1 holds a lock on A
- T2 tries to acquire the lock on A and blocks
- (ok, because T1 is "older")
- T2 holds a lock on A
- T1 tries to acquire the lock on A
- ABORT T2 and restart it at the same age.
"Wait-Wound"
- Wait-Die
- Abort an older transaction that tries to wait on a younger one.
- Wait-Wound
- If an older transaction tries to wait on a younger one, kill the younger.
Managing Deadlocks
- Approach 1: Detection
- Detect cycles (or conditions that could indicate cycles)
- Abort transactions until the cycles go away
- Approach 2: Recovery
- Enforce an invariant on lock acquisition order
- Acquire all locks upfront