A transaction is a group of SQL operations that execute as a single unit - either all operations succeed (COMMIT) or all operations fail (ROLLBACK). Think of a transaction like a bank transfer: you debit $100 from Account A and credit $100 to Account B. Both operations must succeed together - you cannot debit without crediting (money disappears) or credit without debiting (money created from nothing). Transactions guarantee ACID properties: Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions do not interfere), Durability (committed changes persist forever). Without transactions, databases would be unreliable: money could disappear, inventory could go negative, bookings could double-book. Every production database uses transactions to maintain data integrity.

ACID Properties Deep Dive:

ACID is the foundation of database reliability. Every transaction must guarantee four properties:

1. Atomicity (All-or-Nothing):

Atomicity means a transaction executes completely or not at all. There is no partial execution. If any operation in the transaction fails, all operations are rolled back (undone).

Example: Bank transfer $100 from Account A to Account B

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'; -- Debit

UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B'; -- Credit

COMMIT;

Atomicity guarantee: Both updates succeed or both fail. You cannot have:

- Debit succeeds, credit fails (money disappears)

- Debit fails, credit succeeds (money created from nothing)

How atomicity works: Database maintains a transaction log (write-ahead log). Every change is logged before being applied. If transaction fails, database uses log to undo all changes (rollback). If transaction succeeds, changes are made permanent (commit).

Real-world atomicity failure: Knight Capital lost $440 million in 45 minutes (2012) due to transaction errors. Their trading system executed buy orders without corresponding sell orders (atomicity violated). Result: Massive financial loss from partial transactions.

2. Consistency (Valid State to Valid State):

Consistency means a transaction moves the database from one valid state to another valid state. All constraints, triggers, and business rules are enforced.

Example: Account balance must be ≥ 0 (business rule)

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';

-- Check constraint: balance >= 0

IF (SELECT balance FROM accounts WHERE account_id = 'A') < 0 THEN

ROLLBACK; -- Violates consistency, undo transaction

ELSE

COMMIT; -- Maintains consistency, save transaction

END IF;

Consistency guarantee: Database enforces constraints (CHECK, FOREIGN KEY, UNIQUE, NOT NULL). If any constraint is violated, transaction is rolled back.

Consistency types:

- Database consistency: Constraints enforced by database (CHECK, FK, UNIQUE)

- Application consistency: Business rules enforced by application (balance >= 0, inventory >= 0)

- Referential consistency: Foreign keys maintain relationships (order must reference valid customer)

Real-world consistency failure: TSB Bank outage (2018) affected 1.9 million customers due to consistency violations during data migration. Customer balances became inconsistent (some accounts showed wrong balances). Result: 18-day outage, £330 million cost.

3. Isolation (Concurrent Transactions Do Not Interfere):

Isolation means concurrent transactions execute as if they were running sequentially. One transaction cannot see uncommitted changes from another transaction.

Example: Two users transfer money from same account simultaneously

Transaction 1: Transfer $100 from Account A to Account B

Transaction 2: Transfer $50 from Account A to Account C

Without isolation:

- Both transactions read balance = $200

- Transaction 1 debits $100, balance = $100