4Transactions & concurrency control

• 4.1Transactions and ACID properties

  Covers the ACID properties—atomicity, consistency, isolation, and durability—that make up a transaction, and how transaction boundaries are designed with BEGIN/COMMIT/ROLLBACK, building the judgment needed to preserve business-process integrity.

• 4.2Concurrency control and locking

  Covers the choice of lock granularity via shared locks (S locks) and exclusive locks (X locks), and the growing phase and shrinking phase of the two-phase locking protocol (2PL), which guarantees serializability, building the judgment needed to choose the optimal concurrency-control approach from the tradeoff between throughput and consistency.

• 4.3Deadlock and isolation levels

  Covers detecting deadlock via the wait-for graph and countermeasures, the four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—and how they correspond to the three read anomalies dirty read, non-repeatable read, and phantom read, plus MVCC, building the judgment needed to choose the optimal isolation level from the balance between consistency and throughput.

• 4.4Failure recovery and logging

  Covers the WAL (write-ahead logging) principle of recording updates to the log before the data itself, checkpoints, which shorten the recovery starting point, rollback/roll-forward via UNDO/REDO after a failure, and the recovery approach for each failure type—transaction failure/media failure/system failure—building the judgment needed to preserve durability.

• 4.5Distributed transactions and two-phase commit

  Covers the prepare (commit-request) phase and commit phase of two-phase commit (2PC), which guarantees atomicity for a transaction spanning multiple database sites, the three-phase commit (3PC) that addresses its weakness, and the blocking problem in 2PC, building the judgment needed to decide how to ensure consistency in a distributed environment.