MVCC e Snapshot Isolation: PostgreSQL vs. MySQL InnoDB sob o Capô

Bancos relacionais modernos evitam travas de leitura via MVCC. A arquitetura divide-se entre o heap do PostgreSQL e os undo logs do MySQL InnoDB.

Essa escolha estrutural dita a vazão de gravação, o consumo de memória e a integridade sob alta concorrência.

Entender essa física interna evita gargalos severos de throughput e anomalias transacionais silenciosas em produção.

Como o MVCC supera os bloqueios de leitura do Two-Phase Locking?

O controle de concorrência multiversão elimina a contenção do modelo clássico Two-Phase Locking (2PL) mantendo múltiplas versões simultâneas de cada registro no armazenamento. Sob MVCC, leituras acessam fotografias consistentes do passado sem travas compartilhadas. Mutações intensas e relatórios analíticos ocorrem em paralelo sem bloqueio mútuo.

No paradigma clássico de Two-Phase Locking estrito, leituras exigem travas compartilhadas (S-locks). As escritas demandam travas exclusivas (X-locks).

Sob alta concorrência, esse mecanismo colapsa a vazão global. Consultas analíticas demoradas paralisam mutações operacionais nas mesmas tabelas.

O padrão ANSI SQL-92 formalizou três anomalias clássicas: leitura suja (Dirty Read), leitura não repetível (Non-repeatable Read) e leitura fantasma (Phantom Read).

Contudo, a pesquisa de Berenson et al. (SIGMOD 1995) provou que o padrão ANSI era incompleto.

O estudo provou que o isolamento por instantâneo (Snapshot Isolation) previne todas as anomalias do nível Repeatable Read sem bloqueios de leitura.

Ao iniciar, a transação obtém uma visão pontual do banco baseada em um timestamp lógico.

Qualquer alteração posterior efetuada por outras transações permanece invisível para aquela execução.

MODELO 2PL CLÁSSICO (BLOQUEIOS PESSIMISTAS):
Transação A (Read)  ---- [Adquire S-Lock] ---- Lê Dados ---- [Libera Lock]
Transação B (Write) ------------------------- [Bloqueada aguardando S-Lock...]

MODELO MVCC MODERNO (DESACOPLAMENTO MULTIVERSÃO):
Transação A (Read)  ---- Lê Versão Snapshot T0 ------------------------> Sucesso
Transação B (Write) ---- Grava Nova Versão T1 em Paralelo -------------> Sucesso

Como o PostgreSQL implementa tuplas no heap e cabeçalhos de visibilidade?

O PostgreSQL implementa o MVCC armazenando todas as versões de linha diretamente nas páginas de dados do heap comum. Atualizações nunca alteram uma linha no local original. Cada UPDATE insere uma nova tupla e preenche cabeçalhos na versão anterior indicando sua expiração, gerando tuplas mortas.

No PostgreSQL Storage Page Layout, cada tupla aloca 24 bytes de cabeçalho (HeapTupleHeaderData).

Os campos fundamentais desse cabeçalho governam a visibilidade de cada registro no disco:

• t_xmin: armazena o identificador da transação (XID) que criou a tupla física.
• t_xmax: armazena o identificador da transação que atualizou ou excluiu o registro. Contém zero se a linha estiver ativa.
• t_cid: representa o identificador de comando interno (Command ID) para controlar a visibilidade dentro da mesma transação.
• t_ctid: armazena o ponteiro físico para o bloco e slot atual da tupla ou para sua versão sucessora.
• t_infomask: conjunto de bits que grava flags de estado, como HEAP_XMIN_COMMITTED e HEAP_XMAX_INVALID.

Quando uma transação executa uma consulta, o PostgreSQL compara o t_xmin e o t_xmax da tupla contra o instantâneo ativo (Active Snapshot).

Se o t_xmin não foi confirmado ou pertence a uma transação posterior ao snapshot, a linha é ignorada.

Se o t_xmax pertencer a uma transação já confirmada anterior ao snapshot, a linha é tratada como morta.

Essa mecânica impõe um desafio severo para os índices B-Tree secundários.

Como a nova tupla ganha um novo endereço físico (ItemPointer), todos os índices da tabela precisariam ser atualizados.

Para mitigar esse custo, o PostgreSQL adota a otimização HOT (Heap-Only Tuples).

Se a nova versão couber na mesma página de 8 KB sem alterar colunas indexadas, o motor cria uma cadeia interna (LP_REDIRECT).

O índice continua apontando para o slot original, eliminando escritas redundantes em índices secundários.

