PostgreSQL: trocar o isolamento padrão para SERIALIZABLE quebra mais do que resolve

O atalho de uma linha, e por que ele tenta

Mais cedo ou mais tarde, alguém no time lê sobre write skew: duas transações, cada uma correta isoladamente, cada uma lendo um dado que a outra está prestes a mudar, ambas fazendo commit, e o banco terminando num estado que nenhuma ordem de execução das duas produziria. Tanto READ COMMITTED quanto REPEATABLE READ permitem que isso aconteça.

A correção parece trivial, uma única linha de SQL↳SQL64 conteúdosSQL Server – Como evitar SQL Injection?Data · mai 2019Azure SQL DB Managed InstanceData · abr 2019SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query)Data · abr 2019Ver tudo em Data →:

ALTER DATABASE app SET default_transaction_isolation = 'serializable';

Todas as anomalias desaparecem, nenhuma mudança na aplicação, segundo Christophe Pettus descreve em "A Parade of Horribles, in Serial Order", publicado no Planet PostgreSQL. A conclusão dele, depois de medir o efeito real desse atalho, é direta:

Não faça isso.

Do not do this.Christophe Pettus, autor do artigo "A Parade of Horribles, in Serial Order"

SSI é engenharia séria, mas tem pré-requisitos

Desde a versão 9.1, o SERIALIZABLE do PostgreSQL é implementado como Serializable Snapshot Isolation (SSI). Ele roda a transação exatamente como o REPEATABLE READ rodaria, sem bloqueio adicional, e usa predicate locks (aparecem no pg_locks como SIReadLock, mais perto de flags que de locks de verdade) para rastrear o que cada transação leu. Quando identifica um padrão de dependência que poderia produzir uma anomalia, cancela uma das transações com SQLSTATE 40001 e espera que a aplicação tente de novo.

O trabalho original de Dan Ports e Kevin Grittner sobre a implementação mediu 5% de perda contra snapshot isolation num benchmark de processamento transacional em memória, e a documentação do PostgreSQL chama o SERIALIZABLE de melhor escolha de performance para alguns ambientes. Pettus concorda, mas aponta a letra miúda: esses ambientes são os construídos para isso, com transações curtas e descartáveis, reexecutáveis por um wrapper genérico de retry, leitura explicitamente marcada como READ ONLY e conexão sempre via pool. Uma aplicação escrita para READ COMMITTED que ganha esse default por baixo dos panos não cumpre nenhum desses requisitos.

O que quebra quando ninguém mudou o código

Pettus rodou os testes a seguir sobre PostgreSQL 18.6, numa máquina de dois núcleos com fsync desligado, usando pgbench em escala 20 com oito clientes, um cenário propositalmente desfavorável ao SSI. Os números devem ser lidos como proporção, não como benchmark absoluto.

Sob READ COMMITTED, uma transação cujas instruções foram bem-sucedidas faz commit (deixando de lado constraints adiadas). Sob SERIALIZABLE, o erro pode chegar em qualquer instrução, inclusive no próprio COMMIT:

ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.

Se a aplicação já disparou um e-mail de confirmação no meio da transação, o e-mail virou problema dela, não do banco.

Transações somente leitura não estão livres: no teste de Pettus com o exemplo "Deposit Report" da wiki do PostgreSQL, uma transação READ ONLY com dois SELECT foi cancelada no segundo. Savepoints também não ajudam: o conflito vem do que a transação já leu, e voltar a um savepoint não desfaz a leitura. Pettus repetiu o INSERT que havia falhado depois de um rollback para savepoint, e o erro voltou, idêntico. Só o reinício completo da transação, a partir do BEGIN, funciona. Qualquer framework que envolva cada operação num savepoint e tente retry nesse nível cai na mesma armadilha.

O plano de execução decide sua taxa de erro

Predicate locks começam no nível de linha nas tabelas e de página nos índices. Acima de duas linhas numa mesma página, eles são promovidos a lock de página; acima de um certo número de locks numa tabela (32 por padrão), viram lock da relação inteira, controlado por max_pred_locks_per_relation. Um sequential scan pula a etapa intermediária e trava a relação inteira de cara.

No teste de Pettus, uma transação que lê 40 linhas espalhadas por uma tabela de dois milhões de registros, seguida de um UPDATE em outro ponto qualquer, quase nunca tocava nas mesmas linhas que outras transações concorrentes. Mesmo assim:

Quarenta linhas já passam do limiar de promoção, então toda transação acabava com um lock na tabela inteira, e qualquer UPDATE concorrente virava conflito. Isso também dá ao otimizador de consultas poder sobre a taxa de erro: uma mudança de estatística que troca um index scan por um sequential scan, antes custava alguns milissegundos; sob SSI, converte um lock de linha num lock de tabela inteira.

O custo que aparece mesmo quando nada falha

Subir o limiar de promoção (max_pred_locks_per_relation = 1000) zera as falhas do teste anterior, mas o throughput cai para a faixa de 3.700 a 4.000 TPS, contra cerca de 5.000 em READ COMMITTED: entre 20% e 25% de perda só pela contabilidade de cerca de 80 predicate locks por transação. Mesmo no pgbench padrão, mais gentil, Pettus mediu 9.231 TPS em READ COMMITTED, 6.879 em REPEATABLE READ e 6.283 em SERIALIZABLE: o SSI cobra mais 9% sobre uma base que o REPEATABLE READ já havia reduzido.

Uma sessão esquecida derruba todo o cluster

Um predicate lock não desaparece quando a transação dona dele faz commit: ele precisa sobreviver até que todas as transações serializáveis concorrentes terminem. Pettus abriu uma sessão, rodou BEGIN e um SELECT, e a deixou aberta. Em seguida, iniciou a carga de 40 linhas descrita acima, com o limiar de promoção elevado. Em um décimo de segundo, todos os clientes receberam:

