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

---

The ACID Properties

The ACID properties are four guarantees that database transactions must provide:

A — Atomicity

A transaction is all or nothing. If any part of the transaction fails, the entire transaction is rolled back. No partial updates are left in the database.

Example: In the bank transfer above, either both the debit and credit happen, or neither does.

C — Consistency

A transaction takes the database from one valid state to another valid state. All rules, constraints, and integrity conditions (such as foreign keys, NOT NULL, and CHECK constraints) must be satisfied before and after the transaction.

Example: If a constraint says Balance >= 0, a transaction that would make a balance negative will be rejected, preserving consistency.

I — Isolation

Concurrent transactions do not interfere with each other. Each transaction behaves as if it is the only transaction running, even when many transactions execute simultaneously. Intermediate states of a transaction are invisible to other transactions.

Example: If User 1 is transferring money and User 2 checks the account balance at the same time, User 2 will see either the state before the transfer or the state after — never a partially completed transfer.

D — Durability

Once a transaction has been committed, its changes are permanent and will survive system crashes, power failures, or other errors. The data is written to non-volatile storage.

Example: After the bank transfer is committed, even if the server crashes immediately afterwards, the changes will be preserved when the system restarts.

---

Summary of ACID

Property	Guarantee	What Happens Without It
Atomicity	All or nothing	Partial updates leave data in an inconsistent state
Consistency	Valid state to valid state	Constraints could be violated, leaving invalid data
Isolation	No interference between concurrent transactions	Users could see partially completed operations
Durability	Committed changes are permanent	Completed transactions could be lost after a crash

---

Transaction Control Statements