ATUALIZAÇÃO COM OTIMIZAÇÃO HOT (HEAP-ONLY TUPLES):
Página Heap (8 KB)
[Slot 1: LP_REDIRECT -> Slot 2]  <=== Índice B-Tree aponta apenas aqui
[Slot 2: Tupla Antiga (t_xmax = 101, ctid = (0,3))]
[Slot 3: Tupla Nova   (t_xmin = 101, t_xmax = 0)]

Por que o VACUUM e o risco de Transaction ID Wraparound são vitais no PostgreSQL?

O processo de VACUUM é mandatório no PostgreSQL para recuperar o espaço físico ocupado por tuplas mortas e prevenir a corrupção de Transaction ID Wraparound. Sem a limpeza contínua, o disco sofre inchaço e o contador modular de 32 bits pode interpretar dados históricos como transações futuras, ocultando registros.

Como o PostgreSQL não remove tuplas antigas durante comandos de DELETE ou UPDATE, o espaço em disco permanece ocupado até o Autovacuum agir.

O Autovacuum varre as páginas e remove tuplas cujo t_xmax seja mais antigo que a transação ativa mais velha do cluster.

Os identificadores de transação no PostgreSQL possuem tamanho fixo de 32 bits.

Essa restrição impõe um teto de 2^32 ≈ 4,29 bilhões de transações possíveis.

Para viabilizar comparações de visibilidade infinitas, o motor utiliza aritmética modular circular.

Uma transação T2 é considerada posterior a T1 se a expressão (T2 - T1) < 2^31 for verdadeira.

Com essa regra matemática, metade dos números reside no passado e a outra metade no futuro.

Se um banco ultrapassar 2 bilhões de transações sem congelamento, transações antigas passam a ser avaliadas como futuras.

Quando essa anomalia ocorre, o banco deixa de enxergar os dados armazenados.

Para neutralizar esse risco, o Autovacuum executa a rotina periódica de VACUUM FREEZE.

Ao congelar uma tupla, o motor ativa o bit especial HEAP_XMIN_FROZEN na máscara t_infomask.

Esse bit sinaliza que a tupla é mais antiga que qualquer transação ativa, blindando o banco contra o esgotamento.

Adicionalmente, o VACUUM atualiza dois mapas de suporte essenciais:

1. Free Space Map (FSM): mapeia a quantidade de bytes livres em cada página para acelerar novas inserções sem inflar o arquivo.
2. Visibility Map (VM): registra páginas onde todas as tuplas já estão confirmadas, permitindo que consultas usem Index-Only Scans.

Como o MySQL InnoDB organiza registros em B+Tree clusterizada e reconstrói versões via Undo Log?

O MySQL InnoDB organiza tabelas por índice clusterizado em torno da chave primária, executando atualizações na própria página sempre que o espaço permitir. As versões históricas anteriores são removidas da tabela principal e salvas como deltas compactos em segmentos de reversão chamados undo logs, preservando os índices secundários.

Conforme o Manual de Referência do MySQL 8.4 sobre InnoDB Multi-Versioning, cada registro embute dois campos ocultos obrigatórios:

• DB_TRX_ID (6 bytes): identifica a última transação que inseriu ou modificou o registro.
• DB_ROLL_PTR (7 bytes): armazena um ponteiro para o registro de undo log correspondente no Rollback Segment.

Quando uma transação altera uma linha no InnoDB, o motor executa etapas coordenadas:

Primeiro, grava uma entrada de undo contendo os valores originais das colunas modificadas.

Em seguida, altera o registro diretamente na página do índice clusterizado e atualiza o DB_ROLL_PTR para apontar para o novo delta.

Se uma consulta sob Repeatable Read requisitar a linha, o motor avalia sua estrutura Read View.

A Read View contém a lista de transações ativas na abertura da leitura (m_ids), o menor ID ativo (m_up_limit_id) e o próximo ID disponível (m_low_limit_id).

Se o DB_TRX_ID do registro for mais recente que o limite visível, o InnoDB não lê dados do heap.

Ele segue o ponteiro DB_ROLL_PTR e aplica os deltas do undo log em ordem reversa na memória até reconstruir a versão correta.

CADEIA DE VERSÕES DO INNODB (DELTA EM UNDO LOG):
Página B+Tree Clustered:
[Registro Atual: Saldo = R$ 500 | DB_TRX_ID = 205 | DB_ROLL_PTR ----+ ]
                                                                     |
Segmento de Undo Log:                                                |
[Delta 1: Saldo = R$ 300 | DB_TRX_ID = 180 | DB_ROLL_PTR ----+ ]  <--+
                                                             |
[Delta 0: Saldo = R$ 100 | DB_TRX_ID = 110 | DB_ROLL_PTR = NULL] <--+

A limpeza dos undo logs não depende de reconstrução no arquivo de dados.

