Transações em Banco de Dados: ACID e Níveis de Isolamento

Por que uma transferência bancária precisa de mais que dois UPDATE

Mova R$ 50 de uma conta para outra e a versão naive são duas instruções: subtrair da conta A, somar à conta B. Execute-as separadamente e, se o processo cair ou a conexão cair entre as duas, o dinheiro desaparece de A e nunca chega a B. O banco de dados fez exatamente o que foi pedido, duas vezes. O negócio perdeu os R$ 50 do mesmo jeito.

Uma transação envolve as duas instruções para que o banco de dados as trate como uma única unidade: ou as duas acontecem, ou nenhuma acontece. Essa garantia, mais outras três, é o que o termo “ACID” designa. É por isso que você pode confiar a um banco de dados dinheiro ou níveis de estoque, onde “meio feito” é pior que “não feito”.

O que as quatro propriedades ACID garantem

• Atomicidade. Todas as instruções de uma transação fazem commit juntas, ou nenhuma faz. Se o processo cair depois do débito mas antes do crédito, o banco de dados desfaz o débito também. Não sobra nenhum estado parcial para limpar manualmente.
• Consistência. Uma transação só pode levar o banco de dados de um estado válido para outro. Se uma restrição CHECK diz que um saldo não pode ficar negativo, nenhuma transação pode fazer commit de uma linha que a viole, independentemente do que o código da aplicação tentou fazer.
• Isolamento. Transações rodando ao mesmo tempo não veem o trabalho pela metade das outras. O quão rígido isso é aplicado é um ajuste, não uma regra fixa: esse ajuste é o nível de isolamento, tratado mais abaixo.
• Durabilidade. Uma vez que o banco de dados diz que uma transação fez commit, esse dado sobrevive a uma queda um segundo depois. É para isso que serve o write-ahead log: a mudança está em disco (ou replicada) antes do commit retornar.

Atomicidade e durabilidade são trabalho do banco de dados, e os motores mais usados fazem isso corretamente. Isolamento é aquele sobre o qual você decide. Isolamento mais rígido custa concorrência, e esse custo aparece nos gráficos de latência em produção.

Como executar uma transação com BEGIN, COMMIT e ROLLBACK

No PostgreSQL (e na maioria dos bancos SQL, com pequenas diferenças de sintaxe), uma transação é o conjunto de instruções entre BEGIN e COMMIT:

1
2
3
4
5
6

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

COMMIT;

Se algo der errado antes do COMMIT (uma violação de restrição, ou uma verificação de regra de negócio no código da aplicação), o ROLLBACK desfaz tudo que aconteceu desde o BEGIN:

1
2
3
4
5

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- a verificação de regra de negócio no código da aplicação encontra o saldo negativo
ROLLBACK;

Depois do rollback, o saldo da conta 1 é exatamente o que era antes do BEGIN ser executado. Para uma transação longa em que você quer desfazer só parte do trabalho, um savepoint te dá um rollback parcial:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

ROLLBACK TO before_credit;  -- desfaz só o crédito
UPDATE accounts SET balance = balance + 50 WHERE id = 3; -- credita outra conta no lugar

COMMIT;

Fora de um BEGIN explícito, a maioria dos drivers executa cada instrução na sua própria transação com auto-commit. Cada instrução é atômica por si só, mas nada liga duas instruções entre si. Pule o BEGIN na transferência bancária e você volta aos R$ 50 perdidos.

O que os níveis de isolamento de transação evitam

Duas transações concorrentes podem interferir uma na outra de três formas específicas, e os níveis de isolamento são definidos por quais delas permitem:

O padrão SQL define quatro níveis em torno dessas três anomalias:

Você raramente vai querer usar READ UNCOMMITTED. O PostgreSQL nem implementa esse nível e silenciosamente roda Read Committed no lugar; o MySQL respeita e vai te mostrar dirty reads. Na prática, os dois níveis que importam são Read Committed e Repeatable Read, e os dois bancos open source mais usados divergem nesse padrão:

• PostgreSQL usa Read Committed por padrão. Cada instrução dentro de uma transação vê um snapshot novo, tirado quando aquela instrução começa, então dois SELECTs na mesma transação podem retornar dados diferentes se outra transação fez commit no meio tempo.
• MySQL (InnoDB) usa Repeatable Read por padrão. O snapshot é tirado uma única vez, na primeira leitura da transação, e todo SELECT simples posterior naquela transação vê esse mesmo snapshot, mesmo que outra transação tenha feito commit de uma atualização desde então.

No PostgreSQL, defina o nível logo depois do BEGIN, antes de qualquer query rodar:

1
2
3
4
5
6
7

-- PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- ... mais adiante na mesma transação ...
SELECT balance FROM accounts WHERE id = 1; -- garantido o mesmo valor de cima
COMMIT;

No MySQL, a forma SESSION muda o nível para toda transação seguinte naquela conexão:

1
2

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Como o Repeatable Read bloqueia um phantom read no PostgreSQL

Abra duas sessões psql contra a mesma tabela e rode isto. Ver uma vez fica melhor na memória do que decorar a tabela.

Sessão A:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

Sessão B, enquanto a transação de A ainda está aberta:

1
2
3

BEGIN;
INSERT INTO accounts (id, balance) VALUES (9, 2000);
COMMIT;

Sessão A, mesma transação aberta, rodando a query idêntica de novo:

1
2
3
4

SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

A linha que B inseriu e fez commit não aparece. O Repeatable Read do PostgreSQL tira seu snapshot na primeira query da transação de A e o mantém até o COMMIT, então linhas novas de outras transações nunca aparecem. Isso é mais rígido do que o padrão exige: a tabela acima permite phantoms nesse nível, mas a implementação do PostgreSQL não os produz. Rode a mesma sequência com A em Read Committed e o segundo SELECT retorna a linha nova, porque Read Committed tira um snapshot novo a cada instrução.

Serializable vai além. Ele detecta quando duas transações concorrentes produziram um resultado que não poderia ter acontecido em nenhuma ordem sequencial, e aborta uma delas com um erro de serialização (o PostgreSQL retorna o SQLSTATE 40001) em vez de deixar a anomalia fazer commit. Esse é o nível que fecha a clássica race condition em que duas transações leem um valor, decidem com base nele, e as duas escrevem. Código que usa SERIALIZABLE precisa estar preparado para tentar de novo:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... instruções ...
COMMIT;
-- em caso de erro 40001 ("could not serialize access due to read/write dependencies"):
-- faça rollback e tente a transação inteira de novo

O que os níveis de isolamento mais rígidos custam

O preço de um isolamento mais rígido é a concorrência:

• Escritas ficam travadas até o commit, em todo nível. Um UPDATE pega um lock de linha no momento em que roda e o mantém até COMMIT ou ROLLBACK, independentemente do nível de isolamento. O que Repeatable Read e Serializable adicionam é manter o mesmo snapshot (ou, no Serializable, locks de predicado nas linhas que suas queries examinaram) durante toda a transação. Faça uma chamada de API externa lenta entre BEGIN e COMMIT e toda linha que você escreveu fica travada, e toda linha que você leu sob Serializable fica rastreada, pelo tempo que essa chamada durar.
• Retentativas não são opcionais sob Serializable. Ele vai rejeitar transações que parecem válidas sob carga concorrente, por design. Se sua aplicação não captura erros de serialização e tenta de novo, essas transações falham de forma intermitente, sob carga, que é o pior momento para descobrir isso. Repeatable Read pode lançar o mesmo erro 40001 numa atualização concorrente simples, então o código de retentativa não é só para Serializable — qualquer transação acima de Read Committed precisa dele.
• Transações longas bloqueiam mudanças de schema e incham o armazenamento. O MVCC do PostgreSQL mantém versões antigas das linhas enquanto qualquer transação aberta ainda possa precisar delas. Uma transação deixada aberta por horas (um bug comum: abrir uma numa requisição web e nunca fazer commit nem rollback) impede o VACUUM de recuperar esse espaço, e um ALTER TABLE numa tabela que ela tocou espera ela terminar.
• Nem toda escrita precisa de uma transação explícita. Uma única instrução UPDATE já é atômica. Envolver uma em BEGIN/COMMIT adiciona uma ida e volta sem garantia extra nenhuma. Use uma transação explícita quando a correção depender de duas ou mais instruções terem sucesso ou falharem juntas: a transferência bancária, um cadastro que escreve tanto uma linha em users quanto em accounts, um pedido que decrementa o estoque e insere o registro do pedido.

ORMs (Entity Framework, SQLAlchemy, ActiveRecord) normalmente abrem uma transação por unidade de trabalho e fazem o commit por você. Não vá contra isso. O bug para ficar de olho é o contrário: código que chama .save() ou .commit() separadamente para duas escritas relacionadas, jogando fora a atomicidade que o ORM teria dado.

Qual nível de isolamento usar

Comece em Read Committed e suba só para as transações específicas em que você consegue nomear a corrida que está fechando. É a mesma disciplina de adicionar um índice só depois de encontrar a query lenta. Se você não conseguir escrever essa corrida numa frase, ainda não precisa de um nível mais rígido.

Artigos relacionados

• Como Funcionam os Índices de Banco de Dados
• Tipos de JOIN em SQL Explicados com Exemplos
• O Que É uma Race Condition e Como Corrigi-la