Cambridge 9618 · International A Level Computer Science · ~16 min read
Notes
Video
Slides
Quiz
Worksheet
What is a Transaction?
A transaction is a sequence of one or more database operations (reads, writes, updates) that are treated as a single logical unit of work. Either all operations in the transaction complete successfully, or none of them are applied to the database.
The classic example is a bank transfer — debiting one account and crediting another. Both operations must succeed together; it would be disastrous for only one to complete.
Bank Transfer — Transaction example
1. BEGIN TRANSACTION 2.READ balance FROM Account WHERE id = 'Alice' → £1000 3.UPDATE Account SET balance = 700 WHERE id = 'Alice' 4.UPDATE Account SET balance = 800 WHERE id = 'Bob' ← FAILS (Bob's account frozen) 5.⟳ ROLLBACK — Alice's debit is UNDONE. Both accounts unchanged.
—— OR if everything succeeds —— 5.COMMIT — all changes are permanently saved.
ACID Properties
The four ACID properties guarantee that database transactions are processed reliably, especially in the face of errors, power failures, and concurrent users.
A
Atomicity
"All or nothing"
A transaction is treated as a single, indivisible unit. Either ALL operations within the transaction are completed successfully and committed, OR if any operation fails, NONE of the changes are applied — the database is rolled back to its previous state. No partial transactions are allowed.
C
Consistency
"Valid state to valid state"
A transaction brings the database from one VALID state to another VALID state. It must not violate any integrity constraints, referential integrity rules, or business rules. If a transaction would break a constraint (e.g. a foreign key), it is not committed — the database remains consistent.
I
Isolation
"Concurrent transactions don't interfere"
When multiple transactions run concurrently, each transaction executes as if it were the ONLY transaction running. The intermediate state of a transaction is not visible to other concurrent transactions. This prevents one transaction from reading or modifying data that another transaction is in the middle of updating.
D
Durability
"Committed = permanent"
Once a transaction is COMMITTED, its changes are permanently saved and will survive system failures (power cuts, crashes). The database uses mechanisms like transaction logs and write-ahead logging (WAL) to ensure committed data can be recovered. A committed transaction cannot be undone by a system crash.
COMMIT and ROLLBACK
COMMIT ✓
Signals that the transaction has completed successfully. All changes made during the transaction are permanently saved to the database. After COMMIT, the data is durable — it persists even if the system crashes moments later.
ROLLBACK ✗
Signals that the transaction has failed or been aborted. ALL changes made during the transaction are undone — the database is restored to the exact state it was in BEFORE the transaction began. Ensures atomicity in the event of failure.
Concurrency Problems (without isolation)
When multiple users access a database simultaneously without proper isolation, data corruption can occur in several ways:
🧹 Dirty Read
Transaction A reads data that Transaction B has modified but NOT yet committed. If B then rolls back, A has read data that never officially existed — a "dirty" (invalid) read.
💥 Lost Update
Two transactions both read the same value, both calculate an update based on the original, and both write back. The second write overwrites the first — one update is permanently lost.
👻 Phantom Read
Transaction A runs the same query twice. Between the two reads, Transaction B inserts or deletes rows that match the query. A sees different results for the same query — phantom rows appear or disappear.
Concurrency Control — Locking
Databases use locking to enforce isolation and prevent concurrency problems. A lock prevents other transactions from accessing data while it's being used.
🔒 Shared Lock (Read lock)
Allows multiple transactions to READ the same data simultaneously. No transaction can WRITE to the data while a shared lock is held. Multiple shared locks can coexist on the same data item.
Prevents: dirty reads, lost updates (partially).
🔐 Exclusive Lock (Write lock)
Only ONE transaction can hold an exclusive lock. No other transaction can READ or WRITE the data while an exclusive lock is held. Acquired before updating data.
Prevents: all concurrency problems while held. Released on COMMIT or ROLLBACK.
Deadlock
A deadlock occurs when two or more transactions are each waiting for the other to release a lock — creating a circular dependency where neither can proceed.
Deadlock example
Transaction A: holds LOCK on Account(Alice), waiting for LOCK on Account(Bob)
Transaction B: holds LOCK on Account(Bob), waiting for LOCK on Account(Alice) → Neither can proceed — DEADLOCK. DBMS detects and rolls back one transaction.
The DBMS (Database Management System) detects deadlocks using timeouts or wait-for graphs, and resolves them by rolling back one of the transactions (the "victim").
Recovery — Checkpoints and Transaction Logs
To support durability, databases maintain a transaction log (journal) — a record of every operation performed. This enables recovery after a system failure.
Mechanism
Description
Transaction log (journal)
Records every operation: what was changed, old value, new value. Used for UNDO (rollback) and REDO (replay committed transactions after a crash).
Checkpoint
A periodic snapshot of the database state written to stable storage. Recovery starts from the most recent checkpoint — only transactions after the checkpoint need to be replayed, making recovery faster.
Write-ahead logging (WAL)
Changes are written to the transaction log BEFORE they are applied to the database. This ensures recovery is always possible — the log can be replayed to restore committed changes.
Summary — ACID in one sentence each
Property
One-sentence definition
Mechanism
Atomicity
All operations succeed or none are applied
ROLLBACK / COMMIT
Consistency
Transaction leaves database in a valid state
Integrity constraints / validation
Isolation
Concurrent transactions don't see each other's intermediate state
Locks (shared/exclusive)
Durability
Committed changes survive system failures
Transaction log / WAL / checkpoints
Cambridge 9618 exam tip: When asked to describe an ACID property, give the definition AND explain HOW it is achieved mechanically. For Atomicity → mention ROLLBACK/COMMIT. For Consistency → mention constraints. For Isolation → mention locking. For Durability → mention transaction logs. If asked "what happens when a transaction fails" — the key word is ROLLBACK. If asked about concurrent users — mention isolation and locking. "Deadlock" requires explaining that both transactions hold a lock the other needs and neither can proceed — the DBMS resolves by rolling one back.
⚠️ Common Mistakes
Confusing Atomicity and Durability — Atomicity = all or nothing (ROLLBACK if fail). Durability = committed data is permanent (survives crash). They are different; both are needed. An "atom" cannot be split — likewise, an atomic transaction cannot be partially applied.
Saying COMMIT "saves to disk" and ROLLBACK "doesn't save" — COMMIT makes changes PERMANENT (durable). ROLLBACK UNDOES all changes in the transaction. The distinction is permanent-and-applied vs completely-undone, not just about disk writing.
Confusing shared and exclusive locks — SHARED lock: many readers allowed, no writers. EXCLUSIVE lock: only one transaction, no readers or writers. Remember: exclusive means "only me".
Forgetting Consistency is about constraints, not just data accuracy — Consistency means the transaction does not violate integrity constraints (NOT NULL, foreign keys, check constraints). It's the database's rules, not a general sense of "correctness".
Deadlock definition — A deadlock is NOT just "two transactions waiting" — it specifically requires each waiting for a lock held BY THE OTHER. Circular dependency is the key. Without the circular dependency, one will eventually get its lock and proceed (no deadlock).
✅ Notes completed!
▶
Video coming soon
Click slide or press arrow keys to navigate
Worksheet — 4.5.3 Transactions & ACID
8 questions · Cambridge 9618 standard
Q1What is a database transaction? State why treating operations as a transaction is important when transferring money between bank accounts.[4]
✅ Mark scheme
Transaction: a sequence of one or more database operations (reads/writes/updates) that is treated as a single, indivisible logical unit of work [1]; importance for bank transfer: the transfer involves TWO operations — debiting one account AND crediting another [1]; both must succeed together; if only the debit succeeds (e.g. the credit fails due to a system error), money disappears from one account but never arrives in the other — an unacceptable inconsistency [1]; using a transaction ensures atomicity — if either operation fails, the entire transaction is ROLLED BACK and both accounts remain unchanged, as if the transfer never started [1].
Q2Describe each ACID property, stating in each case how the property is enforced by the DBMS.[8]
✅ Mark scheme
Atomicity: all operations in a transaction are completed or none are applied — "all or nothing"; enforced via COMMIT (confirm all changes permanently) and ROLLBACK (undo all changes if any operation fails) [2]; Consistency: a transaction brings the database from one valid state to another valid state; it must not violate any integrity constraints (foreign keys, NOT NULL, CHECK constraints) or business rules; enforced by constraint checking before committing [2]; Isolation: concurrent transactions execute as if each is the only transaction running; intermediate states of one transaction are not visible to other transactions; enforced via LOCKING mechanisms — shared locks (allow multiple readers) and exclusive locks (only one writer) [2]; Durability: once a transaction is COMMITTED, its changes are permanent and survive system failures (power cuts, crashes); enforced via transaction logs (journals) and write-ahead logging (WAL) — committed changes can be recovered by replaying the log after a crash [2]. 2 marks per property: 1 for definition, 1 for enforcement mechanism. Max 8.
Q3Distinguish between a COMMIT and a ROLLBACK. Under what circumstances is each used?[4]
✅ Mark scheme
COMMIT [2]: signals that a transaction has completed successfully; ALL changes made by the transaction are permanently saved to the database; after commit, changes are durable — they persist even if the system subsequently crashes; used when every operation in the transaction has succeeded and the data is ready to be made permanent; ROLLBACK [2]: signals that a transaction has failed or been aborted; ALL changes made during the transaction are UNDONE — the database is restored to the state it was in before the transaction began; used when any operation in the transaction fails (e.g. constraint violation, system error), or when the transaction is explicitly cancelled; ensures atomicity — partial changes are never left in the database. Award 2 per command: 1 for what it does, 1 for when it's used.
Q4Explain the "lost update" concurrency problem with an example. Explain how database locking prevents this problem.[4]
✅ Mark scheme
Lost update problem [2]: occurs when two concurrent transactions read the same data, both calculate an update based on the original value, then both write back their update — the second write overwrites the first; example: Transaction A reads stock level = 100; Transaction B also reads stock level = 100; A reduces by 3 (writes 97); B reduces by 5 (writes 95) — but the correct answer after both reductions should be 92; B's write overwrites A's write entirely — A's update is LOST [1 for problem description, 1 for correct example]; Locking solution [2]: when Transaction A wants to update stock level, it acquires an EXCLUSIVE LOCK; Transaction B cannot read or write that data until A commits and releases the lock; B must wait; after A commits (stock = 97), B acquires the lock, reads 97, and updates to 92; both updates are applied correctly [1 for explaining exclusive lock prevents the problem, 1 for explaining the correct sequence].
Q5Describe what a deadlock is in a database context. Give an example of how a deadlock can occur, and state how the DBMS resolves it.[4]
✅ Mark scheme
Deadlock definition [1]: a situation where two or more transactions are each waiting for a lock held by the other, creating a circular dependency in which neither transaction can proceed; Example [2]: Transaction A holds a lock on Account(Alice) and is waiting to acquire a lock on Account(Bob); Transaction B holds a lock on Account(Bob) and is waiting to acquire a lock on Account(Alice); A cannot proceed without Bob's lock; B cannot proceed without Alice's lock; neither can release its current lock while waiting — a deadlock [2 marks for a correct example with two transactions and circular lock dependency]; Resolution [1]: the DBMS uses deadlock detection (timeout monitoring or wait-for-graph analysis) to identify the deadlock; it then selects one transaction as the "victim" and forces it to ROLLBACK, releasing its locks; the other transaction can then proceed; the rolled-back transaction may be retried automatically [1].
Q6Explain how a transaction log (journal) and checkpoints are used to recover a database after a system crash.[4]
✅ Mark scheme
Transaction log [2]: a continuous record of every database operation — what data was changed, the old value, and the new value, along with transaction identifiers and timestamps; changes are written to the log BEFORE being applied to the database (write-ahead logging — WAL) so the log is always ahead of the actual database state; after a crash, the log is used to: REDO committed transactions that hadn't yet been written to the database (replay their changes), and UNDO (rollback) any transactions that were in progress at the time of crash (they were not committed so their partial changes must be removed) [2]; Checkpoints [2]: a checkpoint is a periodic synchronisation point where all current in-memory changes are flushed to stable storage and the current database state is recorded; after a crash, recovery only needs to process transactions SINCE the last checkpoint — not replay the entire log from the beginning; this dramatically reduces recovery time [1 for what a checkpoint is, 1 for how it speeds up recovery]. Award 4 marks total across both mechanisms.
Q7A bank processes a transfer: (1) Deduct £200 from Account A, (2) Add £200 to Account B. The system crashes after step 1. Explain how the ACID properties of Atomicity and Durability together ensure the database remains consistent after the crash and recovery.[5]
✅ Mark scheme
Atomicity: the transaction is treated as a single indivisible unit — either both steps complete or neither does [1]; because the crash occurred mid-transaction, the transaction is incomplete and must be rolled back [1]; the ROLLBACK undoes the deduction from Account A, restoring it to its original balance [1]; Durability: once a transaction is committed, its changes are permanently saved (e.g. to a write-ahead log) and survive crashes [1]; since the transaction was never committed, the WAL / log contains no commit record, so recovery correctly identifies it as incomplete and rolls it back [1].
Q8Explain what a deadlock is in the context of concurrent database transactions. Describe a scenario involving two transactions and two records where a deadlock arises. State two strategies a database system can use to resolve or prevent deadlocks.[5]
✅ Mark scheme
Deadlock: two or more transactions are each waiting for a lock held by the other, so neither can proceed [1]; Scenario: Transaction T1 locks Record X and requests Record Y; Transaction T2 locks Record Y and requests Record X — both wait indefinitely [1]; Strategy 1: deadlock detection — the system detects the circular wait using a wait-for graph and aborts one transaction to break the cycle [1]; Strategy 2: deadlock prevention — impose a fixed lock ordering (all transactions must request locks in the same order), so circular waits cannot form [1]; Award 1 additional mark for any other valid strategy e.g. timeout-based abort or two-phase locking [1 max].
Topic Quiz
Question 1 of 10
You scored
out of 10
Card 1 of 9
Click to reveal definition
🎉
All cards reviewed!
Term
Definition
🎯
Mini Test — 4.5.3 Transactions & ACID
10 questions · 10 marks · 10 minutes
⏱ 10:00
Section A — Multiple Choice [5 marks]
Q1Which ACID property ensures that if part of a transaction fails, all changes are undone?
Q2The Durability property is enforced by:
Q3A "dirty read" occurs when:
Q4An exclusive (write) lock means:
Q5A checkpoint in a database recovery system:
Section B — Short Answer [5 marks]
Q6Explain the Consistency property of ACID. What happens if a transaction would violate a database constraint?
Mark schemeConsistency: a transaction must bring the database from one VALID state to another valid state; a valid state is one that satisfies all defined integrity constraints — NOT NULL rules, foreign key constraints, CHECK constraints, and business rules [1]; if a transaction would violate a constraint (e.g. inserting a row with a foreign key that references a non-existent parent row), the DBMS rejects the operation; the transaction cannot be committed and must be rolled back [1]; example: a bank transfer that would result in a negative balance — if the business rule is "balance ≥ 0", the transfer transaction would be rejected and rolled back, leaving both accounts unchanged [1].
Q7Transaction A holds a lock on Row X and is waiting for a lock on Row Y. Transaction B holds a lock on Row Y and is waiting for a lock on Row X. What situation has occurred? How is it resolved?
Mark schemeSituation: this is a DEADLOCK [1]; both transactions are each waiting for a lock held by the other — a circular dependency — so neither can ever proceed [1]; Resolution: the DBMS detects the deadlock (via timeout or wait-for-graph analysis); it selects one transaction as the "victim" and forces it to ROLLBACK, releasing its locks [1]; the other transaction can then acquire the freed lock and proceed; the rolled-back transaction may be restarted automatically [1]. Award 2 marks for identification and explanation of deadlock + 2 for resolution. Max 4.
Q8Explain the difference between a shared lock and an exclusive lock. Under what circumstances is each used?
Mark schemeShared lock (read lock): allows MULTIPLE transactions to read the same data simultaneously; NO transaction can WRITE to the data while a shared lock is held by any transaction; used when a transaction wants to READ data without it being changed underneath them — prevents dirty reads and lost updates (partially) [1]; Exclusive lock (write lock): only ONE transaction can hold the lock; no other transaction can READ or WRITE the locked data while the exclusive lock is held; used when a transaction wants to MODIFY (update/delete/insert) data — prevents all concurrency problems for the locked data; acquired before any write operation and released at COMMIT or ROLLBACK [1]. Full marks require: shared = multiple readers, no writers; exclusive = only one transaction, no readers OR writers; and correct usage context. [2 total]
Q9A power cut occurs while several transactions are in progress. Explain how the database uses its transaction log to recover correctly.
Mark schemeThe transaction log (journal) records every database operation with old and new values, along with whether each transaction was committed or not [1]; on restart after a crash, the DBMS reads the transaction log: for transactions that had issued a COMMIT before the crash — their changes are REDONE (replayed) in case they hadn't been written to the actual database yet (because of write-ahead logging, the log may be ahead of the database) [1]; for transactions that were still in progress (not yet committed) at the time of the crash — their partial changes are UNDONE (rolled back) from the log, ensuring atomicity — no partial transactions remain in the database [1]. Starting recovery from the most recent checkpoint means only the portion of the log since the checkpoint is processed, reducing recovery time [1]. Award marks for: log purpose (1), REDO committed (1), UNDO uncommitted (1), checkpoint mention (optional bonus).
Q10State which ACID property is described by each statement: (a) "Concurrent users do not interfere with each other's operations." (b) "A committed change will not be lost even if the server crashes."
Mark scheme(a) ISOLATION [1] — the Isolation property ensures that each transaction executes as if it is the only transaction running; intermediate states are not visible to concurrent transactions; enforced by locking mechanisms; prevents dirty reads, lost updates, phantom reads; (b) DURABILITY [1] — the Durability property ensures that once a transaction has been COMMITTED, its changes are permanent and survive any subsequent system failure; enforced by transaction logs, write-ahead logging (WAL), and checkpoints. [1 mark each — must be the correct property name to earn the mark].