AP Subject A 15: Transactions, Concurrency, and Recovery

Learning index

1. Scope and terms · 2. ACID and boundaries · 3. S/X locks and deadlock · 4. Anomalies and isolation · 5. DBMS differences · 6. WAL and recovery · 7. Five questions · 8. Review

1. Scope and terms

This maps to IPA Applied Information Technology Engineer Examination (Level 3) Syllabus Ver.7.2: Major Category 3 → Middle Category 9 Databases → 4 Transaction Processing, (1) concurrency control, (2) failure recovery, and (3) transaction management, at PDF pages 44–45 (printed footer pages 40–41). It does not reteach AP14 query SQL. The next installment, AP16, covers network layers, TCP/UDP, and ports; indexes, database performance, and access control are covered in AP32.

한국어	日本語	English
트랜잭션	トランザクション	transaction
원자성/일관성/격리성/지속성	原子性/一貫性/独立性/耐久性	atomicity/consistency/isolation/durability
공유(S)/배타(X) 잠금	共有(S)/排他(X)ロック	shared (S)/exclusive (X) lock
교착 상태	デッドロック	deadlock
더티/비반복/팬텀 읽기	ダーティ/反復不能/ファントムリード	dirty/non-repeatable/phantom read
미완료/완료 트랜잭션	未完了/完了トランザクション	loser/winner transaction
실행 취소/재실행	UNDO/REDO	undo/redo

2. ACID and transaction boundaries

• Atomicity: all changes in a transaction happen, or all are canceled.
• Consistency: a transaction preserves constraints and invariants and moves between valid states. It does not magically correct bad business logic; correct application logic is also required.
• Isolation: concurrent work follows the visibility rules promised by its isolation level.
• Durability: after a successful COMMIT, results survive under the promised failure model and durability configuration. It does not guarantee a remote backup.

BEGIN;
-- changes in one unit of work
COMMIT;   -- successful completion

BEGIN;
-- error or cancellation decision
ROLLBACK; -- explicit cancellation

A successful COMMIT, an explicit ROLLBACK, and a crash are distinct. After a crash, the DBMS may inspect logs and storage, REDO winners, and UNDO losers.

3. S/X locks and deadlock

Lock-based engines commonly use S for reads and X for changes.

Held \ requested	S	X
S	compatible	incompatible
X	incompatible	incompatible

Only S/S is compatible. Ordinary MVCC reads may not acquire the same blocking S locks, so do not project this table mechanically onto every read implementation.

Step	T1	T2
1	obtains X(A)	—
2	—	obtains X(B)
3	requests X(B) → waits	holds X(B)
4	holds X(A)	requests X(A) → waits
5	T1→T2→T1 wait cycle	deadlock

The DBMS commonly detects the cycle, chooses a victim, and rolls it back; the application may retry. Deadlock is cyclic waiting. A long wait need not contain a cycle, while starvation means one operation repeatedly fails to get an opportunity.

4. Three anomalies and isolation levels

Dirty read

Step	State/T1	T2 observes
0	initially X=100	—
1	writes X=70, uncommitted	—
2	—	reads X=70
3	ROLLBACK restores X=100	T2 already read a vanishing value

Non-repeatable read

Step	T1 observes	T2
0	initially X=100	—
1	reads X=100	—
2	—	writes X=120 and COMMITs
3	rereads X=120	—

Phantom read

Step	T1 observes	T2
0	qualifying IDs initially {1,2}	—
1	query returns {1,2}	—
2	—	inserts qualifying ID 3 and COMMITs
3	same query returns {1,2,3}	—

This is the SQL-standard phenomena table/minimum-guarantee model, not a universal prediction for every product.

Isolation level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	permitted	permitted	permitted
READ COMMITTED	prevented	permitted	permitted
REPEATABLE READ	prevented	prevented	permitted
SERIALIZABLE	prevented	prevented	prevented

5. DBMS implementation differences

These first-party summaries were checked 2026-10-21. They are neither exhaustive nor invariant across versions/configurations; check the documentation for the deployed version.

• PostgreSQL current: READ UNCOMMITTED behaves as READ COMMITTED. REPEATABLE READ uses a transaction snapshot, exceeds the minimum, and prevents phantoms. Serializable uses SSI and may require retry after a serialization failure.
• MySQL InnoDB 8.4: REPEATABLE READ is the default; consistent reads use a transaction snapshot. Locking reads/statements and next-key/gap locks affect phantoms and blocking depending on statement/configuration. The official URL may return HTTP 403 to automated clients but remains the cited first-party manual.
• SQL Server: READ COMMITTED depends on READ_COMMITTED_SNAPSHOT. SNAPSHOT is separate; locking/versioning configuration changes blocking and visibility.
• Oracle Database: supports READ COMMITTED and SERIALIZABLE transaction isolation (plus read-only mode). Reads use statement/transaction consistency rather than exposing all four standard level names.
• SQLite: serializes writes. Rollback-journal and WAL modes differ; WAL readers see snapshot isolation. read_uncommitted is exceptional and relevant only under shared-cache conditions.

6. WAL, checkpoints, and UNDO/REDO

