Isolation Levels and Concurrency

Many users hit a database at once. Isolation decides how much concurrent transactions can see of each other's changes — trading correctness guarantees against performance.

What can go wrong

• Dirty read — reading another transaction's uncommitted change, which might later be rolled back.
• Non-repeatable read — reading the same row twice in one transaction and getting different values, because another transaction committed a change in between.
• Phantom read — running the same query twice and getting extra or missing rows.
• Lost update — two transactions read a value, both compute a new value from it, and the second write silently overwrites the first.

The standard isolation levels

-- PostgreSQL
BEGIN ISOLATION LEVEL SERIALIZABLE;

Higher levels are safer but can cause more waiting or serialization failures that the application must retry.

The lost update problem

Two people buy the last item at the same moment:

Transaction A                        Transaction B
SELECT stock → 1
                                     SELECT stock → 1
UPDATE products SET stock = 0
                                     UPDATE products SET stock = 0
COMMIT                               COMMIT        -- both sold the last item!

Three common fixes:

1. Update atomically, with the check in the statement:

-- standalone
CREATE TABLE stock (product_id INTEGER PRIMARY KEY, quantity INTEGER);
INSERT INTO stock VALUES (1, 1);

UPDATE stock SET quantity = quantity - 1 WHERE product_id = 1 AND quantity > 0;
SELECT changes() AS rows_updated;
UPDATE stock SET quantity = quantity - 1 WHERE product_id = 1 AND quantity > 0;
SELECT changes() AS rows_updated;

+--------------+
| rows_updated |
+--------------+
|            1 |
+--------------+
+--------------+
| rows_updated |
+--------------+
|            0 |
+--------------+

The second buyer's update matches no row (stock is already 0), and the application sees 0 rows updated and reports "sold out". Simple and effective.

2. Lock the row while deciding (pessimistic locking):

-- PostgreSQL / MySQL
BEGIN;
SELECT quantity FROM stock WHERE product_id = 1 FOR UPDATE;   -- others wait here
-- ...check and update...
COMMIT;

3. Detect conflicts with a version column (optimistic locking): each row has a version number; updates include WHERE id = ? AND version = ? and increment it. If no row matches, someone else changed it first — reload and retry. ORMs often support this directly.

Deadlocks

A deadlock happens when two transactions each wait for a lock the other holds. The database detects it and aborts one of them. Reduce deadlocks by keeping transactions short and touching rows in a consistent order — and make the application retry aborted transactions.