SQL Transaction Isolation Levels

-- Check current isolation level
SHOW transaction_isolation;

-- Set isolation level for a session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set for a single transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... queries ...
COMMIT;

-- READ COMMITTED (default in PostgreSQL)
-- Each statement sees most recently committed data
BEGIN;
  SELECT balance FROM accounts WHERE id = 1; -- sees 100
  -- Another transaction commits: balance = 50
  SELECT balance FROM accounts WHERE id = 1; -- sees 50 (different!)
COMMIT;

-- REPEATABLE READ
-- Transaction sees snapshot from start, prevents non-repeatable reads
BEGIN ISOLATION LEVEL REPEATABLE READ;
  SELECT balance FROM accounts WHERE id = 1; -- sees 100
  -- Another transaction commits: balance = 50
  SELECT balance FROM accounts WHERE id = 1; -- still sees 100
COMMIT;

-- SERIALIZABLE (strictest)
-- Prevents phantom reads, full serializability
BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT SUM(amount) FROM orders WHERE user_id = 1; -- 500
  INSERT INTO orders (user_id, amount) VALUES (1, 100);
  -- If concurrent tx also inserted, one will be rolled back
COMMIT; -- may get serialization failure, must retry

-- Practical pattern: retry on serialization failure
-- Application code:
-- while True:
--   try:
--     execute_transaction()
--     break
--   except SerializationFailure:
--     continue

-- Advisory locks (application-level)
SELECT pg_advisory_lock(12345);   -- blocks until acquired
-- ... critical section ...
SELECT pg_advisory_unlock(12345);

-- Try lock (non-blocking)
SELECT pg_try_advisory_lock(12345); -- returns true/false