Lesson 18: Isolation Levels & Locking

Isolation levels control how much one transaction can see of another's uncommitted changes — a real tradeoff between consistency and concurrency.

Real-time example

Two customers try to book the last seat on a flight at the same instant — isolation level determines whether both can succeed (a bug) or only one.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT seats_available FROM flights WHERE id = 1 FOR UPDATE;
-- if seats_available > 0: book it, decrement, COMMIT
-- else: ROLLBACK

What's happening

SELECT ... FOR UPDATE locks the row so a second concurrent transaction must wait until the first commits or rolls back.

Higher isolation prevents more anomalies but reduces concurrency — pick the lowest level that still keeps your data correct.