Transaction Processing

This lesson covers transaction processing for the OCR A-Level Computer Science (H446) specification. Transactions ensure that database operations are reliable, consistent, and safe, even when multiple users access the database simultaneously or when failures occur.

---

What is a Transaction?

A transaction is a sequence of one or more database operations that are treated as a single logical unit of work. Either ALL operations in the transaction succeed, or NONE of them do.

Example: Bank Transfer

Transferring 100 pounds from Account A to Account B involves two operations:

1. Deduct 100 from Account A.
2. Add 100 to Account B.

Both operations must succeed together. If the system crashes after step 1 but before step 2, money would be lost. A transaction ensures this cannot happen.

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

If an error occurs, the transaction is rolled back (all changes are undone):

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';
    -- Error occurs here!
ROLLBACK;  -- Both accounts are unchanged

---

ACID Properties

The ACID properties are the four guarantees that a database transaction must provide:

Property	Description	Example
Atomicity	A transaction is "all or nothing" -- either all operations complete successfully, or none of them do.	If the debit succeeds but the credit fails, both are rolled back.
Consistency	A transaction moves the database from one valid state to another. All rules, constraints, and triggers are satisfied.	The total money across all accounts remains the same after a transfer.
Isolation	Concurrent transactions do not interfere with each other. Each transaction behaves as if it is the only one running.	Two simultaneous transfers do not see each other's partial results.
Durability	Once a transaction is committed, the changes are permanent and survive system failures (e.g., power loss, crashes).	After COMMIT, the data is written to disk and will not be lost.

Exam Tip: You MUST be able to define all four ACID properties and give an example for each. A common exam question asks you to explain how ACID properties apply to a specific scenario (e.g., online banking, ticket booking).

---

Record Locking

Record locking is a mechanism that prevents multiple users from modifying the same data simultaneously, which could lead to inconsistencies.

The Lost Update Problem

Without locking:

1. User A reads the stock level: 10.
2. User B reads the stock level: 10.
3. User A sells 1 item, writes stock = 9.
4. User B sells 1 item, writes stock = 9. (Should be 8!)

How Locking Works

Lock Type	Description
Exclusive lock (write lock)	Only one user can read or write the record. All other users must wait.
Shared lock (read lock)	Multiple users can read the record, but no one can write until all shared locks are released.

Deadlock