Threads dedicadas de Purge removem páginas de undo assim que a transação ativa mais antiga do sistema encerra sua execução.

O risco operacional crítico no InnoDB é o acúmulo de History List Length (HLL).

Transações analíticas abertas por horas impedem o descarte de undo logs de todo o cluster.

Essa retenção infla o armazenamento e degrada consultas posteriores, que precisam percorrer longas cadeias de deltas.

O que é Write Skew e como o Serializable Snapshot Isolation (SSI) resolve a anomalia?

O Write Skew é uma anomalia de concorrência onde duas transações simultâneas leem dados sobrepostos e realizam alterações em registros distintos que violam uma restrição global. O nível de isolamento Snapshot Isolation não detecta esse conflito porque nenhuma das transações modifica diretamente o registro que a outra alterou.

Considere o caso clássico da escala de plantão médico em um hospital.

A regra de negócio exige que sempre exista pelo menos um médico ativo no plantão.

Suponha que dois médicos, Alice e Roberto, estejam escalados simultaneamente.

Ambos decidem solicitar dispensa no mesmo instante através de transações paralelas:

ESTADO INICIAL: Médicos de plantão = Alice (ativo), Roberto (ativo). Restrição: Total >= 1.

T1 (Alice):   SELECT count(*) FROM escala WHERE ativo = true; -- Retorna 2.
T2 (Roberto): SELECT count(*) FROM escala WHERE ativo = true; -- Retorna 2.

T1 (Alice):   UPDATE escala SET ativo = false WHERE medico = 'Alice'; -- Válido, leu 2.
T2 (Roberto): UPDATE escala SET ativo = false WHERE medico = 'Roberto'; -- Válido, leu 2.

T1: COMMIT;
T2: COMMIT;

ESTADO FINAL: Alice = inativo, Roberto = inativo. TOTAL = 0 MÉDICOS (VIOLAÇÃO GRAVE).

Sob Repeatable Read no MySQL InnoDB ou PostgreSQL padrão, ambas as transações concluem com sucesso.

Como T1 modificou apenas a linha da Alice e T2 alterou apenas o registro do Roberto, inexiste conflito de chave.

Para prevenir essa falha no InnoDB, desenvolvedores recorrem a bloqueios manuais com SELECT ... FOR UPDATE.

No PostgreSQL, a solução arquitetural recomendada é o nível de isolamento SERIALIZABLE.

O PostgreSQL implementa o algoritmo de Serializable Snapshot Isolation (SSI) formulado por Cahill et al. (SIGMOD 2008).

Em vez de travar leituras, o motor monitora dependências de leitura e escrita (rw-antidependencies) por meio de estruturas leves na memória chamadas SIREAD locks.

O SSI constrói um grafo dinâmico de dependências de serialização.

Quando detecta dois arcos consecutivos de conflito (dangerous structure T1 --rw--> T_pivot --rw--> T3), o motor identifica risco iminente de ciclo.

Nesse instante, o PostgreSQL aborta deterministicamente uma das transações com o erro 40001 (serialization_failure).

A aplicação receptora recebe o sinal claro para repetir a operação, mantendo a consistência sem travar o banco.

Comparativo arquitetural: PostgreSQL vs. MySQL InnoDB sob o capô

A tabela comparativa a seguir sintetiza as principais divergências de engenharia física entre os motores de armazenamento do PostgreSQL e do MySQL InnoDB:

Essa comparação evidencia que não existe motor universalmente superior para todos os cenários.

Sistemas com taxas elevadas de atualização em tabelas com múltiplos índices secundários tendem a sofrer write amplification acentuada no PostgreSQL.

Por outro lado, aplicações analíticas complexas e sistemas que exigem consistência matemática estrita sem deadlocks se beneficiam do SSI nativo do PostgreSQL.

Como runtimes de IA e agentes autônomos utilizam o MVCC para orquestração resiliente?

Agentes de código e runtimes de IA usam MVCC para salvar checkpoints, coordenar sub-agentes e processar filas sem contenção. Ambientes modernos de automação demandam gravação concorrente veloz e garantias estritas de idempotência transacional.

No desenvolvimento de agentes de código, como a infraestrutura do MaxVision Code, múltiplos executores concorrentes manipulam árvores sintáticas e ferramentas MCP simultaneamente.

Para evitar que dezenas de agentes disputem as mesmas tarefas em tabelas de despacho, aplica-se o padrão FOR UPDATE SKIP LOCKED.

