Transaction Isolation Levels

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Per-transaction isolation
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- ... queries ...
COMMIT;

-- Check current isolation level
SHOW transaction_isolation;

Pessimistic Locking

-- Lock rows for update
SELECT * FROM accounts
WHERE account_id = 123
FOR UPDATE;

-- Skip locked rows (non-blocking)
SELECT * FROM accounts
WHERE account_id = 123
FOR UPDATE SKIP LOCKED;

-- Lock only if exists
SELECT * FROM accounts
WHERE account_id = 123
FOR UPDATE NOWAIT;

-- Share lock (read lock)
SELECT * FROM accounts
WHERE account_id = 123
FOR SHARE;

Optimistic Locking

-- Version-based optimistic locking
UPDATE accounts
SET balance = balance - 100,
    version = version + 1
WHERE account_id = 123
  AND version = 5  -- Expected version
RETURNING *;

-- Timestamp-based optimistic locking
UPDATE accounts
SET balance = balance - 100,
    last_modified = CURRENT_TIMESTAMP
WHERE account_id = 123
  AND last_modified = '2024-01-15 10:30:00'::TIMESTAMP
RETURNING *;

Deadlock Prevention

-- Consistent lock ordering
BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE;
-- ... perform transfer ...
COMMIT;

-- Use NOWAIT to fail fast
BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE NOWAIT;
-- If locked, fails immediately instead of waiting
COMMIT;

Advisory Locks

-- Application-level locks
SELECT pg_advisory_lock(12345);  -- Acquire advisory lock
-- ... perform operation ...
SELECT pg_advisory_unlock(12345);  -- Release

-- Try to acquire (non-blocking)
SELECT pg_try_advisory_lock(12345);

-- Transaction-scoped advisory locks
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- Lock released at COMMIT/ROLLBACK
COMMIT;

Batch Processing with SKIP LOCKED

-- Process queue without conflicts
WITH batch AS (
  SELECT job_id
  FROM job_queue
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE job_queue
SET status = 'processing',
    started_at = CURRENT_TIMESTAMP
WHERE job_id IN (SELECT job_id FROM batch)
RETURNING *;

Snapshot Isolation

-- PostgreSQL uses MVCC (Multi-Version Concurrency Control)
-- Readers don't block writers, writers don't block readers

-- Check transaction ID
SELECT txid_current();

-- See row versions
SELECT
  xmin,
  xmax,
  *
FROM accounts
WHERE account_id = 123;

Serializable Anomaly Detection

-- PostgreSQL serializable anomaly detection
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
-- This transaction may fail with serialization_error
SELECT SUM(balance) FROM accounts;
-- ... another transaction modifies data ...
UPDATE accounts SET balance = balance * 1.01;
COMMIT;  -- May fail with serialization failure

Explicit Table Locking

-- Lock entire table
LOCK TABLE accounts IN EXCLUSIVE MODE;

-- Lock for specific operations
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;  -- DDL operations
LOCK TABLE accounts IN SHARE UPDATE EXCLUSIVE MODE;  -- VACUUM
LOCK TABLE accounts IN SHARE ROW EXCLUSIVE MODE;  -- DML

Long-Running Transaction Monitoring

-- Find long-running transactions
SELECT
  pid,
  usename,
  state,
  query,
  query_start,
  NOW() - query_start AS duration,
  backend_start
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < NOW() - INTERVAL '5 minutes'
ORDER BY duration DESC;

-- Kill long-running query
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE query_start < NOW() - INTERVAL '1 hour'
  AND state = 'active';

Connection Pool Configuration

-- PostgreSQL connection settings
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET superuser_reserved_connections = 3;
ALTER SYSTEM SET password_encryption = 'scram-sha-256';

-- Reload configuration
SELECT pg_reload_conf();

Replication Lag Monitoring

-- Check replication status
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  replay_lsn,
  replay_lag
FROM pg_stat_replication;

-- Check replication slot lag
SELECT
  slot_name,
  active,
  restart_lsn,
  confirmed_flush_lsn
FROM pg_replication_slots;

Write-Ahead Logging (WAL)

-- Check WAL settings
SHOW wal_level;
SHOW max_wal_size;
SHOW checkpoint_timeout;

-- Force WAL checkpoint
CHECKPOINT;

-- Monitor WAL generation
SELECT
  pg_current_wal_lsn() AS current_lsn,
  pg_current_wal_insert_lsn() AS insert_lsn;

Follow-Up Questions

1. When would you use SERIALIZABLE over READ COMMITTED?
2. How do you handle serialization failures in application code?
3. Explain the difference between pessimistic and optimistic locking.
4. How do you prevent deadlocks in a distributed system?
5. What's the impact of MVCC on storage and performance?
6. How do you monitor and tune PostgreSQL replication lag?