The write-ahead rule requires log records to reach stable storage before their corresponding dirty data page. A successful commit requires a durable commit record under the configured durability promise. A checkpoint is not a backup, and a fuzzy checkpoint need not flush every page.

Assume WAL, STEAL (an uncommitted page may reach disk), and NO-FORCE (commit need not force every data page).

LSN	Record/event	Storage consequence
—	initial	A=100, B=200, C=300
10	T0 updates C 300→330	—
20	T0 COMMIT	—
30	CHECKPOINT active={}	assume metadata safely accounts for T0; C=330 survives/replays as needed
40	T1 BEGIN	—
50	T1 updates A 100→80, uncommitted	A page reaches disk early under STEAL
60	T2 BEGIN	—
70	T2 updates B 200→260	B page has not reached disk under NO-FORCE
80	T2 COMMIT; then crash	disk immediately before recovery: A=80, B=200, C=330

T1 is a loser: UNDO A 80→100. T2 is a winner: REDO B 200→260. T0 is safely accounted for by the checkpoint assumption, leaving C=330. Final state: A=100, B=260, C=330. This is the exam-level winner/loser rule under the stated assumptions. Exact ARIES pageLSN, compensation-record, and checkpoint mechanics vary; not every storage design needs both operations on every crash.

7. Five original questions

Q1

Which ACID/boundary statement is correct?

• ア. Consistency means the DBMS automatically repairs bad business logic.
• イ. Durability means every COMMIT immediately creates a remote backup.
• ウ. Atomicity means all transaction changes happen or none do, and ROLLBACK explicitly cancels them.
• エ. Successful COMMIT and crash recovery are the same event.

Answer: ウ (3). ア ignores the need for correct logic. イ confuses promised durability with backup. ウ is precise. エ conflates distinct boundaries.

Q2

T1 holds X(A), T2 holds X(B), then T1 requests X(B) and T2 requests X(A). Which is most accurate?

• ア. It is a dirty read caused by an S/S conflict.
• イ. It is an X-lock wait cycle; the DBMS may roll back a victim and the application may retry.
• ウ. Neither request conflicts, so both proceed immediately.
• エ. Any long wait is necessarily deadlock; no cycle is needed.

Answer: イ (2). ア has the wrong locks and phenomenon. イ describes deadlock. ウ ignores X/X incompatibility. エ confuses waiting with a cycle.

Q3

Which statement correctly relates the standard table to implementations?

• ア. READ COMMITTED prevents all three phenomena.
• イ. Every REPEATABLE READ implementation must permit phantoms.
• ウ. SQL Server READ COMMITTED always locks identically regardless of configuration.
• エ. The table is a minimum-guarantee model; products such as PostgreSQL may be stronger, so check version/configuration documentation.

Answer: エ (4). ア ignores non-repeatable and phantom reads. イ ignores stronger products. ウ ignores READ_COMMITTED_SNAPSHOT. エ makes the correct distinction.

Q4

Initially X=100. T1 writes 70 without committing; T2 reads 70; T1 then ROLLBACKs to 100. What did T2 experience?

• ア. Dirty read
• イ. Non-repeatable read
• ウ. Phantom read
• エ. Deadlock

Answer: ア (1). ア is reading uncommitted data. イ requires two reads around a committed value change. ウ changes a qualifying row set. エ requires a wait cycle.

Q5

In the recovery trace above, disk is A=80, B=200, C=330 just before the crash. What is the recovered state?

• ア. A=80, B=200, C=330
• イ. A=80, B=260, C=300
• ウ. A=100, B=260, C=330
• エ. A=100, B=200, C=300

Answer: ウ (3). ア omits loser UNDO and winner REDO. イ retains T1 and loses safe T0. ウ UNDOs T1, REDOs T2, and preserves T0. エ omits T2 and loses T0.

8. Quick sheet, traps, and review

Quick sheet: ACID = all-or-nothing, valid states, promised visibility, promised durability. Only S/S is compatible. Deadlock is a wait cycle. Standard baseline: RU permits all; RC prevents dirty; RR also prevents non-repeatable; SERIALIZABLE prevents all three. STEAL creates possible UNDO; NO-FORCE creates possible REDO.

Traps: Do not turn consistency into “always correct data,” durability into backup, the lock matrix into a universal MVCC-read rule, a long wait into deadlock, a checkpoint into backup, or minimum guarantees into product predictions.

Self-check: Recreate the lock matrix; distinguish all three schedules by exact values/sets; explain UNDO/REDO from winner/loser and disk state.

1/3/7-day plan: Day 1, reproduce ACID and both matrices from memory. Day 3, recompute the three schedules and crash trace. Day 7, answer all five questions and explain every distractor; recheck product differences in official docs.

Official sources

All checked 2026-10-21. Product behavior uses first-party sources only; no third-party commentary was used.

• IPA, Applied Information Technology Engineer Examination (Level 3) Syllabus Ver.7.2, PDF 44–45 (printed footer 40–41)
• PostgreSQL · MySQL 8.4 · SQL Server · Oracle Database 19c · SQLite

This is independently created study material, unaffiliated with IPA or the DBMS vendors. It guarantees neither examination success nor specific product behavior.