📄 Paper 2 · 4.10 Databases
4.10.5 Transactions, ACID Properties & Concurrency
AQA 7517 · A-Level Computer Science · ~15 min read

What is a Database Transaction?

A transaction is a sequence of operations performed as a single logical unit of work. Either all operations in the transaction complete successfully, or none of them are applied — the database is never left in a partial or inconsistent state.

Example: A bank transfer — debit £100 from Account A and credit £100 to Account B. Both must succeed or neither should happen.

COMMIT and ROLLBACK

  • COMMIT: permanently saves all changes made by a transaction to the database.
  • ROLLBACK: undoes all changes made by the current transaction — returns the database to its state before the transaction began.

ACID Properties

ACID defines four properties that guarantee database transactions are processed reliably.

A — Atomicity

A transaction is treated as a single unit — either ALL operations complete (COMMIT) or NONE are applied (ROLLBACK). No partial updates.

C — Consistency

A transaction brings the database from one valid state to another valid state. All integrity constraints (PK, FK, NOT NULL, CHECK) must remain satisfied before and after.

I — Isolation

Concurrent transactions execute as if they were serial (one at a time). Intermediate states of a transaction are invisible to other transactions.

D — Durability

Once a transaction is committed, changes are permanent — even if the system crashes immediately after. Achieved via transaction logs and write-ahead logging.

Concurrency Problems

When multiple transactions run simultaneously, three classic problems can arise without proper control:

ProblemDescriptionExample
Lost UpdateTwo transactions read the same value and both update it — one update overwrites the otherT1 and T2 both read stock=10. T1 sets stock=8, T2 sets stock=9. T1's update is lost.
Dirty ReadA transaction reads data written by an uncommitted transaction that later rolls backT1 changes balance to £500. T2 reads £500. T1 rolls back. T2 acted on invalid data.
Uncommitted DependencySame as dirty read — reading data that was never permanently writtenAlso called "dirty read" in some texts — reading uncommitted changes.

Concurrency Control Methods

Locking

  • Pessimistic locking: assume conflicts will happen — lock the resource before reading or writing. Other transactions must wait. Prevents all three problems but can cause deadlock.
  • Optimistic locking: assume conflicts are rare — no lock during read/write; check for conflicts at COMMIT time. If conflict found, rollback and retry. Better performance when conflicts are rare.

Timestamp Ordering

Each transaction is given a unique timestamp when it starts. Operations are ordered based on timestamps — older transactions have priority. No locks needed; conflicts resolved by timestamp comparison.

Deadlock

Two transactions each hold a lock that the other needs — both wait indefinitely. Solutions: timeout (abort one after a wait threshold), deadlock detection graphs (abort the younger transaction).

Exam tip: AQA 7517 requires you to state and explain all four ACID properties — learn the acronym. For concurrency, be able to describe lost update and dirty read with examples. Know the difference between pessimistic (lock first) and optimistic (check at commit) locking. Commit = permanently saves; rollback = undoes all changes.
▶
Click through the slides at your own pace. Use arrow keys or click to advance.
Click slide or press arrow keys to navigate

Worksheet — 4.10.5 Transactions & ACID

8 questions · instantly marked · AQA 7517 standard

Q1What is a database transaction? Give a real-world example to support your answer.[2]
✅ Mark scheme
Mark scheme
A transaction is a sequence of database operations treated as a single logical unit of work [1]; either all operations complete (COMMIT) or none are applied (ROLLBACK) — the database is never left in an inconsistent state [1]. Example: bank transfer — debit from account A and credit to account B must both succeed or both be undone [1]. Accept any suitable real-world example (online purchase, exam enrolment, hotel booking).
Q2Explain what is meant by Atomicity in the context of ACID. Why is it important?[2]
✅ Mark scheme
Mark scheme
Atomicity: a transaction is treated as a single indivisible unit — either ALL operations execute successfully and are committed, or NONE are applied and the transaction is rolled back [1]. No partial updates are ever written to the database [1]. Important: prevents the database being left in an inconsistent state — e.g. money debited from one account but not credited to the other if the system crashes mid-transaction [1].
Q3Explain the difference between COMMIT and ROLLBACK with an example for each.[3]
✅ Mark scheme
Mark scheme
COMMIT: permanently saves all changes made by the current transaction to the database [1]; example: after a successful bank transfer, COMMIT makes the debit and credit permanent [1]. ROLLBACK: undoes all changes made by the current transaction since it began [1]; example: if the credit step fails, ROLLBACK restores both accounts to their original balances as if the transaction never happened [1].
Q4Describe the Consistency and Durability properties of ACID transactions.[3]
✅ Mark scheme
Mark scheme
Consistency: a transaction brings the database from one valid/consistent state to another [1]; all integrity constraints (PKs, FKs, NOT NULL, CHECK) must remain satisfied after the transaction — a transaction that would violate them is rolled back [1]. Durability: once a transaction is committed, changes are permanent [1]; even if the system crashes immediately after commit, the data is not lost — achieved through transaction logs and write-ahead logging [1].
Q5Describe the 'lost update' concurrency problem. Use an example with two transactions.[3]
✅ Mark scheme
Mark scheme
Lost update: two transactions both read the same data at the same time [1]; both make changes based on the original value they read and both write back [1]; one transaction's update overwrites the other's — the first update is 'lost' [1]. Example: stock=10. T1 reads stock=10, T2 reads stock=10. T1 sets stock=9 (sold 1). T2 sets stock=8 (sold 2). T1's update is overwritten — actual stock should be 8 but T1's -1 is lost [1].
Q6What is a dirty read? Explain why it causes problems and how isolation prevents it.[2]
✅ Mark scheme
Mark scheme
Dirty read: one transaction reads data that has been modified by another transaction that has not yet committed [1]; if the modifying transaction is then rolled back, the reading transaction has used invalid/non-existent data [1]. Problem: decisions or further operations based on uncommitted data are incorrect [1]. Isolation prevents this by ensuring intermediate states of a transaction are hidden from other concurrent transactions — other transactions only see committed data [1].
Q7Compare pessimistic and optimistic locking as methods of concurrency control.[3]
✅ Mark scheme
Mark scheme
Pessimistic: assumes conflicts will occur — locks the resource before reading/writing; other transactions must wait [1]; prevents all concurrency problems but reduces concurrency (throughput) and can cause deadlock [1]. Optimistic: assumes conflicts are rare — no locks during transaction; checks for conflicts only at commit time [1]; if conflict detected, rollback and retry; better performance in low-conflict environments [1].
Q8Explain what is meant by a deadlock in a database system and describe two ways it can be resolved.[2]
✅ Mark scheme
Mark scheme
Deadlock: two or more transactions are each waiting for a lock held by the other — neither can proceed, creating a permanent wait cycle [1]. Resolution method 1: timeout — abort a transaction that has been waiting longer than a set threshold and retry it [1]. Resolution method 2: deadlock detection — use a wait-for graph to detect cycles; when a cycle is found, abort one transaction (typically the youngest/least costly) to break the deadlock [1]. Award 1 mark for any other valid method (e.g. lock ordering — acquire locks in a predefined order to prevent cycles) [1].
Transactions & ACID Quiz
Question 1 of 15
You scored
out of 15
Card 1 of 8
Click to reveal definition
🎉
All cards reviewed!
TermDefinition
🎯

Mini Test — Transactions & ACID

10 questions · 10 minutes

← 4.10.4 Normalisation
67 of 70 · AQA 7517
4.11.1 Big Data →