Transaction Processing

A database is rarely touched by one user at a time, and computers fail at the worst moments. Transaction processing is the set of guarantees that keep the data correct anyway — so a half-finished operation never leaves money missing, and two cashiers selling the last ticket never both succeed. This lesson defines the four ACID properties that every transaction must uphold, shows how COMMIT and ROLLBACK enforce all-or-nothing behaviour, traces the concurrency problems (lost updates, deadlock) that arise without record locking, and pins down the difference between data redundancy and data consistency. The through-line is integrity: every mechanism here exists to ensure the database only ever moves from one valid state to another.

---

Spec Mapping

This lesson addresses the H446 1.3.2 Databases content on transactions and integrity:

• Define a transaction as a single logical unit of work and state the four ACID properties (atomicity, consistency, isolation, durability) with examples.
• Use COMMIT to make a transaction's changes permanent and ROLLBACK to undo them, achieving atomicity.
• Explain record locking as the mechanism that enforces isolation, the lost-update problem it prevents, and how deadlock can arise and be resolved.
• Distinguish data redundancy (deliberate duplication for resilience) from data consistency (the data never contradicting itself), and explain why transactions are essential to integrity.

(Phrasing here paraphrases the specification content; it is not a verbatim quote.)

---

What Is a Transaction?

A transaction is a sequence of one or more database operations treated as a single, indivisible logical unit of work: either every operation in it takes effect, or none does. There is no in-between state visible to anyone else. The word logical matters: a transaction groups operations that belong together as one real-world action, even though the database performs them as several separate updates. Booking a flight, for instance, might reserve a seat, charge a card and add a meal preference — three table changes that make no sense individually but form one coherent "booking" that must succeed or fail as a whole.

The classic example: a bank transfer

Moving £100 from account A to account B is two operations:

1. subtract £100 from A's balance;
2. add £100 to B's balance.

These must happen together. If the machine crashed after step 1 but before step 2, £100 would simply vanish — debited from A but never credited to B. Wrapping both in a transaction makes that impossible:

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

The COMMIT is the moment of truth: only when it is reached does the database make both updates permanent and visible to others. If anything goes wrong before COMMIT — an error, a constraint violation, a crash — the transaction is rolled back, undoing any partial change so the accounts are left exactly as they were:

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';
    -- error detected here (e.g. account B is frozen)
ROLLBACK;   -- the debit from A is undone; both balances unchanged

COMMIT and ROLLBACK are therefore the two ways a transaction can end: commit to keep everything, roll back to keep nothing.

---

The ACID Properties

A transaction must provide four guarantees, remembered by the acronym ACID. You must be able to define each and illustrate it.

Property	Guarantee	Bank-transfer example
Atomicity	All-or-nothing: every operation in the transaction succeeds, or the whole transaction is rolled back so none does.	The debit and credit either both happen or neither does — never just the debit.
Consistency	A transaction takes the database from one valid state to another, satisfying all rules and constraints.	The total money across A and B is the same before and after; no constraint (e.g. balance ≥ 0) is broken.
Isolation	Concurrent transactions do not interfere; each behaves as if it ran alone, and partial results are invisible to others.	A second transfer running at the same time never sees A debited but B not yet credited.
Durability	Once committed, changes are permanent and survive failures such as power loss or a crash.	After COMMIT, the new balances are safely on disk and cannot be lost even if the server immediately crashes.

A helpful way to see how the four relate: atomicity is enforced by the COMMIT/ROLLBACK log; consistency is the goal the other three serve; isolation is enforced by locking (the next section); and durability is enforced by writing committed changes to non-volatile storage (and a transaction log) before reporting success. They are not four unrelated facts but four facets of "the database stays correct under failure and concurrency". A useful exam habit is to pair each property with the threat it defends against: atomicity defends against a crash mid-transaction, isolation against interference from other concurrent users, durability against losing committed work in a later failure, and consistency is the overall correctness that the other three exist to preserve.

Exam Tip: You must be able to name and define all four ACID properties and give an example of each — this is examined almost every series. When a question gives a scenario (banking, ticket booking, stock control), apply each letter to that scenario rather than reciting the generic definition; the marks are for the application.

---

COMMIT, ROLLBACK and the Transaction Log

How does a database undo a half-done transaction after a crash? The answer is a transaction log (or journal): before any change is applied to the actual data, the intended change is first recorded in an append-only log on disk. This gives the system the information it needs to do two things:

• Roll back an incomplete transaction by reversing its logged changes (undo), restoring the prior values.
• Roll forward a committed-but-not-yet-written transaction after a crash by re-applying its logged changes (redo), which is how durability survives a power cut between COMMIT and the data physically reaching disk.

This is why COMMIT is meaningful: it writes a "committed" marker to the log, the point after which the change is guaranteed to survive. Anything without that marker is rolled back on recovery. The log is the concrete mechanism behind both atomicity and durability.

A practical note on when a transaction begins and ends. Many database tools run in autocommit mode by default, where each individual statement is its own one-statement transaction that commits immediately — convenient, but useless for grouping the bank transfer's two updates. To make several statements atomic you must explicitly open a transaction (BEGIN TRANSACTION) so they commit together:

BEGIN TRANSACTION;
    INSERT INTO Orders (OrderID, CustomerID) VALUES (501, 'C7');
    UPDATE Stock SET Qty = Qty - 1 WHERE ProductID = 'P3';
COMMIT;   -- the order and the stock decrement become permanent together

