DBMS Characteristics: Transactions, Concurrency, Recovery, Integrity
Domain 4 - DBMS characteristics: transactions, concurrency control, recovery, integrity
The exam tests ACID, 排他制御, 隔離レベル, 障害回復 and 整合性制約. Expect scenarios asking which anomaly occurs or which recovery step applies.
Core rules
- ACID: 原子性, 整合性, 隔離性, 永続性.
- 共有ロック (S) permits concurrent reads; 専有ロック (X) excludes everything. 2相ロック (2PL) has a growing and a shrinking phase; strict 2PL holds X locks until commit.
- Isolation levels: READ UNCOMMITTED allows dirty reads; READ COMMITTED prevents dirty reads; REPEATABLE READ prevents non-repeatable reads; SERIALIZABLE prevents phantoms.
- Recovery uses a log of 更新前/更新後 images plus checkpoints: redo committed transactions after the checkpoint, undo uncommitted ones.
- 2相コミット (2PC) coordinates distributed commit in prepare and commit phases; it blocks while the coordinator is unavailable.
- Integrity: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY with CASCADE/SET NULL/RESTRICT.
Worked example
T1 reads balance=100, T2 reads 100; T1 writes 110, T2 writes 120. The last write wins and one update is lost - a lost update, possible under READ COMMITTED because reads take no lock. Fix with SELECT ... FOR UPDATE (X lock), SERIALIZABLE, or optimistic concurrency using a version column. Deadlock: T1 locks row A then requests B; T2 locks B then requests A. The wait-for graph has a cycle, so the DBMS picks a victim and rolls it back.
Traps
Confusing dirty read with non-repeatable read; assuming REPEATABLE READ removes phantoms; thinking a checkpoint eliminates all redo; forgetting undo for uncommitted transactions; and believing 2PC never blocks.