Transactions and ACID Properties

A transaction is a sequence of one or more SQL operations that are treated as a single logical unit of work. Either all operations in the transaction succeed (and are committed), or none of them take effect (and are rolled back). Transactions are essential for maintaining data integrity, especially in multi-user environments.

---

What is a Transaction?

Consider a bank transfer: moving 100 pounds from Account A to Account B requires two operations:

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

If the system crashes after step 1 but before step 2, the 100 pounds would vanish — the money has been deducted but never added. By wrapping both operations in a transaction, we guarantee that either both happen or neither happens.

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 database performs a rollback:

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

---