If the stock update failed (say it would drive Qty below zero and a constraint forbids it), the whole transaction — including the already-issued INSERT — is rolled back, so you never end up with an order for an item you could not reserve. This is atomicity doing exactly the job it exists for: keeping a multi-step business operation all-or-nothing.

---

Concurrency and Record Locking

Isolation is the hardest ACID property because, in reality, many transactions run at the same time. The tool that delivers it is record locking: while one transaction is using a record, the database locks it so others cannot interfere.

The lost-update problem

Without locking, two transactions reading-then-writing the same record can silently lose one update. Suppose a shop has 10 units of an item in stock and two sales happen at once:

Step	Transaction A (sells 1)	Transaction B (sells 1)	Stock value
1	reads stock = 10		10
2		reads stock = 10	10
3	computes 10 − 1 = 9, writes 9		9
4		computes 10 − 1 = 9, writes 9	9

Two items were sold, but the stock shows 9 instead of the correct 8 — B overwrote A's update because B had read the old value before A wrote. This is the lost update, and it is the canonical reason locking exists.

How locks fix it

A lock forces the two transactions to take turns. There are two kinds:

Lock type	Effect
Exclusive (write) lock	One transaction may read and write the record; everyone else must wait until it is released.
Shared (read) lock	Several transactions may read the record at once, but none may write until all shared locks are released.

With an exclusive lock the same two sales now play out safely:

Step	Transaction A (sells 1)	Transaction B (sells 1)	Stock value
1	locks record, reads stock = 10	blocked (record locked)	10
2	computes 10 − 1 = 9, writes 9	blocked	9
3	commits, releases lock	acquires lock, reads stock = 9	9
4		computes 9 − 1 = 8, writes 8, commits	8

Transaction A locks the stock record at step 1; B is made to wait until A commits and releases the lock; only then, at step 3, does B read — now seeing the updated value 9 — subtract 1, and correctly write 8. The lock has serialised the two transactions so the result is exactly as if they had run one after another, which is precisely what isolation promises. Notice the cost: B was blocked while A held the lock, so locking trades a little concurrency (waiting) for correctness — and it is this very waiting that, in the wrong pattern, can escalate into deadlock.

Deadlock

Locking solves lost updates but introduces a new hazard: deadlock, where two transactions each hold a lock the other needs, so neither can ever proceed.

Step	Transaction A	Transaction B
1	locks Record 1	locks Record 2
2	wants Record 2 → waits for B	wants Record 1 → waits for A
3	blocked forever	blocked forever

A holds Record 1 and waits for Record 2; B holds Record 2 and waits for Record 1 — a circular wait that will never resolve on its own. Databases handle deadlock by one of:

• Detection and recovery: the DBMS periodically looks for a cycle in the "who waits for whom" graph and, on finding one, rolls back one transaction (the victim) to break it; that transaction is then retried.
• Timeout: a transaction that has waited longer than a set limit is assumed deadlocked and rolled back.
• Prevention: require transactions to acquire all needed locks at the start, or always lock records in a fixed global order, so a cycle can never form.

The key insight for the exam is that deadlock needs a circular dependency of locks, and the usual cure is to roll one transaction back so the other can complete. It is worth comparing the three strategies. Detection lets deadlocks happen and cleans them up afterwards, which is simple and has no overhead when there is no deadlock, but means a transaction occasionally gets aborted and retried. Timeout is even simpler but crude — it cannot tell a genuine deadlock from a transaction that is merely slow, so it may roll back a transaction that was about to succeed. Prevention avoids deadlock entirely (for example, by always locking records in a fixed global order, so a cycle is impossible) but constrains how transactions may be written and can reduce concurrency by holding locks longer than strictly necessary. Most production databases choose detection and recovery as the default, because deadlocks are rare in well-designed schemas and aborting the occasional victim is cheaper than the restrictions prevention imposes. Whichever is used, the unavoidable consequence is that one transaction's work is lost and must be redone — which is acceptable precisely because atomicity guarantees the rolled-back transaction leaves no trace behind.

---

Data Redundancy vs Data Consistency

The specification asks you to distinguish two ideas that sound similar but are opposites in spirit.

• Data consistency means the database never contradicts itself — every copy of a fact agrees, and all constraints hold. Transactions exist largely to protect consistency: by being atomic and isolated, they guarantee the database only ever moves between valid, self-agreeing states.
• Data redundancy means deliberately keeping duplicate copies of data (or hardware) so that a failure does not lose it. Note this is a different sense of "redundancy" from the normalisation lesson: there, redundancy within a table was a fault causing anomalies; here, redundancy across disks or sites is a deliberate resilience strategy. The distinction is one of purpose and location: duplicating a fact inside one table serves no purpose and risks the copies disagreeing (an integrity problem), whereas duplicating the whole, consistent database onto a second disk or a remote site serves the clear purpose of surviving hardware failure. Examiners value candidates who can hold these two meanings apart and say which one a given scenario is about.

Aspect	Data consistency	Data redundancy (resilience)
What it is	Data never disagreeing with itself	Spare copies of data/hardware
Why we want it	Correctness / integrity	Survive disk failure, disaster
Provided by	Transactions, constraints, locking	Backups, RAID, replication, off-site copies

Redundancy mechanism	Description
Backups	Periodic copies (full / incremental / differential) kept separately, so data can be restored after loss.
RAID / mirroring	Multiple disks holding the same data so one disk failing loses nothing.
Replication / off-site copies	Live copies on other servers or in other locations, surviving a whole-site disaster.