Transactions and isolation

A transaction is a unit of work that either all happens or none of it does.

BEGIN;
  UPDATE account SET cents = cents - 1000 WHERE id = 1;
  UPDATE account SET cents = cents + 1000 WHERE id = 2;
COMMIT;

(account is the textbook example rather than one of our four tables — the two-sided transfer is the shortest thing that shows why atomicity is not optional.)

Both engines run in autocommit by default: a statement outside an explicit transaction is its own transaction. So the code above without BEGIN is two separate transactions, and a crash between them loses the money. Most connection libraries and ORMs manage this for you — which means the first thing to know about your stack is where the transaction boundary actually is.

ROLLBACK undoes everything since BEGIN. A SAVEPOINT lets you undo part of it, which is how an ORM implements a nested transaction.

What isolation is protecting you from

When transactions overlap, four things can go wrong. The names are worth knowing because they are how the levels are defined:

And the levels, in increasing strictness:

The defaults are different, and that matters

Two consequences for code that has to run on both:

A SELECT repeated in one transaction behaves differently. In PostgreSQL’s default it sees a fresh snapshot each statement, so a row can change under you. In MySQL’s default it sees the snapshot from the first read, so it cannot. A report that runs several queries and expects them to agree is correct by accident on MySQL and wrong by default on PostgreSQL.

PostgreSQL’s REPEATABLE READ and SERIALIZABLE can abort your transaction. You get could not serialize access due to concurrent update — SQLSTATE 40001 — and the transaction must be retried from the beginning. This is not an error to log and move on from; it is a normal outcome that the caller has to handle.

Lost updates, and the two ways to prevent them

This is the concurrency bug that actually appears in applications.

-- Two requests run this at the same time. Both read 10, both write 11.
-- One increment is gone, and nothing failed.
SELECT quantity FROM order_item WHERE order_id = 1 AND product_id = 5;   -- 10
UPDATE order_item SET quantity = 11 WHERE order_id = 1 AND product_id = 5;

Three fixes:

-- 1. Do the arithmetic in the database. Correct at every isolation level.
UPDATE order_item SET quantity = quantity + 1 WHERE order_id = 1 AND product_id = 5;

-- 2. Pessimistic lock: take the row and make the other transaction wait.
BEGIN;
  SELECT quantity FROM order_item
   WHERE order_id = 1 AND product_id = 5
     FOR UPDATE;
  -- ... compute in the application ...
  UPDATE order_item SET quantity = :new WHERE order_id = 1 AND product_id = 5;
COMMIT;

-- 3. Optimistic lock: a version column, and check you are updating what you read.
UPDATE order_item SET quantity = :new, version = version + 1
 WHERE order_id = 1 AND product_id = 5 AND version = :version_i_read;
-- 0 rows affected means somebody else got there first. Re-read and retry.

Option 1 whenever the new value is a function of the old one. Option 3 scales better than option 2 under contention, because nobody waits — but you must handle the retry.

Locking reads, and the two clauses that make queues work

SELECT ... FOR UPDATE;     -- exclusive: nobody else may read-for-update or write
SELECT ... FOR SHARE;      -- shared: others may also read, none may write

By default a locking read waits for whoever holds the row. Both engines let you change that:

SELECT * FROM "order" WHERE status = 'new'
ORDER BY placed_at LIMIT 1
FOR UPDATE SKIP LOCKED;    -- skip rows someone else has; take the next free one

SELECT ... FOR UPDATE NOWAIT;   -- fail immediately instead of waiting

SKIP LOCKED is how you build a work queue in SQL, and it is supported by PostgreSQL 9.5+ and MySQL 8.0+. Without it, ten workers polling the same table all block behind the same row and you have a queue of one.

Gap locks: MySQL’s extra behaviour

In REPEATABLE READ, InnoDB does not just lock the rows it finds — it locks the gaps between index values, to stop another transaction inserting a row that would change the result. These are next-key locks.

-- InnoDB locks the range, so a concurrent INSERT of placed_at = '2026-01-15' waits
SELECT * FROM `order`
 WHERE placed_at BETWEEN '2026-01-01' AND '2026-01-31'
   FOR UPDATE;

This prevents phantoms, and it also causes deadlocks that surprise people, because transactions block each other over rows that do not exist. If a MySQL workload deadlocks in ways that make no sense from the row-level reasoning, gap locks are the first place to look. READ COMMITTED disables most of them, at the cost of the guarantee.

PostgreSQL has no gap locks. Its REPEATABLE READ gets the same protection from snapshots, and SERIALIZABLE detects the conflict at commit time instead of preventing it with a lock.

Deadlocks are normal

Two transactions each holding what the other wants. The engine detects the cycle, kills one, and returns an error:

A deadlock is not a bug to eliminate; it is a condition to handle. Retry the whole transaction — retrying the statement is meaningless because the transaction is already rolled back.

Reduce the frequency by having every transaction touch rows in the same order. Most application deadlocks are two code paths that update the same two tables in opposite sequences.

Long transactions are an operational problem

A transaction left open for minutes costs more than the lock it holds.

• PostgreSQL: VACUUM cannot remove row versions that an open transaction might still need, so dead rows accumulate — table bloat. A single forgotten transaction can grow a table for hours. Watch pg_stat_activity for state = 'idle in transaction'.
• MySQL: the undo log grows for the same reason, and the history list length with it.

Keep transactions short and do not hold one open across a network call to another service. That is the pattern that turns a slow third party into a database incident.

One more difference: DDL

This is why PostgreSQL migrations can be written as one atomic step and MySQL migrations cannot. In MySQL, each statement must be independently safe to have applied on its own — which is the same discipline the next lesson recommends anyway.

What to take away

• PostgreSQL defaults to READ COMMITTED, MySQL to REPEATABLE READ. The same code has different anomalies.
• PostgreSQL’s stricter levels abort transactions with 40001. Handle the retry.
• Prevent lost updates by computing in the database, FOR UPDATE, or a version column.
• SKIP LOCKED is what makes a SQL work queue possible.
• MySQL’s gap locks prevent phantoms and cause surprising deadlocks.
• Retry the transaction on deadlock, and order your writes consistently to reduce them.
• MySQL DDL commits implicitly; a half-applied migration stays applied.