SQL Transactions & ACID Properties Explained

You send ₹5,000 to a friend on UPI. Two things must happen: your balance goes down, their balance goes up. Now imagine the server crashes between those two updates. Your money left… and never arrived. Would you ever use that app again? The reason this never happens is the single most important idea in databases: the transaction — several statements wrapped into one all-or-nothing unit. This lesson covers transactions, the famous ACID guarantees, and what happens when many transactions run at once.

START, COMMIT, ROLLBACK — the all-or-nothing wrapper

START TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE account_id = 'A101';
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 'B202';

COMMIT;   -- both changes become permanent, together

Between START and COMMIT, the changes are provisional — visible to you, invisible (by default) to everyone else, and fully reversible with ROLLBACK. Crash mid-way? The database rolls back automatically on recovery. Two facts to hold onto: COMMIT is the point of no return — no ROLLBACK can undo it — and MySQL runs in autocommit by default, silently wrapping every single statement in its own tiny transaction until you say START TRANSACTION.

ACID — four promises, one per letter

• A — Atomicity: all statements apply, or none do. The UPI transfer either fully happens or fully doesn't; money can never be "in between".
• C — Consistency: a transaction moves the database from one valid state to another — every constraint, key and rule from lesson 7 still holds at the end.
• I — Isolation: concurrent transactions don't trample each other; each behaves as if it were alone (how strictly — that's isolation levels, below).
• D — Durability: once committed, survived — power cut, crash, whatever. Engines guarantee this by writing a log to disk before saying "committed".

Don't just memorise the expansion — attach the UPI transfer to A, constraints to C, the anomalies below to I, and crash-recovery to D. Stories survive interviews; acronyms alone don't.

SAVEPOINT — checkpoints inside a transaction

START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 'A101';
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 'A101';
ROLLBACK TO SAVEPOINT sp1;   -- undoes only the 5000 debit
COMMIT;                      -- the 200 debit is saved

A SAVEPOINT is a named bookmark: ROLLBACK TO SAVEPOINT undoes only the work after it, keeping the rest of the transaction alive. Perfect for multi-step batches where step 3 failing shouldn't throw away steps 1–2.

The three read anomalies — what goes wrong without isolation

Concurrency bugs come in three named flavours. Learn them as tiny stories:

• Dirty read: T2 reads a balance T1 changed but hasn't committed; T1 rolls back. T2 acted on money that never officially existed.
• Non-repeatable read: T1 reads a row, T2 commits a change to it, T1 reads the same row again — different value inside one transaction.
• Phantom read: T1 runs COUNT(*) WHERE city = 'Pune', T2 commits a new Pune row, T1 re-runs the query — a phantom row appeared. (Non-repeatable = same row changed; phantom = new rows appeared. Interviewers test exactly this distinction.)

The four isolation levels — the safety dial

Stricter = safer but slower under load — transactions block each other more. That trade is the whole design space. (*The asterisk is a nice flex: InnoDB's REPEATABLE READ also blocks most phantoms in practice via next-key locking — mention it and watch the interviewer sit up.) Set it per session with SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Locks and deadlocks — when transactions collide

Isolation is implemented with locks: a transaction updating a row holds a lock on it until commit/rollback, and others wanting that row wait. Two patterns to know by name:

-- Pessimistic locking: grab the lock BEFORE deciding
START TRANSACTION;
SELECT stock_qty FROM inventory WHERE item_id = 1 FOR UPDATE;
UPDATE inventory SET stock_qty = stock_qty - 1 WHERE item_id = 1;
COMMIT;

-- Optimistic locking: don't lock, verify at write time
UPDATE inventory
SET stock_qty = stock_qty - 1, version = version + 1
WHERE item_id = 1 AND version = 3;   -- 0 rows affected ⇒ someone beat you; retry

And the famous failure: deadlock — T1 holds row A and wants B; T2 holds B and wants A. A circular wait, forever… except the engine's deadlock detector spots the cycle and kills one transaction so the other can finish. The classic prevention is disarmingly simple: make every transaction lock rows in the same order — no cycle can form. You'll act this whole drama out in the practice.

Common mistakes

• Believing COMMIT can be rolled back — it can't; undoing it means a new compensating transaction.
• Reciting ACID without examples — attach a story to each letter.
• Confusing non-repeatable reads (same row changed) with phantoms (new rows appeared).
• Forgetting MySQL's default level is REPEATABLE READ (not READ COMMITTED like PostgreSQL and Oracle).
• Read-then-update without FOR UPDATE or a version check — the classic lost-update bug in every naive inventory system.

Quick recap

Practice Zone — PYQs from real selection rounds

Bank transfers, savepoints, and optimistic locking — the exact scenarios payment and product companies ask about.

Q1Atomicityeasy

A money transfer debits account A, then the system crashes before crediting account B. What does atomicity guarantee?

Asked in

AThe debit stays; B is credited after restartBThe whole transaction rolls back — the debit never happenedCBoth accounts are frozen until an admin decidesDThe bank absorbs the difference

Q2COMMIT vs ROLLBACKeasy

Can you ROLLBACK changes after you've issued COMMIT?

Asked in

AYes, within 60 secondsBNo — committed changes are durable and finalCYes, if no one else has read the dataDOnly for UPDATE statements, not INSERTs

Q3Isolation levelsmedium

Which anomaly does the READ COMMITTED isolation level prevent?

Asked in

ADirty reads onlyBDirty and non-repeatable readsCAll three, including phantomsDNone — that's READ UNCOMMITTED

Q4Read anomaliesmedium

T1 updates a balance to 500 but hasn't committed. T2 reads 500. T1 then rolls back. What did T2 just experience?

Asked in

AA dirty read — it saw data that never officially existedBA non-repeatable readCA phantom readDA lost update

Q5Deadlockhard

T1 locks row A and waits for row B; T2 holds B and waits for A. What happens in MySQL?

Asked in

ABoth wait forever until the server restartsBThe deadlock detector aborts one transaction so the other proceedsCBoth transactions are committed as-isDMySQL merges the two transactions

FAQ

Next lesson: naming your queries so humans can read them — Lesson 11: Views & CTEs →