Understanding Isolation Levels

1. Understanding Read Committed

Property	Detail
Default	Yes
Sees	Snapshot at start of each statement
Prevents	Dirty reads
Allows	Non-repeatable reads, phantoms

Property	Detail
Sees	Snapshot at first statement of TX
Prevents	Dirty + non-repeatable reads + phantoms (PG-specific)
Conflicts	Raises serialization_failure (40001) on concurrent updates

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- SSI: predicate locks detect conflicts
COMMIT;   -- may raise 40001 — retry

Implementation	SSI (Serializable Snapshot Isolation)
Prevents	All anomalies
Cost	Predicate locks + retry logic required

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

ALTER DATABASE shop SET default_transaction_isolation = 'repeatable read';
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Phenomenon	RC	RR	S
Dirty read	No	No	No
Non-repeatable	Yes	No	No
Phantom	Yes	No (PG)	No
Serialization anomaly	Yes	Yes	No

-- Pseudocode retry loop
for attempt in 1..5 loop
   BEGIN ISOLATION LEVEL SERIALIZABLE;
   ... ;
   COMMIT;     -- on 40001 retry
end loop;

Warning: Application MUST retry on SQLSTATE 40001 / 40P01 — server cannot transparently retry.

BEGIN READ ONLY;            -- forbids writes; allows planner optimizations
SELECT ... ;
COMMIT;

BEGIN READ WRITE;           -- default mode