AQA 7517 · Paper 2 · 4.10.5

ACID &
Transactions

Atomicity · Consistency · Isolation · Durability · COMMIT · ROLLBACK · locking

WHAT YOU'LL LEARN

ACID properties · transactions · COMMIT/ROLLBACK · record locking · deadlock

AQA SPEC LINK

4.10.5 — Transactions: ACID, COMMIT, ROLLBACK, record locking, deadlock

Transactions

What is a Database Transaction?

A transaction is a sequence of database operations treated as a single logical unit of work. Either all operations succeed or none are applied.

Example: Bank transfer — debit Account A, credit Account B. If the debit succeeds but credit fails, money disappears. A transaction ensures both happen or neither does.

COMMIT — permanently saves all changes made in the transaction

ROLLBACK — undoes all changes made since the last COMMIT if an error occurs

ACID

ACID Properties

A — Atomicity: All operations in a transaction complete successfully, or none are applied (all-or-nothing)

C — Consistency: A transaction brings the database from one valid state to another; all integrity constraints are maintained

I — Isolation: Concurrent transactions are executed as if they were serial; intermediate states are not visible to other transactions

D — Durability: Once a transaction is committed, changes persist even if the system crashes (written to disk/transaction log)

COMMIT & ROLLBACK

COMMIT and ROLLBACK

COMMIT

Makes all changes in the current transaction permanent. The database is now in the new state. Cannot be undone once committed.

ROLLBACK

Reverts the database to the state at the last COMMIT, undoing all changes made in the current transaction. Used when an error is detected mid-transaction.

Concurrency

Concurrency Problems

When multiple users access the database simultaneously, problems can occur without proper isolation.

Lost update — User A and User B both read balance (£100); A adds £50, B adds £30; last write wins, one update is lost

Dirty read — User B reads data changed by User A's uncommitted transaction; if A rolls back, B has read invalid data

Phantom read — User A reads a set of rows; User B inserts a new row; A re-reads and sees different results

Record Locking

Record locking prevents concurrent transactions from accessing the same record simultaneously, ensuring isolation.

Shared lock (read lock) — other transactions can read but not write the locked record

Exclusive lock (write lock) — no other transaction can read or write the locked record until the lock is released

Locks are released when the transaction COMMITs or ROLLBACKs

Deadlock

Deadlock occurs when two or more transactions are each waiting for the other to release a lock — neither can proceed.

T1 locks Record A, waits for Record B
T2 locks Record B, waits for Record A
→ Neither can proceed — deadlock!

DBMS detects deadlocks and rolls back one transaction to break the deadlock

Prevention: acquire locks in a consistent order; use timeouts

Transaction Log

Transaction Log & Recovery

The DBMS maintains a transaction log — a record of all changes made. Used to recover the database after a crash.

Redo — re-apply committed transactions that weren't yet written to disk (ensures Durability)

Undo — roll back uncommitted transactions that were partially written (ensures Atomicity)

Checkpoints — periodic snapshots to speed up recovery (only replay log from last checkpoint)

AQA Exam Style

Practice Question

AQA 7517 — Paper 2 Style

(a) Explain what is meant by ACID in the context of database transactions. [4]
(b) Two users simultaneously try to update the same record. Explain how record locking prevents data corruption. [3]
(c) Explain what a deadlock is and how a DBMS resolves it. [3]

[10 marks]

4 marks

(a) Atomicity — all operations complete or none do [1]; Consistency — database moves from one valid state to another [1]; Isolation — concurrent transactions don't interfere with each other [1]; Durability — committed changes survive system failures [1]

3 marks

(b) When User A begins to update a record, the DBMS places an exclusive lock on it [1]; User B's attempt to write is blocked until User A COMMITs or ROLLBACKs and the lock is released [1]; this ensures only one update happens at a time, preventing the lost update problem [1]

3 marks

(c) Deadlock occurs when Transaction A holds a lock needed by Transaction B, and B holds a lock needed by A — neither can proceed [1]; the DBMS detects the circular wait [1]; it rolls back one transaction (the victim) to break the deadlock, allowing the other to continue [1]

Summary

Key Points to Remember

Transaction — unit of work; all-or-nothing; COMMIT saves, ROLLBACK undoes

ACID — Atomicity, Consistency, Isolation, Durability

Record locking — prevents concurrent conflicts; shared (read) vs exclusive (write) locks

Deadlock — circular wait; DBMS rolls back one transaction to resolve

Transaction log — enables redo (durability) and undo (atomicity) after crashes

🎉 Lesson complete — move to the quiz!