A bank cannot charge the same payroll twice. An airline must not sell the last seat to two passengers at the same time. Currency exchange bets, shopping carts, like counters: behind all of these there is the same technical problem, concurrency, and the same question: what happens when two transactions want to touch the same data at the same time?
A transaction is a unit of work that must run as a whole: either it applies completely, or nothing is applied. Like a wire transfer, which credits one account and debits another in a single indivisible operation.
The problem: getting lost in the race
Imagine two customers checking the balance of the same account at the same time, and both read 100 euros. Both ask to withdraw 70. Each computes its new balance (30), but if the system does not coordinate the writes, the second overwrites the first: the account is left at 30 instead of -40. That disaster has a formal name: a lost update.
The database does not guess that you want to protect yourself. You decide how much protection you want by choosing an isolation level, a contract that defines which interferences between transactions you are willing to tolerate in exchange for performance.
The four levels defined by the standard
The SQL standard SQL:1992 defines four levels, from least to most strict. Each one eliminates a specific set of anomalies:
- Read Uncommitted: a transaction can read data that another has not yet committed. This brings dirty reads: you read a value that two seconds later is rolled back and never existed. It is a showcase of maximum performance, rarely used in production.
- Read Committed (the default in PostgreSQL, SQL Server and Oracle): only already-committed data is read. It eliminates dirty reads, but allows non-repeatable reads: two reads of the same row within a transaction return different values because another transaction modified and committed it in between.
- Repeatable Read: a row you read is “frozen” for your transaction; you always read it the same. But the phantom read can still appear: a second query with the same filter returns more rows, because another transaction inserted new records that match the condition.
- Serializable: the strictest level. Transactions behave as if they ran one after another, in series. No anomaly survives, but the database pays the price in locks and performance.
The weapons: locks and multi-versioning
To enforce these guarantees, engines use two broad strategies. The first is locks: a transaction asks for a lock on a row or object, and the others wait until it is released. The danger is the deadlock: transaction A locks row 1 and waits for row 2; transaction B locks row 2 and waits for row 1. Neither gives in, and the system must detect it and abort one of them.
The second, more elegant and widespread, is multi-version concurrency control (MVCC), the mechanism of PostgreSQL and of MySQL’s InnoDB engine. Instead of blocking reads, MVCC keeps several historical versions of each row. Each transaction sees “a snapshot” of the state from the moment it started. Writers do not block readers: a reader simply sees the old version until the writer commits. That is why in PostgreSQL a SELECT never waits for a competing UPDATE on the same row.
Each version is identified with a transaction number. When a row is modified, the previous version is not overwritten: a new one is created and the old one is marked obsolete for transactions that started before the change. The space occupied by dead versions is reclaimed by a cleanup process that PostgreSQL calls VACUUM.
Real cases: not all engines are alike
Levels are a standard, but each engine implements them in its own way. MySQL in its classic configuration uses Repeatable Read as its default level and avoids phantoms with a special lock on index ranges called a gap lock. PostgreSQL, on the other hand, defaults to Read Committed and also resolves phantoms when you ask for Repeatable Read or Serializable, using serialization predicates that detect when two transactions have depended on overlapping conditions.
Choosing the level is a trade-off of engineering. A business analysis that only reads can relax guarantees and gain speed; a payments system, instead, should aim for Repeatable Read or Serializable and accept more locking. The choice is never “always the highest level”, because each level of costly protection translates into transactions that wait longer or abort more often.
Practical technical precautions
Several frequent traps: do not assume that an isolated read is a transaction; in the default level of MySQL and PostgreSQL, autocommit makes each statement its own transaction, so “read, think and write” in three separate statements is not protected. The correct way to attack a race is to wrap the related operations in an explicit transaction (with BEGIN ... COMMIT) and, when the business requires it, add pessimistic locking with SELECT ... FOR UPDATE, which reserves the row and stops anyone else from touching it until you commit. For retries, add a transaction timeout and a retry code for deadlocks: it is the resilience strategy that practically all banking services use.
Understanding isolation is understanding that the database is not omniscient: it is a contract that you choose to sign. The next time a query stays “pending” or returns an impossible number, you will know exactly which isolation level you are playing at, and who is blocking whom meanwhile.
