Transactions and Data Integrity

AQA 4.10.5 — Transactions and data integrity

Retrieval: every query so far has been a single, self-contained SELECT. Real systems often need several related changes to happen together, reliably — this lesson covers exactly that, using products.stock (already in this course's schema) as a genuine, concrete example.

Key vocabulary

• Transaction — a sequence of one or more database operations treated as a single, indivisible unit: either all of them succeed, or none of them take effect.
• ACID — the four properties a properly-handled transaction guarantees: Atomicity (all-or-nothing), Consistency (the database moves from one valid state to another, never leaving broken/contradictory data), Isolation (concurrent transactions don't interfere with each other's intermediate steps), Durability (once committed, a transaction's changes survive even a crash immediately afterwards).
• Concurrency — multiple operations happening on the database at, or near, the same time.
• Index — a separate structure the DBMS maintains to speed up looking up rows by a specific column, at the cost of extra storage and slightly slower writes.
• Referential integrity — the guarantee that every foreign key value genuinely matches an existing row in the table it references.

Understand — why "placing an order" needs to be a transaction

Placing an order genuinely requires two related changes: inserting a new orders row, and reducing the matching product's stock. If the first step succeeds but the second one fails partway (a crash, a network drop), the database is left in a genuinely broken state — an order exists for stock that was never actually reserved. Wrapping both steps in a single transaction guarantees Atomicity: either the order is created and stock is reduced together, or neither happens at all.

See it — a transaction, conceptually

BEGIN TRANSACTION;
  INSERT INTO orders (customer_id, product_id, quantity, order_date)
    VALUES (2, 5, 1, '2026-02-01');
  UPDATE products SET stock = stock - 1 WHERE id = 5;
COMMIT;

If any statement between BEGIN TRANSACTION and COMMIT fails, the DBMS performs a ROLLBACK — undoing every change made so far in that transaction, restoring the database exactly as it was before the transaction began.

Exam-style worked example 1

Question: products.stock for the Laptop Stand is currently 6. A transaction attempts to insert an order for quantity 10 of the Laptop Stand, then reduce its stock by 10, but a CHECK constraint prevents stock from ever going negative, causing the UPDATE to fail. Explain what should happen to the database as a result. (3 marks)

Model answer: Because the two statements are part of one transaction, and the second statement fails, Atomicity requires the entire transaction to be rolled back — the INSERT that already ran must also be undone, so no order for 10 units is left in the orders table even though it was technically inserted successfully before the failure. Without this rollback, the database would contain an order that was never actually fulfillable.

Understand — the lost-update concurrency problem

Two staff members both view the Laptop Stand's stock (6) at the same moment, before either has made a change. Both independently process a sale of 1 unit, and both compute "6 − 1 = 5" using the value they each originally read, then both write stock = 5. The genuinely correct final stock, after two sales, should be 4 — but because neither transaction was aware of the other's change, one sale's reduction is silently lost, and stock ends up overstated. Isolation (correctly implemented) prevents this by ensuring one transaction's changes are properly accounted for before another begins working from the same data.

Understand — indexing

Without an index, finding all orders for a specific customer_id requires scanning every single row in orders — for a small sample table this is instant, but for a table with millions of rows, this is exactly the O(n) linear-search cost Year 12 Sequence 5 covered. An index on orders.customer_id lets the DBMS locate matching rows far faster (conceptually similar to a BST or hash table's faster-than-linear lookup, Sequence 12) — at the cost of extra storage, and a small slowdown on every INSERT/UPDATE, since the index itself must also be kept up to date.

Understand — referential integrity in practice

A well-designed DBMS refuses to allow orders.customer_id to be set to a value that doesn't exist in customers.id at all, and (depending on configuration) either refuses to delete a customer who still has orders, or requires an explicit decision about what happens to those orders (delete them too, or set the foreign key to a placeholder). This is exactly what stops a database from silently accumulating "orphaned" rows referencing customers that no longer exist.

Common mistake

Assuming a transaction's Atomicity guarantee happens automatically just because several SQL statements were written near each other. Without an explicit BEGIN TRANSACTION / COMMIT boundary, each statement is its own separate, independent unit — a crash between two ordinarily-related statements can leave the database in exactly the inconsistent state a transaction is designed to prevent.

Why this matters for the NEA

Any NEA project that performs more than one related database write for a single logical action (recording a purchase, transferring a balance between two records) should genuinely reason about what happens if the second write fails after the first succeeds — this is precisely the kind of robustness consideration that separates a superficially working project from a genuinely well-engineered one.

Check your understanding

A banking app transfers £50 from Account A to Account B using two separate UPDATE statements (subtract 50 from A, add 50 to B) that are not wrapped in a transaction. Explain a genuine way this could leave the database in an inconsistent state. (3 marks)

(If the app crashes, loses its connection, or is interrupted between the two UPDATE statements, the first UPDATE (subtracting £50 from A) could complete while the second (adding £50 to B) never runs - leaving £50 that has vanished from A without ever appearing in B, an inconsistent, incorrect total across the two accounts that a properly wrapped transaction (both statements succeeding or neither) would have prevented.)

Challenge

Identify one genuine index you would add to this course's sample schema (beyond the primary keys, which are already indexed automatically) to speed up a specific, realistic query, and justify your choice with reference to which column is searched most often.

Looking ahead: the next lesson moves from a single organisation's database to genuinely massive scale — Big Data — and the point at which a relational database like the one this course has used starts to struggle.