Postgres Profundo: MVCC, Isolation Levels e Locks

-- Por transação:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 42;
UPDATE accounts SET balance = balance - 50 WHERE id = 42;
COMMIT;

-- Setar default da sessão:
SET default_transaction_isolation = 'repeatable read';

-- Setar como default do banco (não recomendado — caso a caso):
ALTER DATABASE mydb SET default_transaction_isolation = 'read committed';

-- Cenário: 2 usuários editando perfil do user 42 (balance inicial: 100)

-- Tx1 e Tx2 ambas em Read Committed (default)

-- Tx1:
BEGIN;
SELECT balance FROM accounts WHERE id = 42;   -- lê 100
-- ... app calcula novo valor: 100 + 50 = 150

-- Tx2:
BEGIN;
SELECT balance FROM accounts WHERE id = 42;   -- lê 100
-- ... app calcula novo valor: 100 + 30 = 130

-- Tx1:
UPDATE accounts SET balance = 150 WHERE id = 42;
COMMIT;                                        -- OK

-- Tx2:
UPDATE accounts SET balance = 130 WHERE id = 42;   -- sobrescreve!
COMMIT;                                        -- Tx1 foi PERDIDA. Balance: 130 (deveria ser 180).

-- Solução 1: SELECT FOR UPDATE (pessimistic lock)
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;   -- bloqueia a linha
UPDATE accounts SET balance = balance + 50 WHERE id = 42;
COMMIT;
-- Tx2 trava no SELECT FOR UPDATE até Tx1 commitar; depois lê 150 e segue.

-- Solução 2: UPDATE atômico (não precisa de lock se a lógica cabe em SQL)
UPDATE accounts SET balance = balance + 50 WHERE id = 42;
-- Postgres executa atomicamente. Sem race, sem lock manual.

-- Solução 3: Optimistic lock (version column)
-- schema: ALTER TABLE accounts ADD COLUMN version INT DEFAULT 0;
BEGIN;
SELECT balance, version FROM accounts WHERE id = 42;   -- lê (100, 3)
UPDATE accounts
  SET balance = 150, version = version + 1
  WHERE id = 42 AND version = 3;                       -- CAS
-- Se rowcount = 0, alguém mudou antes → erro pro cliente / retry.
COMMIT;

-- Exclusivo, session-level (dura até sessão terminar ou unlock explícito)
SELECT pg_advisory_lock(12345);
-- ... faz algo crítico ...
SELECT pg_advisory_unlock(12345);

-- Exclusivo, transaction-level (libera no COMMIT/ROLLBACK — preferido)
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- ... faz algo crítico ...
COMMIT;  -- lock liberado automaticamente

-- Non-blocking (try)
SELECT pg_try_advisory_xact_lock(12345);
-- retorna true se pegou, false se já tá preso — pra padrões "rodar 1 de N"

-- Ver estatísticas de bloat (approximação)
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Tunar autovacuum pra tabela hot específica:
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,    -- 2% de dead → roda (default 20%)
  autovacuum_vacuum_cost_limit = 2000       -- mais IO permitido (default 200)
);

-- Forçar VACUUM imediato (sem ACCESS EXCLUSIVE):
VACUUM (VERBOSE, ANALYZE) orders;

-- 1. Ver queries rodando agora
SELECT pid, usename, application_name, state, wait_event_type, wait_event,
       now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 2. Ver locks e quem espera quem
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
     ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
     ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
     ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 3. Matar query (com cuidado):
SELECT pg_cancel_backend(pid);     -- pede educadamente
SELECT pg_terminate_backend(pid);  -- força — aborta tx

-- 4. Ver tamanho real das tabelas + bloat:
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS "table only"
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;