Transactions and ACID Properties

A transaction is a sequence of one or more database operations that the DBMS treats as a single, indivisible unit of work: either all of the operations take effect (the transaction commits) or none of them do (the transaction rolls back). This guarantee sounds simple, but it is the foundation on which trustworthy multi-user systems are built. The moment more than one user — or more than one program — can read and write the same data at the same time, you face two distinct dangers: a failure part-way through a multi-step operation could leave the data half-changed, and two concurrent operations could interfere and corrupt each other. Transactions, governed by the ACID properties and implemented with mechanisms such as record locking, are how a Database Management System defends against both. This lesson works through the ACID guarantees, a fully traced lost-update concurrency scenario, the locking that prevents it, and the client–server architecture in which all of this matters most. The running example is a bank, because money makes the consequences of getting it wrong vivid.

---

Spec Mapping

This lesson addresses the AQA A-Level Computer Science (7517) Fundamentals of databases content on transaction processing: the meaning of a transaction; the four ACID properties (Atomicity, Consistency, Isolation, Durability); concurrency problems including the lost update and the dirty (uncommitted) read; record locking as the control mechanism; deadlock; and the use of COMMIT and ROLLBACK. It links closely to the client–server database model, and to the INSERT/UPDATE/DELETE statements (the operations a transaction wraps) and the integrity rules (which Consistency preserves).

---

What a Transaction Guarantees

Consider transferring £100 from account A to account B. This is two operations:

1. Subtract £100 from A's balance.
2. Add £100 to B's balance.

Suppose the system crashes between the two steps. Then £100 has left A but never reached B — the money has simply vanished, and the bank's books no longer balance. Wrapping both steps in a transaction makes them atomic: either both happen or neither does.

BEGIN TRANSACTION;
    UPDATE Account SET Balance = Balance - 100 WHERE AccountID = 'A';
    UPDATE Account SET Balance = Balance + 100 WHERE AccountID = 'B';
COMMIT;

COMMIT makes the changes permanent. If anything goes wrong before the commit, the DBMS performs a rollback, undoing every change the transaction made so far:

BEGIN TRANSACTION;
    UPDATE Account SET Balance = Balance - 100 WHERE AccountID = 'A';
    -- error detected here (e.g. account B is frozen)
ROLLBACK;
-- A's balance is restored; both accounts are exactly as before

The whole point is that an outside observer never sees the in-between state where A has been debited but B not yet credited.

---

The ACID Properties

ACID names the four guarantees a well-behaved transaction provides.

A — Atomicity

A transaction is all-or-nothing. If any part fails, the entire transaction is rolled back, leaving no partial changes. The two-step bank transfer is the canonical illustration: atomicity is exactly what stops the £100 vanishing.

C — Consistency

A transaction moves the database from one valid state to another valid state. Every integrity rule — primary-key uniqueness, referential integrity, NOT NULL, CHECK constraints — that held before the transaction must still hold after it. If a transaction would violate a rule (for example, drive a balance below a CHECK (Balance >= 0) limit), the DBMS refuses it and rolls back, so an invalid state is never committed.

I — Isolation

Concurrent transactions must not interfere; each should behave as if it were the only transaction running. The intermediate, uncommitted states of one transaction are invisible to the others. Without isolation, the concurrency problems below arise. Isolation does not mean transactions run one at a time — they can run in parallel — only that the result is as if they had been run in some serial order.

D — Durability

Once a transaction has committed, its effects are permanent and survive a subsequent crash, power cut, or restart, because they have been written to non-volatile storage (and recorded in the transaction log). After the bank transfer commits, the money has moved for good even if the server loses power one millisecond later.

Property	Guarantee	Failure if absent
Atomicity	All operations, or none	Half-completed updates corrupt the data
Consistency	Valid state → valid state	Integrity constraints could be violated
Isolation	Concurrent transactions don't interfere	Lost updates / dirty reads occur
Durability	Committed changes persist	A crash could lose confirmed work

---

Transaction Control Statements

Statement	Purpose
BEGIN TRANSACTION (or START TRANSACTION)	Mark the start of a transaction
COMMIT	Make all changes since BEGIN permanent
ROLLBACK	Undo all changes since BEGIN
SAVEPOINT name	Mark a point to which a partial rollback is possible

A savepoint lets a long transaction undo part of its work without abandoning all of it:

BEGIN TRANSACTION;
    INSERT INTO "Order" (OrderID, CustomerID) VALUES (501, 1001);
    SAVEPOINT after_order;
    INSERT INTO OrderLine (OrderID, ProductID, Quantity) VALUES (501, 'P1', 3);
    -- this line turns out to be wrong
    ROLLBACK TO after_order;   -- undoes only the OrderLine insert
    INSERT INTO OrderLine (OrderID, ProductID, Quantity) VALUES (501, 'P2', 1);
COMMIT;

Effect: the order header survives, the mistaken order line is undone, the correct line is inserted, and the whole thing commits together.

Worked rollback trace

To see atomicity concretely, trace the bank transfer when the second step fails. Account A starts at £500, account B at £300, and we attempt to move £100 from A to B — but B's account turns out to be frozen, raising an error:

Step	Statement	A's balance	B's balance	Status
1	BEGIN TRANSACTION	500	300	started
2	UPDATE A SET Balance = Balance - 100	400	300	uncommitted
3	UPDATE B SET Balance = Balance + 100	400	—	error: B frozen
4	ROLLBACK	500	300	undone

The key observation is row 4: although A's balance had already been reduced to 400 in step 2, the ROLLBACK restores it to its original 500, because the change in step 2 was only ever uncommitted. No other transaction could have seen the temporary 400 (that is isolation), and after the rollback the database is exactly as it began (that is atomicity). Had there been no transaction, step 2's debit would have stood alone — A charged £100, B credited nothing — the precise corruption transactions exist to prevent.