-- Consumo concorrente atômico sem contenção entre múltiplos agentes de IA
WITH proxima_tarefa AS (
  SELECT id FROM fila_execucao_agentes
  WHERE status = 'pendente'
  ORDER BY prioridade DESC, criado_em ASC
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE fila_execucao_agentes
SET status = 'executando', iniciado_em = clock_timestamp()
WHERE id = (SELECT id FROM proxima_tarefa)
RETURNING id, payload_execucao;

Essa consulta seleciona e bloqueia a primeira tarefa livre sem enfileirar outros agentes em espera forçada.

Graças ao MVCC, outros executores saltam os registros travados e operam instantaneamente sobre as próximas linhas da fila.

Outro ajuste mandatório em cargas de alta rotatividade de agentes é a configuração deliberada de fillfactor no PostgreSQL.

Por padrão, páginas de dados aceitam preenchimento até 100% de sua capacidade.

Ao reduzir o fillfactor para 75% em tabelas operacionais, reserva-se 25% de cada página de 8 KB para novas versões.

Essa folga intencional garante que grande parte dos updates executados pelos runners ocorram como atualizações HOT.

Isso elimina mutações redundantes nos índices B-Tree de status e telemetria, cortando a amplificação de gravação no subsistema NVMe.

Checklist de engenharia para modelagem e tuning de concorrência relacional

Para extrair a máxima eficiência do MVCC e evitar gargalos silenciosos em produção, siga este roteiro de práticas recomendadas:

• Monitore o horizonte de transações: no PostgreSQL, acompanhe a métrica datfrozenxid contra o relógio do sistema e garanta que autovacuum_freeze_max_age processe tabelas antes de 200 milhões de transações.
• Controle a fila de undo no MySQL: configure alertas para a métrica Innodb_history_list_length. Valores persistentemente acima de 100.000 indicam transações ativas abandonadas comprometendo o buffer pool.
• Calibre o fillfactor para tabelas voláteis: reduza o preenchimento de tabelas com mutações frequentes para 70% a 85% no PostgreSQL para maximizar o aproveitamento de atualizações HOT.
• Evite transações abertas desnecessárias: encerre conexões ociosas em modo transacional (idle in transaction) para não travar o recuo do horizonte de limpeza do VACUUM e do Purge.
• Adote SSI para regras multicamada: utilize o nível SERIALIZABLE no PostgreSQL em fluxos críticos de saldo, estoques ou alocação de recursos onde restrições de negócio cruzam múltiplas linhas.
• Trate erros de serialização no código: qualquer aplicação operando sob SSI deve implementar retentativas exponenciais automáticas ao interceptar o código de erro SQLSTATE 40001.
• Prefira SKIP LOCKED para filas assíncronas: nunca utilize polling ingênuo com loops de espera em bancos relacionais; consuma filas com FOR UPDATE SKIP LOCKED para eliminar contenção.

Perguntas Frequentes sobre MVCC e Concorrência Relacional

O que significa a afirmação de que leitores nunca bloqueiam escritores no MVCC?

No controle de concorrência multiversão, leitores acessam cópias históricas de dados geradas antes do início da consulta. Mutações concorrentes criam versões paralelas sem travar páginas de leitura compartilhada. Essa dissociação permite leitura e escrita simultâneas sem espera mútua de locks.

Por que o PostgreSQL gera tuplas mortas e exige o processo de VACUUM?

O PostgreSQL adota uma arquitetura append-only onde cada atualização grava um novo registro completo na página do heap. A versão anterior é mantida intacta com o cabeçalho marcado como expirado para atender consultas em andamento. O VACUUM varre as páginas periodicamente para liberar esse espaço morto e reutilizá-lo em novas gravações.

O que acontece se o Autovacuum falhar e ocorrer o Transaction ID Wraparound?

Caso o contador circular de 32 bits ultrapasse o limite de 2 bilhões de transações sem congelamento, o banco sofre wraparound. Transações passadas passam a ser avaliadas matematicamente como transações futuras. O PostgreSQL entra em modo de emergência e recusa novas conexões de escrita para evitar perda total de dados.

Como o MySQL InnoDB reconstrói versões antigas sem inflar a tabela principal?

O InnoDB modifica os registros diretamente na página do índice clusterizado e salva as versões passadas como deltas compactos no undo log. Quando uma consulta necessita de um dado histórico, ela segue o ponteiro DB_ROLL_PTR. O motor aplica as alterações de trás para frente na memória até atingir a versão exigida pela transação.

O que diferencia o nível Repeatable Read do Serializable no PostgreSQL?

O Repeatable Read garante que consultas dentro da mesma transação enxerguem a mesma foto dos dados, mas não previne anomalias de escrita disjunta como o Write Skew. Já o nível Serializable aplica o algoritmo SSI com travas de predicado em memória. Ele detecta conflitos lógicos entre transações concorrentes e aborta a operação antes da quebra da consistência.