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

---