- Transação é uma estrutura para executar várias operações no banco de dados como uma unidade atômica, incluindo leitura, escrita, atualização e exclusão
- MySQL e Postgres controlam transações com
begin; e commit;, e em caso de falha ou erro, cancelam as alterações com rollback;
- Ambos os bancos de dados garantem leitura consistente (consistent read), mas o Postgres usa armazenamento multiversão de linhas (MVCC), enquanto o MySQL usa undo log
- O nível de isolamento (isolation level) controla a interferência de dados entre transações e é dividido em quatro níveis, de Serializable até Read Uncommitted
- Postgres e MySQL tratam conflitos de escrita concorrente de formas diferentes: o Postgres usa validação otimista, enquanto o MySQL usa bloqueio em nível de linha (row-level locking)
Conceitos básicos de transações
- Uma transação é uma estrutura que agrupa várias operações SQL no banco de dados em uma unidade de execução atômica
- Começa com
begin;, termina com commit; e permite executar várias consultas no meio do processo
- No momento do
commit;, todas as alterações são aplicadas de uma só vez
- Em caso de falha inesperada (queda de energia, erro de disco etc.) ou cancelamento intencional,
rollback; reverte as alterações
- O Postgres oferece suporte à recuperação com WAL(Write-Ahead Log)
- Os dados alterados durante uma transação ficam isolados e não podem ser vistos por outras sessões
- Com
rollback;, todas as alterações são canceladas e o banco de dados volta ao estado original
Leitura consistente (Consistent Reads)
- Durante a execução, a transação deve manter uma visão consistente dos dados que não seja afetada por alterações externas
- MySQL e Postgres oferecem isso no modo
REPEATABLE READ ou superior, mas a implementação é diferente
- Postgres: gerencia versões de cada linha com armazenamento multiversão de linhas (MVCC)
- MySQL: reconstrói versões anteriores usando undo log
Armazenamento multiversão de linhas no Postgres
- Sempre que uma linha é atualizada, uma nova versão é criada, e a versão anterior registra o ID da transação em
xmax, enquanto a nova registra em xmin
- Antes do commit da transação, outras sessões não conseguem ver o conteúdo alterado
- Após o commit, a nova versão passa a valer em todo o banco de dados
- Em caso de
rollback;, a alteração é descartada e os dados originais são mantidos
- Versões antigas de linhas são limpas com o comando
VACUUM FULL, recuperando espaço de armazenamento
Undo Log no MySQL
- O MySQL sobrescreve a linha diretamente, mas registra o valor anterior no undo log para poder restaurá-lo quando necessário
- Cada linha possui como metadados
xid (ID da transação que modificou mais recentemente) e ptr (ponteiro para o undo log)
- Quando várias transações são executadas ao mesmo tempo, cada uma consulta seletivamente a versão de que precisa por meio do undo log
- Pode haver vários registros de undo log para a mesma linha, e a versão apropriada é escolhida com base no ID da transação
Níveis de isolamento (Isolation Levels)
- É a configuração que controla a interferência de dados entre transações e vai sendo relaxada na ordem Serializable → Repeatable Read → Read Committed → Read Uncommitted
- Serializable: todas as transações se comportam como se fossem executadas em sequência
- Repeatable Read: repetir a mesma consulta produz o mesmo resultado, mas ainda pode ocorrer phantom read
- Read Committed: é possível ler alterações já confirmadas por outras transações
- Read Uncommitted: permite dirty read, oferecendo o menor nível de proteção, mas maior desempenho
Escritas concorrentes (Concurrent Writes)
- Quando duas transações tentam modificar a mesma linha ao mesmo tempo, a forma de tratamento varia conforme o banco de dados
MySQL: bloqueio em nível de linha (Row-level Locking)
- O bloqueio compartilhado (S lock) permite que várias transações leiam ao mesmo tempo
- O bloqueio exclusivo (X lock) permite que apenas uma transação modifique a linha
- No modo
SERIALIZABLE, é preciso obter X lock em todas as atualizações, e em caso de conflito pode ocorrer deadlock
- O MySQL detecta deadlocks e encerra uma das transações
Postgres: Serializable Snapshot Isolation
- O Postgres usa predicate lock para rastrear acessos no nível de conjuntos de linhas
- Ex.: bloqueio para a condição
WHERE id BETWEEN 10 AND 20
- Em vez de bloquear o acesso de fato, ele detecta conflitos e encerra a transação quando há violação
- Isso evita deadlocks por meio de resolução otimista de conflitos (optimistic conflict resolution)
- Assim como no MySQL, em caso de conflito uma das transações é encerrada, e a aplicação precisa implementar lógica de nova tentativa
Conclusão
- As transações são um componente central dos bancos de dados e garantem atomicidade, consistência, isolamento e durabilidade (ACID)
- Postgres e MySQL alcançam o mesmo objetivo com estruturas internas diferentes
- Entender os quatro níveis de isolamento e o funcionamento das transações permite operar bancos de dados com mais estabilidade
1 comentários
Opiniões no Hacker News
Achei que este texto ficou um pouco aquém do esperado
Em vez de explicar os níveis de isolamento com foco nos fenômenos (phenomena) definidos no padrão SQL, acho mais intuitivo partir do conceito de serializabilidade (serializability)
A serializabilidade pode ser vista como uma generalização de thread safety, e perdê-la gera bugs em que o resultado muda conforme a ordem de execução
Os vários níveis de isolamento de banco de dados são apenas formas de relaxar essa garantia, e o usuário precisa obter garantias de outras maneiras
Os fenômenos são só ferramentas para visualizar situações não seriais, não algo diretamente ligado à serializabilidade
Por exemplo, até um cluster Kubernetes pode se comportar de forma serializável se usar controllers bem projetados
Cobrir transações, níveis de isolamento e MVCC de uma vez só, incluindo comparação entre vários bancos, é um trabalho enorme
Tentei equilibrar profundidade técnica, acessibilidade e o tamanho do texto
Comentou que seria bom ter mais notação e citações
Mas, se usado sem necessidade, o custo de coordenação entre transações aumenta, reduzindo concorrência e throughput
Também dá para pensar em transações como snapshots de sistemas de arquivos copy-on-write (btrfs, zfs), mas considero a analogia com branches do Git mais intuitiva
BEGIN é criar um branch, UPDATE é commit, ROLLBACK é apagar o branch, e COMMIT é como
git mergeSe houver conflito, o banco tenta fazer merge no nível de linha, e, se falhar, faz rollback ou força o merge dependendo da configuração
READ UNCOMMITTED prioriza merge rápido, enquanto SERIALIZABLE prioriza precisão
Esse tipo de analogia pode fazer alguém finalmente ter aquele momento de “ahá!” para entender o conceito de transação
O que surpreende muita gente é que Postgres e MySQL não estão em modo serializável por padrão, e sim em read committed
A diferença de desempenho não é “pequena”; na prática, ela é bem maior
Ao usar read committed, é preciso prestar atenção à gestão de locks, e restrições UNIQUE também são necessárias para evitar condições de corrida
Ainda assim, prefere essa abordagem a aceitar a perda de desempenho e os problemas de retry do modo serializável
Referência: documentação oficial do PostgreSQL
Veja a documentação do MySQL e a documentação do MariaDB
MyISAM hoje quase não é mais usado
A aplicação precisa detectar isso e ter uma estratégia de retry
O modo serializável parece bonito nos livros, mas quase não é usado na prática
Hoje em dia, muitas ferramentas de banco de dados priorizam compartilhamento de atualizações em tempo real em vez de ACID
Por exemplo, no Airtable, ao editar um campo a mudança aparece imediatamente na tela dos colegas, mas, por não haver transações, existe risco de inconsistência de dados
Veja também este post do blog da VisualDB
É realmente um prazer ler o blog da PlanetScale
Fiquei curioso sobre quais ferramentas foram usadas nas visualizações
As visualizações foram feitas com js + gsap (https://gsap.com)
Se você se interessa por esse tema, recomendo fortemente 『Designing Data-Intensive Applications』
O livro cobre não só vários níveis de isolamento, mas também a ambiguidade na definição de ACID
Ouvi dizer que a 2ª edição sai em breve
Em sistemas MVCC como o Postgres, as transações são parecidas com snapshots de sistemas de arquivos copy-on-write
No BEGIN, cria-se um snapshot dos dados, e o UPDATE só é refletido na cópia privada
No ROLLBACK, a cópia é descartada, e no COMMIT o novo snapshot vira a versão oficial
Essa analogia pode ser o que faltava para alguém entender claramente o conceito de transação
P.S. A analogia com branch do Git também funciona
Em casos como SELECT seguido de UPDATE, uma thread pode ficar bloqueada
Hoje vou testar no MySQL se dá para transformar isso em uma única query
Antigamente, transações eram uma pergunta frequente em entrevistas para backend
Todo mundo já usou, mas o nível de entendimento varia conforme a experiência
Mesmo sem decorar todos os níveis de isolamento, só de saber que eles se comportam de forma diferente já dá para perceber curiosidade e compreensão de sistemas
A explicação de “phantom read” pode induzir a erro
Em repeatable read, os valores das linhas existentes não mudam, mas novas linhas podem ser adicionadas
Como linhas existentes não são alteradas nem removidas, esse ponto deveria ficar mais claro
A frase “não tem relação com xmin/xmax” parece incompleta
Também achei estranho a visualização apontar para o cabeçalho da tabela no commit
Na prática, xmax/xmin não são o mecanismo central para decidir se algo foi commitado ou não?
Considerando subtransações, isso fica ainda mais complexo
Ainda assim, no geral foi uma leitura agradável pelas visualizações e explicações
Isso é central para entender níveis de isolamento, então parece mesmo que faltou uma seção