SLIDE 1
CSZone.co.uk
Click to advance · Arrow keys also work
CAIE 9618 · Paper 4 · Topic 4.5.3

Transactions
& ACID

Atomicity · Consistency · Isolation · Durability · COMMIT · ROLLBACK · Concurrency · Locking

CSZone Cambridge International AS & A Level Computer Science 9618
What is a Transaction?

All-or-Nothing Database Operations

A transaction is a sequence of database operations treated as a single indivisible unit. Either ALL operations succeed (COMMIT) or NONE take effect (ROLLBACK) — partial application is impossible.
CLASSIC EXAMPLE — BANK TRANSFER
Transfer £500 from Account A to Account B:

Step 1: Debit Account A by £500
Step 2: Credit Account B by £500

If step 1 succeeds but step 2 fails (power cut), £500 disappears! A transaction ensures both steps complete or neither does.
COMMIT vs ROLLBACK
COMMIT — permanently saves all changes in the transaction; cannot be undone
ROLLBACK — undoes ALL changes made during the transaction; database restored to pre-transaction state
Transactions begin with BEGIN TRANSACTION and end with COMMIT or ROLLBACK
ACID Properties

Four Guarantees of Every Transaction

Atomicity
ALL operations in the transaction succeed, or NONE of them do. The transaction is indivisible — if any step fails, ROLLBACK undoes all previous steps.
Consistency
A transaction brings the database from one VALID state to another valid state. All database rules, constraints, and referential integrity hold before AND after the transaction. No constraint may be broken.
Isolation
Concurrent transactions execute as if they were run serially (one at a time). Intermediate uncommitted states of one transaction are invisible to others. Enforced using locking mechanisms.
Durability
Once a transaction COMMITs, its changes are permanent and survive system failures (crashes, power cuts). Written to non-volatile storage via transaction logs and write-ahead logging (WAL).
Concurrency Problems

What Goes Wrong Without ACID

When multiple transactions access the same data simultaneously without proper isolation, four types of concurrency problem can occur:
ProblemWhat HappensExample
Lost Update Two transactions both read a value, both update it. The second write overwrites the first — the first update is lost. T1 reads stock=10, T2 reads stock=10. T1 writes 9, T2 writes 9. Actual stock should be 8 but is 9.
Dirty Read Transaction T2 reads data that T1 has modified but not yet committed. If T1 then rolls back, T2 has read data that was never valid. T1 reduces balance, T2 reads the new balance, T1 rolls back. T2 acted on data that was never committed.
Uncommitted Dependency Same as dirty read — T2 depends on T1's uncommitted changes. If T1 rolls back, T2's decision was based on invalid data. T2 grants a loan based on T1's uncommitted deposit. T1 rolls back — the deposit never happened.
Inconsistent Analysis T2 reads several related records while T1 is in the middle of updating them. T2 sees a mix of old and new values — an inconsistent snapshot. T2 sums all account balances while T1 is transferring money. T2 sees the debit but not yet the credit.
Locking & Deadlock

Preventing Concurrent Access Problems

RECORD LOCKING
Shared (Read) lock — multiple transactions can hold simultaneously; only allows reading; no writes while held
Exclusive (Write) lock — only ONE transaction can hold at a time; no other reads or writes allowed
Lock acquired before accessing data; released on COMMIT or ROLLBACK
Prevents lost updates, dirty reads, and inconsistent analysis
SERIALISATION
Transactions execute concurrently but produce the SAME result as if they ran one at a time (serially). Locking enforces this. The goal of Isolation is serialisability.
DEADLOCK
Deadlock occurs when two (or more) transactions each hold a lock the other needs, and both are waiting indefinitely — neither can proceed.
T1 holds lock on Record A
T1 waits for lock on Record B
T2 holds lock on Record B
T2 waits for lock on Record A
→ Both wait forever — DEADLOCK
DBMS detects deadlock (wait-for graph cycle) and resolves it by rolling back the transaction with the least work done (the victim).
Exam Practice

Cambridge-style questions

Question 1
Two users simultaneously access the same airline booking database. User A reads that there is 1 seat remaining. Before User A completes the booking, User B also reads that 1 seat is available and begins booking. Explain what concurrency problem this illustrates, and describe how record locking prevents it. [4]
1
This is a lost update problem — both transactions read the same value (1 seat), both then try to write an updated value (0 seats remaining), and the second write may overwrite the first without awareness that User A had already booked.
1
Alternatively it could be described as an inconsistent analysis problem — User B read data that was in the process of being modified by User A's uncommitted transaction, giving User B an inconsistent view.
1
Record locking: when User A reads the seat record and begins booking, the DBMS places an exclusive (write) lock on that record, preventing any other transaction from reading or writing it.
1
User B's transaction must wait until User A's transaction either COMMITs (seat booked) or ROLLBACKs. Only then does User B acquire the lock, reads the updated count (0 seats), and can correctly reject the booking request.
Exam Practice

Cambridge-style questions

Question 2
Describe what is meant by a "dirty read" and explain which ACID property is designed to prevent it. [3]
1
A dirty read occurs when one transaction (T2) reads data that has been modified by another transaction (T1) but which T1 has NOT yet committed.
1
If T1 subsequently rolls back, T2 has based its actions on data values that were never permanently stored in the database — the read data was "dirty" (uncommitted and potentially invalid).
1
The Isolation property of ACID prevents dirty reads — it ensures that uncommitted changes made by one transaction are not visible to any other concurrently executing transaction.
Common Mistakes

Don't lose easy marks

1
Confusing Atomicity and Consistency — Atomicity = all-or-nothing (either all operations complete or none do). Consistency = the database remains in a valid state following all rules and constraints. These are different guarantees — "all happen or none" is ALWAYS Atomicity.
2
Saying Durability means "the data is backed up" — Durability means committed data survives system failures (crashes, power cuts). The mechanism is transaction logs and write-ahead logging. Regular backups are a separate concept from Durability.
3
Describing a lost update as a "dirty read" — a dirty read is reading uncommitted data. A lost update is when two transactions both write to the same field and one write overwrites the other. Know the distinction — CAIE mark schemes test these specifically by name.
Topic Summary — 4.5.3

What You Need to Know

TRANSACTIONS & ACID
Transaction = all-or-nothing unit. COMMIT = permanent. ROLLBACK = undo.

Atomicity · Consistency · Isolation · Durability
CONCURRENCY PROBLEMS
Lost update: two writes overwrite each other.
Dirty read: reading uncommitted data.
Uncommitted dependency: same as dirty read.
Inconsistent analysis: mix of old and new values.
LOCKING & DEADLOCK
Shared lock: many readers allowed, no write.
Exclusive lock: one writer, no other access.
Deadlock: circular lock wait — DBMS detects and rolls back victim.
Serialisation: concurrent result = serial result.
CSZone

Almost There!

Section 4.5 — Databases complete · 2 more topics to go
4.6.1
Functional Programming
Pure Functions · Higher-Order Functions · Map/Filter/Reduce
Head to CSZone.co.uk for the complete worksheet, quiz, and interactive tools