Why this matters
- Wrong isolation assumptions cause double charges, negative stock, and duplicate loyalty points at VaultCommerce scale.
READ COMMITTEDis Postgres default — knowing what it allows and blocks prevents surprise bugs.- Long transactions hold locks and block checkout; isolation tuning is a performance and correctness decision.
- Senior interviews expect dirty read, non-repeatable read, and phantom read definitions with examples.
The isolation spectrum
SQL defines four standard levels. Each prevents a class of concurrency anomaly:
Anomalies and prevention
- Dirty read — Reading uncommitted data from another transaction. Prevented by READ COMMITTED and above.
- Non-repeatable read — Same query returns different rows within one transaction. Prevented by REPEATABLE READ and above.
- Phantom read — New rows appear in a repeated range scan. Prevented by SERIALIZABLE (Postgres REPEATABLE READ also blocks phantoms via MVCC).
Postgres implements READ UNCOMMITTED as READ COMMITTED — dirty reads never occur.
Postgres defaults and MVCC
Multi-Version Concurrency Control keeps old row versions visible to snapshots. Readers do not block writers; writers do not block readers. Conflicts appear when two transactions try to update the same row:
-- Session A
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 881;
-- holds row lock until COMMIT
-- Session B (blocked until A commits or rolls back)
UPDATE products SET stock = stock - 1 WHERE id = 881;
VaultCommerce checkout uses short transactions — lock duration is milliseconds, not seconds.
Isolation levels in practice
-- Per-transaction override
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM products WHERE id = 881; -- returns 5
-- another session commits stock = 4
SELECT stock FROM products WHERE id = 881; -- still returns 5 (snapshot)
COMMIT;
| Aspect | Level | VaultCommerce usage |
|---|---|---|
| READ COMMITTED | Default | Checkout, cart updates, most APIs |
| REPEATABLE READ | Snapshot stable | Financial reconciliation scripts |
| SERIALIZABLE | Strictest | Rare; promo code redemption counters |
READ COMMITTED
LevelDefaultVaultCommerce usageCheckout, cart updates, most APIsREPEATABLE READ
LevelSnapshot stableVaultCommerce usageFinancial reconciliation scriptsSERIALIZABLE
LevelStrictestVaultCommerce usageRare; promo code redemption counters
Explicit locking with SELECT FOR UPDATE
When application logic reads then writes, use pessimistic locking:
BEGIN;
SELECT stock FROM products
WHERE id = 881
FOR UPDATE; -- exclusive row lock
-- Application validates stock >= qty
UPDATE products SET stock = stock - 1 WHERE id = 881;
COMMIT;
FOR UPDATE serialises competing checkouts on the same SKU. Without it, two READ COMMITTED sessions can both read stock = 1 and both decrement.
Optimistic concurrency
Alternative pattern: version column instead of explicit locks.
ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0;
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 881 AND version = 5;
-- 0 rows updated → concurrent modification, retry or fail
VaultCommerce uses optimistic locking on low-contention admin edits; pessimistic FOR UPDATE on hot inventory rows during flash sales.
Observing locks
SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;
Blocked sessions appear with granted = false. VaultCommerce on-call checks this view when checkout latency spikes during promotions.
Quick recall
Everything you need if you only revisit this box.
- Isolation levels trade concurrency for consistency; Postgres default is READ COMMITTED.
- MVCC lets readers and writers proceed without blocking unless they target the same row.
SELECT FOR UPDATEprevents lost updates on hot inventory rows.- Optimistic locking with a version column suits low-contention updates.
- Keep transactions short — locks and snapshots held too long stall checkout.
Test yourself
Answer these before moving on — recall is what makes it stick.