Concurrency Control and Isolation Levels

Concurrency problems

Dirty read: reading uncommitted data.
Non-repeatable read: same query returns different row values.
Phantom read: additional rows appear between repeated scans.
Lost update: one transaction silently overwrites another.

Isolation levels in practice

Higher isolation gives stronger correctness but may reduce throughput due to locking and conflict retries.

Setting isolation level

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

SELECT stock FROM products WHERE product_id = 77;
-- perform business checks
UPDATE products SET stock = stock - 1 WHERE product_id = 77;

COMMIT;