ERROR: out of shared memory
HINT: You might need to increase "max_pred_locks_per_transaction".

A sessão ociosa estava segurando todo predicate lock tirado desde o início do seu snapshot, até a tabela de locks compartilhada encher. O SQLSTATE dessa falha é 53200, não 40001: um wrapper de retry que só trata erros de serialização não pega esse caso. O mais grave é que nem precisa ser uma sessão serializável. Uma sessão READ COMMITTED parada depois de um único UPDATE derrubou os oito clientes em dois segundos, porque a limpeza de locks espera pelo snapshot mais antigo entre as transações serializáveis ativas, e qualquer transação aberta que detenha um ID de transação trava esse avanço.

As réplicas também param de responder

SERIALIZABLE não existe em hot standby. Como ALTER DATABASE ... SET fica armazenado no catálogo, e o catálogo é replicado, segundos depois do "ajuste de uma linha" rodar no primário, toda nova conexão numa réplica recebe:

ERROR: cannot use serializable mode in a hot standby
DETAIL: "default_transaction_isolation" is set to "serializable".

Isso inclui o health check do load balancer, a ferramenta de relatório e o pg_dump. Como a réplica é somente leitura, não dá para desfazer a configuração nela: cada cliente precisa rodar o SET sugerido no hint ou se conectar com PGOPTIONS que sobrescreva o valor herdado.

O argumento do README, e por que ele envelheceu mal

Existe um argumento sério a favor do atalho, e ele vem de quem construiu o recurso: o SSI só observa transações serializáveis. Uma transação READ COMMITTED escrevendo nas mesmas tabelas (um job, uma migração, alguém digitando no psql às duas da manhã) é invisível para o mecanismo, e a garantia cai por terra. Daí o README do SSI, no código-fonte do PostgreSQL, concluir:

Se você quer impor regras de negócio por meio do SSI, todas as transações deveriam rodar no nível de isolamento SERIALIZABLE, e isso provavelmente deveria ser configurado como padrão.

If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.README do código-fonte do PostgreSQL, citado por Christophe Pettus

Pettus localiza essa frase no commit que introduziu o SSI, em 7 de fevereiro de 2011, sete meses antes do lançamento do PostgreSQL 9.1, e nunca mais alterada desde então. Na época, a única evidência de custo disponível vinha de dois protótipos acadêmicos sobre Berkeley DB e InnoDB; nada ali previa o que uma sessão esquecida faria à tabela de locks, nem que réplicas parariam de responder.

Além disso, default_transaction_isolation é um parâmetro comum, que qualquer sessão sem privilégio nenhum pode contornar com SET, BEGIN ISOLATION LEVEL READ COMMITTED ou uma opção na string de conexão: o default global não torna todas as transações serializáveis, só as que nunca pediram outra coisa. E "todas as transações" é mais do que o mecanismo exige: a regra real é mais estreita, cobrindo só as transações que leem ou escrevem as tabelas por trás do invariante que importa.

Girar os parâmetros não resolve, só adia o problema

Três parâmetros controlam o predicate locking: max_pred_locks_per_transaction dimensiona a tabela de locks compartilhada e exige reinício; max_pred_locks_per_relation e max_pred_locks_per_page definem os limiares de promoção e só exigem reload. Diante do hint de erro, a tentação é subir tudo até os erros pararem. Nos testes de Pettus, eles não param: mudam de forma.

Em resumo: aumentar max_pred_locks_per_transaction de 64 para 64.000 custa memória real, alocada por inteiro na subida do servidor (não sob demanda, como o shared_buffers), derruba o throughput em cerca de 22% só pelo tamanho da tabela, e ainda assim só adia o esgotamento causado por uma sessão ociosa: de 0,1 segundo para pouco menos de quatro minutos, com 2,1 GB a mais de memória residente.

Acima de cerca de 6,4 milhões de predicate locks, qualquer consulta ao próprio pg_locks, a visão que o time usaria para diagnosticar o problema, passa a falhar com invalid memory alloc request size. E elevar o limiar de promoção junto (efeito colateral do hint, que não menciona isso) multiplicou o tempo de um index scan comum sobre um milhão de linhas, de 0,3 segundo para até 141 segundos, por conta da varredura de listas de locks que a promoção existia justamente para evitar.

O que fazer em vez disso

A recomendação de Pettus, e o ponto onde a leitura dele interessa a quem modela dados no Brasil, é manter default_transaction_isolation em read committed e proteger diretamente os poucos invariantes que write skew ameaça: uma constraint UNIQUE ou de exclusão onde ela resolve, SELECT ... FOR UPDATE na linha que arbitra a decisão, um lock explícito onde nada mais serve. Sob READ COMMITTED, esses mecanismos esperam em vez de falhar, e ficam visíveis no código para quem ler depois.

Quando o SERIALIZABLE for mesmo a ferramenta certa, e há sistemas em que é, ele precisa ser projetado desde o primeiro dia, não ligado por cima de um sistema existente:

• Wrapper de retry em torno da transação inteira, não de trechos isolados;
• READ ONLY declarado onde for verdade;
• Transações curtas, atrás de um pooler de conexões;
• idle_in_transaction_session_timeout curto para todos os papéis, e transaction_timeout (PostgreSQL 17 em diante) nos papéis da aplicação;
• Apenas as tabelas protegidas rodando sob SERIALIZABLE, com privilégios isolando o resto do banco;
• Réplicas assumindo repeatable read por conta própria.

Fica a tarefa de modelagem, não de configuração: isolar as tabelas que realmente precisam da garantia, em vez de empurrar um nível de isolamento para o banco inteiro na esperança de que ninguém perceba a conta.

Fonte: Planet PostgreSQL