Two Phase Locking with two-phase commit (2PL+2PC)
How to ensure a serializable schedule?
- Locking-based approach
- Strawman solution 1:
- Grab global lock when transaction starts
- release global lock when transaction finishes committing
- Strawman solution 2:
- grab lock on item X before read/write X
- release lock on item X after read/write X
Problem with strawman 2?
- Permits this non-serializable interleaving
R_1(C), R_1(S), W_1(C), R_3(C), R_3(S), Commit3, W_1(S), Commit1
- Look at W_1(C), if lock on C is released, then another transaction T’ can read new value of C, but T’ must be able to read new value of S that T has not yet written yet.
- If write lock is not held, —> read of uncommitted value
- So, write lock must be held till the transaction commits
(Extra question) How about read locks, must it also be held till transaction commits?
R_3(C), R_1(C), R_1(S), W_1(S), W_1(S), Commit1, R_3(S), Commit3
- This is a non-serializable schedule
- R_3(C) reads old value
- R_3(S) reads new value (non-repeatable reads)
- So, read locks must also be held till commit time
- a growing phase in which transaction is acquiring locks
- a shrinking phase in which locks are released
- in practice
- growing phase is the entire transaction
- shrinking phase is during commit
- Optimization: use read/write locks instead of exclusive locks
- More on 2PL:
- what is a lock is unavailable?
- deadlock possible?
- how to cope with deadlocks?
- grab locks in order? not always possible
- (central) system detects deadlock cycles and aborts involved transactions
- deadlock prevention
- lock without 2PC
- coordinator –> server-a: lock a, log a=1, write a=1 to database state, unlock a
- coordinator –> server-b: lock b, log b=1, write b=1 to database state, unlock b
- Problem (failure to commit)
- what if transaction cannot commit (e.g. deadlocks)
- Problem (failure):
- coordinator crashes after message to a (before message to b).
- A later transaction T2 sees a=1, but the information about b=1 is permanently lost!
- Problem (serializability violation)
- a’s message to server A arrives read(a = 1) read(b = 0)
- b’s message to server B arrives
- 2PC (Two-phase commit)
- coordinator –> server-a: prepare-T1: server-a lock a, logs a=1,
- coordinator –> server-b: prepare-T1: server-b lock b, logs b=1
- coordinator –> server-a: commit-T1: write a=1 to database state, unlock a
- coordinator –> server-b: commit-T1: write b=1 to database state, unlock b
- Now if coordinator crashes after prepare-a before prepare-b, a recovery protocol should abort T1
- (no other transactions can read a=1 since a is still locked)
- Now if coordinator crashes after commit-a before commit-b, a recovery protocol should send commit-T1 to b.
- How does the recovery protocol work? Two options:
- Option 1:
- Coordinator can unilaterially determine the commit status of a transaction
- e.g. Coordinator receives prepare-ok(T1) from server-a, but times out on server-b,
- Coordinator can abort T1 (even if server-b has successfully prepared T1).
- Coordinator durably logs its decision (e.g. to a Paxos RSM).
- Recovery protocol reads from coordinator’s log to decide to commit or abort.
- Option 2:
- Coordinator can not unilaterially determine the commit status of a transaction
- If both server-a and server-b successfully prepared T1, then T1 must commit
- participants log must be durable against failure (e.g. log replicated via Paxos RSM)
- Recovery protocol must read all participating server’s log to decide commit or abort.
- Option 1: