Postgres: atomicidade e bloqueio — quem espera quem, e quando

Postgres: atomicidade e bloqueio — quem espera quem, e quando#

Como o Postgres garante o "tudo ou nada" de uma transação — descendo até o xid, o xmin/xmax da tupla e os bits de commit no pg_xact — e o que acontece, linha do tempo por linha do tempo, quando duas transações (ou uma migration) disputam a tabela messages.

Todos os exemplos usam a tabela messages deste projeto e podem ser reproduzidos no Postgres do docker-compose.yml com dois terminais psql.

1. Atomicidade: a transação é uma unidade indivisível#

Tudo entre BEGIN e COMMIT ou vira permanente de uma vez, ou desaparece de uma vez. Não existe estado intermediário visível para ninguém de fora. Se qualquer comando falha no meio, o ROLLBACK desfaz tudo — inclusive o que já tinha "dado certo" dentro da transação.

CAMINHO A — TUDO
BEGIN ─► INSERT msg #1 ─► INSERT msg #2 ─► INSERT msg #3 ─► COMMIT
                                                            └─ ✓ as 3 mensagens ficam
                                                               visíveis juntas, no
                                                               mesmo instante
 
CAMINHO B — NADA
BEGIN ─► INSERT msg #1 ─► INSERT msg #2 ─► INSERT msg #3 ─► ROLLBACK
                                           └─ falha         └─ ✗ msgs #1 e #2 também
                                              (constraint)     somem — nunca existiram
                                                               para o mundo

É exatamente isso que protege o batch-insert deste app: um lote de 500 mensagens num INSERT ... VALUES (...), (...), ... entra inteiro ou não entra — nunca "metade do lote".

2. Dicionário: cada termo sozinho, antes de qualquer fluxo#

Antes do fluxo, o aviso que evita a confusão mais comum: transação ≠ versão. Transação é uma sessão executando comandos — ela ganha um xid. Versão é uma cópia física da linha no disco. A linha id = 42 é uma só do ponto de vista lógico, mas durante um UPDATE ela existe em duas cópias físicas ao mesmo tempo.

Detalhe sobre xmin/xmax: são colunas de sistema ocultas — não aparecem no SELECT *, mas você pode pedi-las pelo nome em qualquer tabela (junto com ctid, cmin, cmax e tableoid; cmin e cmax compartilham um único campo físico no cabeçalho, o t_cid). Não existe coluna chamada xid: a linha não tem "um xid", ela tem dois campos que referenciam xids de transações.

3. O filme: UPDATE em id = 42, com as queries reais de cada terminal#

Estado inicial — como a linha 42 está no disco#

SELECT xmin, xmax, ctid, id, status FROM messages WHERE id = 42;
 
--  xmin | xmax | ctid  | id | status
-- ------+------+-------+----+--------
--   640 |    0 | (0,1) | 42 | sent

Como ler: existe uma única cópia física da linha 42, criada pela transação de xid 640 (que já comitou). xmax = 0 significa "nenhuma transação me marcou para substituição". ctid (0,1) é o endereço físico: página 0, posição 1.

Passo 1 — Terminal A abre a transação e faz o UPDATE#

BEGIN;
SELECT pg_current_xact_id();  -- → 812  (o xid que A acabou de receber)
UPDATE messages SET status = 'read' WHERE id = 42;  -- UPDATE 1 (responde na hora)

O que mudou. No pg_xact: nasce a entrada 812 → in_progress, e a transação de A tranca o lock do próprio xid. No disco, três escritas — nenhuma remoção:

o disco agora tem DUAS cópias físicas da mesma linha 42:
 
 cópia   | xmin | xmax | ctid  | status | significado
---------+------+------+-------+--------+---------------------------------------------
 antiga  |  640 |  812 | (0,2)*| sent   | ganhou o carimbo "812 quer me substituir"
 nova    |  812 |    0 | (0,2) | read   | criada agora; só vale se 812 comitar
 
 * o ctid da cópia antiga foi atualizado para apontar para a nova — a corrente antiga→nova

Por quê. Nada foi destruído porque a transação 812 ainda pode falhar. Se o Postgres editasse a cópia no lugar, um rollback precisaria reescrever o valor antigo de volta (undo log, o jeito Oracle). Do jeito Postgres, desistir é grátis: basta declarar o xid 812 morto e os carimbos dele deixam de valer.

[!NOTE] Carimbar xmax é uma mutação in-place — mas só no cabeçalho. Os dados (status='sent') ficam intactos, então leitores concorrentes nunca veem conteúdo pela metade; e o carimbo é reversível de graça, porque o significado dele mora no pg_xact, não na tupla.

Passo 2 — o que cada terminal enxerga AGORA (A ainda não comitou)#

SELECT xmin, xmax, status FROM messages WHERE id = 42;
--  xmin | xmax | status
--   812 |    0 | read      ← A vê a própria escrita: a cópia nova

SELECT xmin, xmax, status FROM messages WHERE id = 42;
--  xmin | xmax | status
--   640 |  812 | sent      ← responde NA HORA, sem travar

Reação. Repare no resultado de B: ele devolve o dado antigo (sent), mas o xmax = 812 já aparece — B enxerga literalmente o carimbo que a transação de A deixou. B consultou o pg_xact: "812 está em andamento" → o carimbo de morte ainda não vale → entrega a cópia antiga. A cópia nova também está no disco, mas é invisível para B, porque seu xmin = 812 não comitou.

Por quê zero espera. Para responder, B só comparou inteiros e consultou o pg_xact. Em nenhum momento tocou no lock manager. Leitor não disputa nada com escritor — é daqui que sai o MVCC da seção 5.

Passo 3 — Terminal B tenta ESCREVER na mesma linha#

UPDATE messages SET status = 'archived' WHERE id = 42;
-- ...trava aqui: sem erro, sem resposta, cursor preso...

SELECT locktype, transactionid, granted FROM pg_locks WHERE granted = false;
 
--    locktype    | transactionid | granted
--  transactionid |           812 | f        ← B espera a TRANSAÇÃO 812 terminar

Reação. B encontrou a cópia antiga com xmax = 812 em andamento — a mesma coisa que o leitor viu. Mas escrever exige saber o desfecho: se 812 comitar, a cópia válida será a nova; se abortar, será a antiga. Como o futuro de 812 é indecidível agora, B dorme esperando o lock do xid 812 — veja na saída do pg_locks: ele não espera "a linha 42", espera a transação 812.

Por quê dormir e não dar erro. No isolamento padrão (READ COMMITTED) o Postgres enfileira escritas concorrentes em vez de rejeitá-las. E não há polling — o lock manager acorda B no instante em que 812 soltar o lock.

Passo 4 — Terminal A comita#

COMMIT;
-- no TERMINAL B, no mesmo instante, sozinho:
-- UPDATE 1        ← o comando preso do passo 3 destravou e completou

O que mudou (com synchronous_commit = on, o padrão — e em ordem):

1. O registro de commit é gravado e persistido no WAL — durabilidade.
2. Os 2 bits viram: pg_xact[812] = committed — o veredito durável que as checagens de tupla vão consultar dali em diante.
3. A transação sai da lista compartilhada de transações em andamento (a ProcArray) — e este é o interruptor atômico da visibilidade: a criação de snapshots é serializada contra essa saída, então todo snapshot novo tirado depois dela enxerga todas as linhas do 812 de uma vez.

Nenhuma das duas cópias da linha foi tocada — ficaram byte a byte como estavam. Por fim, A solta o lock do xid 812.

[!NOTE] Existe até uma janela minúscula entre os passos 2 e 3 em que o pg_xact já diz "committed" mas os leitores ainda tratam a 812 como em andamento — a ordem das checagens de visibilidade no código-fonte existe exatamente por causa dela. E a visibilidade é sempre por snapshot: uma transação REPEATABLE READ que tirou seu snapshot antes do commit continua sem ver nada, por design.

Reação. Em cadeia: leitores novos refazem a aritmética do passo 2 com os mesmos números, mas agora 812 = committed inverte as duas respostas — a cópia antiga está oficialmente morta, a nova oficialmente viva. E B acorda: segue o ctid da cópia antiga até a nova, reavalia o WHERE id = 42 sobre ela (o mecanismo se chama EvalPlanQual), ainda casa, e aplica seu UPDATE — criando uma terceira cópia. Depois do commit de B (xid 813):

SELECT xmin, xmax, status FROM messages WHERE id = 42;
--  xmin | xmax | status
--   813 |    0 | archived   -- o 'archived' de B foi aplicado SOBRE o 'read' de A

Por quê isso é a atomicidade. Se a transação 812 tivesse escrito 500 mensagens, seriam 500 cópias novas, todas com xmin = 812. Um único commit muda o veredito das 500 simultaneamente — custo O(1), não O(linhas escritas).

[!WARNING] Em REPEATABLE READ, o passo "B acorda e reaplica" não existe: B falha com ERROR: could not serialize access due to concurrent update, e a aplicação precisa refazer a transação.

Passo 5 — universo alternativo: e se A desse ROLLBACK (ou caísse a conexão)?#

ROLLBACK;   -- queda de conexão tem o mesmo efeito

O que mudou. Só isto: pg_xact[812] = aborted, e o lock do xid é solto. De novo, nenhuma cópia da linha é tocada.

Reação. O carimbo xmax = 812 na cópia antiga fica anulado (o autor abortou), então ela segue sendo a versão válida. A cópia nova (xmin = 812) vira lixo invisível para sempre — o VACUUM a recolhe depois. B destrava do mesmo jeito, vê que 812 falhou e aplica o UPDATE sobre a cópia antiga: o archived final é construído a partir do sent; o read de A nunca existiu.

Por quê. Commit e rollback custam o mesmo — atualizar o status do xid. O "desfazer tudo" da atomicidade não desfaz nada; ele só nunca oficializa. Por isso rollback é instantâneo mesmo numa transação que escreveu milhões de linhas.

4. MVCC: por isso leitura nunca fica presa em escrita#

Com xmin/xmax no lugar, a leitura vira pura aritmética de visibilidade — sem lock nenhum. Cada query usa seu snapshot e aplica a regra: a tupla é visível se xmin está commitado e fora do meu snapshot, e xmax é 0, abortado, ou ainda em andamento.

Quem bloqueia quem, na mesma linha:

INSERTs de linhas novas (o caso do buffer de mensagens deste app) não disputam linha com ninguém — por isso lotes concorrentes escalam bem. As exceções: checagens de constraint UNIQUE ou de exclusão (e o caminho especulativo do INSERT ... ON CONFLICT), em que o segundo inserter espera o xid do primeiro.

5. A fila do lock: ordem de chegada (aproximada)#

Se três transações querem escrever na mesma linha, forma-se uma fila. Cada uma acorda quando a anterior termina — e antes de aplicar o UPDATE, reavalia o WHERE sobre a versão mais nova da linha.

        ┌────────────────┐      espera       espera
        │  linha id = 42 │ ◄─── [ T2 ] ◄─── [ T3 ]
        │  🔒 lock: T1   │     1ª da fila   2ª da fila
        └────────────────┘

Precisão importa aqui: a arbitragem é feita por um lock pesado de tupla que garante ausência de inanição (starvation) e atende em ordem de chegada aproximada — FIFO estrito não é garantido (por exemplo, uma sessão que já segura locks conflitantes pode ser inserida à frente na fila para evitar deadlock).

Para inspecionar ao vivo quem está parado esperando lock:

SELECT pid, wait_event_type, state, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

6. Deadlock: quando a espera vira um ciclo#

Se T1 segura a linha A e quer a B, enquanto T2 segura a B e quer a A, ninguém nunca acorda:

   T1 (segura 🔒 linha A) ──── espera linha B ───► T2 (segura 🔒 linha B)
        ▲                                                 │
        └──────────────── espera linha A ─────────────────┘

Quem está esperando roda uma checagem de ciclo após deadlock_timeout (padrão 1s). Num deadlock "duro", o primeiro a rodar a checagem e encontrar o ciclo normalmente aborta a si mesmo com ERROR: deadlock detected — mas a documentação avisa que a escolha da vítima não deve ser assumida como previsível. (Deadlocks "moles", resolvíveis reordenando a fila, são desfeitos sem abortar ninguém; e se o bloqueador for um autovacuum, ele é cancelado no lugar.) A vítima sofre rollback completo — a atomicidade garante que ela é desfeita por inteiro.

Prevenção clássica: todas as transações tocam as linhas na mesma ordem (ex.: sempre por id crescente) — sem ordem cruzada, não há ciclo possível.

7. Migrations: quando falha, e quando espera#

No Postgres, DDL é transacional — ALTER TABLE, CREATE INDEX, DROP obedecem à mesma atomicidade dos dados (raro entre bancos; MySQL não faz isso). O catálogo (pg_class, pg_attribute) são tabelas MVCC comuns: a atomicidade do DDL é a MESMA máquina de xmin/xmax da seção 3.

Caso 1 — a migration falha no meio#

BEGIN;
ALTER TABLE messages ADD COLUMN read_at timestamptz;
CREATE INDEX messages_read_at_idx ON messages (read_at);
UPDATE messages SET read_at = now() WHERE status = 'read';  -- ✗ falha aqui
ROLLBACK;  -- a coluna E o índice desaparecem do catálogo junto

Exceções notáveis que não podem rodar dentro de transação (a lista não é exaustiva): CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VACUUM, ALTER SYSTEM, CREATE/DROP DATABASE, CREATE/DROP TABLESPACE. Se um CREATE INDEX CONCURRENTLY falha no meio, ele deixa para trás um índice marcado INVALID — ignorado pelas queries, mas ainda pagando overhead a cada escrita. Não há rollback: recupere dropando-o e tentando de novo (o recomendado), ou com REINDEX INDEX CONCURRENTLY.

[!WARNING] Prisma Migrate (usado neste projeto) NÃO envolve a migration numa transação no Postgres. Se uma migration falha no meio, ela pode ficar meio-aplicada: fica registrada como falha na tabela _prisma_migrations, bloqueia os próximos migrate deploy (erro P3009), e você reconcilia manualmente com prisma migrate resolve --applied <nome> (depois de completar na mão) ou --rolled-back <nome> (depois de reverter na mão). Para ter o tudo-ou-nada de verdade, adicione BEGIN;/COMMIT; explícitos no SQL gerado da migration.

Caso 2 — a migration espera: o engarrafamento do ACCESS EXCLUSIVE#

relatório  ─►  [ SELECT longo em messages (analytics, 90s) ── ACCESS SHARE ]──── fim
                                                                                  │
migration  ─►      [ ALTER TABLE messages ... ══ ESPERANDO ═════════════ ]──[ executa em ms ]─ COMMIT
                     quer ACCESS EXCLUSIVE: conflita até com ACCESS SHARE         │
                                                                                  │
seu app    ─►          [ TODOS os SELECT/INSERT novos ══ NA FILA ════════ ]──[ app volta ao normal ]
                         compatíveis com o relatório, mas atrás do ALTER

O perigo real: a migration nem executou nada ainda — só o pedido de ACCESS EXCLUSIVE já derruba o app, porque pedidos novos de outras sessões entram na fila atrás do ALTER que espera, mesmo sendo compatíveis com o lock atual (senão o ALTER nunca conseguiria entrar; a principal exceção é uma sessão que já segura lock na tabela).

A defesa padrão em produção — falhar rápido em vez de represar o app:

SET lock_timeout = '2s';
ALTER TABLE messages ADD COLUMN read_at timestamptz;
-- se não pegar o lock em 2s:
-- ERROR: canceling statement due to lock timeout  (rollback atômico)
-- → tenta de novo, em vez de segurar o app refém de um relatório de 90s

Locks de tabela por comando (os que importam):

* A maioria das formas. Algumas pegam locks mais fracos: VALIDATE CONSTRAINT, SET (parâmetros de storage) e ATTACH PARTITION pegam SHARE UPDATE EXCLUSIVE; ADD FOREIGN KEY pega SHARE ROW EXCLUSIVE (nas duas tabelas). E desde o PostgreSQL 11, ADD COLUMN ... DEFAULT <valor não-volátil> é só metadado, sem reescrever a tabela (sem default já era rápido antes); um DEFAULT volátil como clock_timestamp(), coluna gerada ou identity ainda força reescrita completa.

8. Veja com seus próprios olhos#

O UPDATE cria mesmo uma cópia nova#

SELECT xmin, xmax, id FROM messages WHERE id = 1;  -- anote o xmin
UPDATE messages SET status = status WHERE id = 1;  -- update "inócuo"
SELECT xmin, xmax, id FROM messages WHERE id = 1;  -- xmin MUDOU!

Listando as cópias mortas (que o SELECT esconde)#

Por SQL normal não dá — a regra de visibilidade filtra as cópias mortas antes de chegar em você. Mas a extensão pageinspect (requer superuser; extensões são por banco) abre a página crua:

CREATE EXTENSION IF NOT EXISTS pageinspect;
 
SELECT lp AS posicao, t_xmin, t_xmax, t_ctid AS aponta_para
FROM heap_page_items(get_raw_page('messages', 0));
 
--  posicao | t_xmin | t_xmax | aponta_para
-- ---------+--------+--------+-------------
--        3 |    640 |    813 | (0,4)   ← cópia antiga: morta pelo 813, aponta pra sucessora
--        4 |    813 |      0 | (0,4)   ← cópia nova: viva

Avisos práticos:

• As cópias mortas somem rápido: o autovacuum e o HOT prune (mini-limpeza que qualquer query pode disparar ao passar pela página) as reciclam. Para a demo: ALTER TABLE messages SET (autovacuum_enabled = false); — e reative depois. Detalhe: isso desliga o autovacuum de rotina, mas o anti-wraparound ainda roda.
• Para só contar sem abrir página: SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'messages'; (n_dead_tup é uma estimativa do coletor de estatísticas, não uma contagem exata).
• Rode VACUUM messages; e inspecione de novo: as posições das cópias mortas aparecem vazias — é o lixo do MVCC sendo recolhido.

O xid tem 32 bits — e isso cobra um preço#

O contador de xid dá a volta (~4 bilhões de valores, horizonte útil de ~2 bilhões). O VACUUM periodicamente "congela" tuplas antigas (marca um bit de frozen no cabeçalho) para o wraparound não corromper a visibilidade — é por isso que autovacuum nunca deve ficar desligado de verdade.

E os hint bits: quando um leitor consulta o pg_xact e descobre "640 comitou", ele grava um bit na própria tupla ("xmin confirmado") para os próximos leitores nem consultarem o pg_xact. Sim — um SELECT pode escrever fisicamente na página. É só memoização de um fato imutável.

Resumo#

Atomicidade: o veredito de uma transação inteira mora em um único lugar (o status do seu xid), então commit e rollback são O(1) — nenhuma tupla é tocada; o interruptor final da visibilidade é a saída da transação da lista global de transações em andamento, contra a qual todo snapshot novo é serializado. Bloqueio: escrita espera escrita na mesma linha, dormindo no lock do xid de quem chegou primeiro, até o commit/rollback dele — leituras nunca entram nessa fila, graças ao MVCC. E em migrations, é o mundo inteiro que pode esperar um ACCESS EXCLUSIVE — por isso lock_timeout em todo DDL de produção, e atenção: o Prisma Migrate não te dá o tudo-ou-nada de graça.