Courses / HEC NSCT – National Skill Competency Test: Complete Preparation for Computing Graduates
4.4 Transactions, Concurrency and Recovery
Area 4 – Databases (10%) · 45 min read · 9 MCQs to check yourself
A transaction is a unit of work that must be completed fully or not at all (e.g. transfer money: debit A, credit B).
ACID
| Property | Meaning |
|---|---|
| Atomicity | All or nothing (rollback on failure) |
| Consistency | Moves the database from one valid state to another (rules respected) |
| Isolation | Concurrent transactions do not interfere |
| Durability | Once committed, changes survive crashes (logs) |
Concurrency problems
| Problem | What happens |
|---|---|
| Lost update | Two transactions overwrite each other's update |
| Dirty read | Reading data written by an uncommitted transaction |
| Non-repeatable read | Same row read twice gives different values |
| Phantom read | Same query returns new rows inserted by another transaction |
| Isolation levels (weak → strong): Read Uncommitted → Read Committed → Repeatable Read → Serializable. |
Concurrency control
- Two-Phase Locking (2PL): growing phase (acquire locks) then shrinking phase (release) – guarantees serializability. Shared (S, read) and exclusive (X, write) locks.
- Deadlock: T1 waits for T2 and T2 waits for T1 – detect with a wait-for graph and abort one transaction.
- Timestamp ordering, MVCC (PostgreSQL keeps multiple versions).
Recovery
Write-ahead logging (WAL): log records are written before data pages. After a crash: redo committed transactions, undo uncommitted ones. Checkpoints shorten recovery.
ACID properties in databases with examples (ByteByteGo)
This lesson ends with a quiz of 9 MCQs. Open the lesson on PrepOasis to take it and save your progress.
Previous: 4.3 Normalization – 1NF to BCNF · Next: 4.5 Indexing, Query Optimization, NoSQL and Data Warehousing