← SQL roadmap

Topic 27 of 52 · Expert

Transactions & ACID

COMMIT, ROLLBACK, isolation levels and locking.

Concept

A transaction groups statements so they all succeed or all fail. ACID: Atomicity, Consistency, Isolation, Durability.

Isolation levels trade safety for concurrency: READ UNCOMMITTED (dirty reads), READ COMMITTED, REPEATABLE READ (InnoDB default) and SERIALIZABLE.

SELECT ... FOR UPDATE locks rows you plan to change. Deadlocks happen when two transactions wait on each other; InnoDB rolls one back, so apps should retry.

Cheat sheet

LevelDirty readNon-repeatablePhantom
READ UNCOMMITTEDYesYesYes
READ COMMITTEDNoYesYes
REPEATABLE READNoNoMostly no (InnoDB)
SERIALIZABLENoNoNo

Code (SQL · MySQL 8)

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;   -- or ROLLBACK on error

Remember

  • InnoDB default isolation = REPEATABLE READ.
  • Lock rows in a consistent order to avoid deadlocks.
  • Keep transactions short.

Interview questions

Explain ACID.

Atomicity: all or nothing. Consistency: constraints stay valid. Isolation: concurrent transactions don't see each other's partial work. Durability: committed data survives crashes.

What is a phantom read?

Re-running a range query in the same transaction returns new rows inserted by another transaction.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Transactions & ACID” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.