A3.3.6 · Database programming
Transactions
By the end you can
Describe how transactions keep data correct: atomicity, consistency, isolation and durability.
Imagine two clerks changing the same train booking at the same moment, or a server failing half way through moving money between accounts. Who is right? Is any money lost? Databases solve this with : a group of operations that is treated as a single unit of work. It either happens completely or it does not happen at all.
The commands#
BEGIN TRANSACTION(orSTART TRANSACTION) opens one.COMMITmakes every change permanent and visible to others.ROLLBACKundoes every change since the transaction began.SAVEPOINT namemarks a place inside it, andROLLBACK TO nameundoes only what happened after that point.
ACID#
A trustworthy transaction has four properties, known by the letters :
- Atomicity. All or nothing. If the debit succeeds but the credit fails, the whole transaction is rolled back, so no money disappears.
- Consistency. The database moves from one valid state to another, obeying every rule (keys, constraints). A transaction that would break a rule is rejected.
- Isolation. Transactions running at the same time do not interfere. Nobody sees another's unfinished work, so the results are as if they ran one after the other.
- Durability. Once committed, a change is permanent, even if the power fails a moment later.
The price is performance. Keeping isolation means locking data, and durability means writing logs, which add overhead and complexity, especially in big, busy systems. For banking, booking and stock control that price is well worth paying; some other systems relax the rules (A3.4).
What this session sees
| IDPK | Owner | Balance |
|---|---|---|
| 1 | Priya | 500 |
| 2 | Tom | 200 |
Total money: 700 (correct: money is neither created nor lost)
What another user sees (isolation)
| IDPK | Owner | Balance |
|---|---|---|
| 1 | Priya | 500 |
| 2 | Tom | 200 |
Until COMMIT, other users only see the last committed figures, never the half-finished ones.
Log (newest first)
Try: BEGIN, take 100 from Priya, then Crash the server. Then try the whole transfer and COMMIT.
A transaction with five updates fails on the fourth, so none of the five is kept.
A transfer that would take an account below its allowed limit is rejected.
Two clerks edit the same booking at the same time and neither sees the other’s half-done change.
A power cut happens just after a payment is confirmed, and the payment is still there on restart.
0 of 4 matched
Check yourself
Transactions and ACID
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
4 questions · 16 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.Define the term “database transaction”.
[2]Define: Give the precise meaning of the term.
2.Describe the purpose of the SQL commands BEGIN TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT.
[4]Describe: A detailed account: say what happens, step by step if it is a process.
3.A bank transfer subtracts 100 from one account and adds 100 to another. Explain why the two updates must be in one transaction.
[4]Explain: Give reasons: say what happens AND why, or how one thing causes another.
4.Explain how the ACID properties allow a relational database to keep its data correct when many users change it at the same time.
[6]Explain: Give reasons: say what happens AND why, or how one thing causes another.
Past papers
Old exam questions on this topic
From HL Paper 1, May 2014 to November 2025. These papers were set on the previous syllabus, so the wording may differ from today's exam, but the topic is the same. Find the questions and markschemes in your school's past-paper pack.
| Paper | Question | Marks | About |
|---|---|---|---|
| May 2024 | 2(b) | 3 | Paper 2 Option A: transactions and ACID |
| November 2020 | 2(c) | 3 | Paper 2 Option A: transactions and ACID |
| November 2019 | 1(d) | 3 | Paper 2 Option A: transactions and ACID |
| May 2018 | 1(e) | 3 | Paper 2 Option A: transactions and ACID |
| November 2018 | 1(b) | 8 | Paper 2 Option A: transactions and ACID |
| May 2016 | 2(b)(ii) | 4 | Paper 2 Option A: transactions and ACID |
| May 2016 | 2(c) | 3 | Paper 2 Option A: transactions and ACID |
| November 2014 | 1(b) | 4 | Paper 2 Option A: transactions and ACID |
In a nutshell
- A transaction is a group of operations treated as one unit of work. TCL commands control it: BEGIN, COMMIT, ROLLBACK, SAVEPOINT.
- ACID: Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions do not interfere), Durability (committed changes survive failure).
- Rollback restores the database to its state before the transaction. A savepoint lets you roll back part of a transaction.
- ACID is what makes databases trustworthy for banking, booking and stock, but it costs performance and adds complexity in large systems.