---

Why ACID Matters Most in Multi-User Systems

A single user running one statement at a time barely needs transactions. ACID becomes essential the moment a database is shared — many users, many programs, all reading and writing concurrently, which is the normal state of any real system. It is worth seeing which guarantee answers which multi-user danger:

Multi-user danger	Without it you get	ACID guarantee that addresses it
A multi-step operation interrupted by a crash	Half-finished updates (money debited, not credited)	Atomicity
Two users updating the same row at once	A lost update	Isolation (via locking)
One user reading another's unsaved changes	A dirty read of data that may be rolled back	Isolation
A committed change lost when the server crashes	Confirmed work vanishes	Durability
An operation leaving the data breaking a rule	Negative balances, orphan rows	Consistency

This mapping is a high-value revision tool: an exam question that describes a symptom ("two cashiers' deposits, one disappears") is really asking which property and mechanism prevent it ("isolation, enforced by record locking"). Reading a scenario, naming the danger, then naming the guarantee and its mechanism is the structure of a full-mark transactions answer. It also explains why the database server — not each client — must own the locks, log and constraints: only a single shared authority can coordinate guarantees that span all the concurrent users at once.

---

Concurrency Problems

When several transactions run at once without proper isolation, characteristic errors appear. Two are named explicitly in the specification.

The lost update — traced step by step

Two cashiers process payments into the same account, whose balance starts at £1000. Cashier A adds £200; Cashier B adds £300. The correct final balance is £1500. But if both transactions read before either writes, one update is silently lost:

sequenceDiagram
    participant A as Transaction A (+200)
    participant DB as Account row (Balance)
    participant B as Transaction B (+300)
    Note over DB: Balance = 1000
    A->>DB: read Balance (1000)
    B->>DB: read Balance (1000)
    A->>A: compute 1000 + 200 = 1200
    B->>B: compute 1000 + 300 = 1300
    A->>DB: write Balance = 1200
    Note over DB: Balance = 1200
    B->>DB: write Balance = 1300
    Note over DB: Balance = 1300  (A's +200 lost!)

Both transactions read the same stale value, 1000, because neither had written yet. A writes 1200; then B, still working from its stale 1000, overwrites with 1300. The £200 deposit has disappeared even though it "succeeded" — the final balance should have been £1500. This is the lost update problem, and it is caused by interleaving read–modify–write cycles.

The dirty read (uncommitted dependency)

A transaction reads data that another transaction has changed but not yet committed. If that other transaction then rolls back, the first transaction has acted on a value that, in the end, never officially existed.

sequenceDiagram
    participant A as Transaction A
    participant DB as Account row
    participant B as Transaction B
    A->>DB: UPDATE Balance = 5000 (uncommitted)
    B->>DB: read Balance (5000) — dirty!
    B->>B: approve loan based on 5000
    A->>DB: ROLLBACK (Balance back to 500)
    Note over B: B's decision used a value that never committed

B approved a loan on the strength of a £5000 balance that was rolled back to £500 — a decision based on data that never truly existed.

Non-repeatable read

A transaction reads the same row twice and gets different values because another committed transaction changed it in between — so a calculation that assumed a stable value becomes inconsistent.

---

Controlling Concurrency — Record Locking

The principal mechanism in the specification is record locking: while a transaction is using a record, the DBMS locks it so that conflicting access by other transactions is blocked until the lock is released (typically at COMMIT or ROLLBACK).

Lock type	Effect	Allows
Shared (read) lock	Held while reading	Other readers may share; writers are blocked
Exclusive (write) lock	Held while writing	No other reader or writer may touch the record

Re-run the lost-update scenario with an exclusive lock. When Transaction A reads the balance intending to update it, it takes an exclusive lock; Transaction B must now wait until A commits and releases the lock, then reads the up-to-date 1200, adds 300, and correctly writes 1500:

sequenceDiagram
    participant A as Transaction A (+200)
    participant DB as Account row (locked)
    participant B as Transaction B (+300)
    Note over DB: Balance = 1000
    A->>DB: lock + read Balance (1000)
    B-->>DB: request lock — BLOCKED, must wait
    A->>DB: write 1200, COMMIT, release lock
    Note over DB: Balance = 1200
    B->>DB: lock + read Balance (1200)
    B->>DB: write 1500, COMMIT
    Note over DB: Balance = 1500 (correct)

Locking serialises conflicting access to the contested record, so no update is lost.

Two-phase locking — making locking correct

Simply taking locks is not enough to guarantee serialisability; when locks are released matters. The standard protocol is two-phase locking (2PL): a transaction has a growing phase in which it only acquires locks, followed by a shrinking phase in which it only releases them — once it has released any lock, it may never acquire another. In practice most systems hold all locks until COMMIT or ROLLBACK (called strict 2PL), which also prevents other transactions from reading uncommitted data and so rules out dirty reads. The reason 2PL works is that it forces conflicting transactions into a consistent order: if A locks a row before B, then B cannot "overtake" A on a later row, so the overall schedule is equivalent to running them one at a time. You are not expected to prove this at A-Level, but understanding that locking needs a protocol, not just isolated locks, deepens an answer about how isolation is actually achieved.

Other techniques

• Timestamp ordering: each transaction is stamped at start; the DBMS uses the stamps to order conflicting operations consistently, rolling back a transaction that arrives "too late". This is an optimistic-leaning alternative to locking that avoids deadlock (there are no locks to deadlock on) at the cost of more rollbacks under contention.
• Serialisation: running transactions strictly one after another removes all concurrency anomalies but sacrifices the throughput that parallelism gives — so it is the conceptual gold standard that locking tries to approximate cheaply.

Where the transaction boundary comes from