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

2-phase-locking (2PL)

  • 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
        • wound-wait
        • wait-die

2-phase-commit (2PC)

  • 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.