CS Fundamentals Trainer · Guias

Níveis de isolamento de transação

De leituras sujas a write skew, os quatro níveis padrão versus o que o PostgreSQL e o MySQL realmente fazem, e como corrigir cada problema.

22 min de leitura

Quando duas transações tocam os mesmos dados ao mesmo tempo, o banco de dados precisa decidir o que cada uma pode ver da outra. Essa decisão é o nível de isolamento, o "I" de ACID. Escolha um nível fraco demais e você terá bugs que aparecem uma vez por mês sob carga e somem quando você tenta reproduzi-los. Escolha um nível forte demais e você paga em velocidade e em transações que precisam ser repetidas. Este guia mostra os problemas que podem acontecer, os quatro níveis padrão, o que os bancos de dados reais de fato fazem e as ferramentas que você tem para corrigir cada problema.

1. O que o isolamento promete

Uma transação agrupa instruções para que elas tenham sucesso ou falhem juntas. O isolamento acrescenta uma promessa sobre concorrência: cada transação se comporta como se rodasse sozinha, mesmo que muitas rodem ao mesmo tempo.

A forma mais forte dessa promessa é a serializabilidade: o resultado de executar transações concorrentemente é o mesmo que se elas tivessem rodado uma depois da outra, em alguma ordem. Sistemas reais costumam enfraquecer a promessa para ganhar velocidade, e o padrão SQL nomeia os níveis mais fracos pelos comportamentos estranhos que ainda permitem. Esses comportamentos se chamam anomalias, e você precisa saber reconhecer cada uma.

2. As anomalias

Cada exemplo mostra duas transações, T1 e T2, com o tempo correndo para baixo.

Dirty read (leitura suja)

T2 lê dados que T1 escreveu mas não confirmou. Se T1 faz rollback, T2 agiu com base em algo que nunca existiu.

TempoT1T2
1UPDATE accounts SET balance = 0 WHERE id = 1
2SELECT balance FROM accounts WHERE id = 1 → 0
3ROLLBACK(decide, com base em 0, recusar um pedido)

Non-repeatable read (leitura não repetível)

T1 lê uma linha duas vezes e obtém respostas diferentes, porque T2 a alterou e confirmou no meio.

TempoT1T2
1SELECT price FROM items WHERE id = 7 → 10
2UPDATE items SET price = 12 WHERE id = 7, COMMIT
3SELECT price FROM items WHERE id = 7 → 12

Phantom read (leitura fantasma)

T1 executa a mesma consulta duas vezes e obtém um conjunto de linhas diferente, porque T2 inseriu (ou apagou) uma linha que corresponde.

TempoT1T2
1SELECT count(*) FROM orders WHERE total > 100 → 5
2INSERT INTO orders (total) VALUES (150), COMMIT
3SELECT count(*) FROM orders WHERE total > 100 → 6

A diferença para a leitura não repetível: nenhuma linha que T1 já tinha visto mudou. Uma linha nova apareceu. Travar as linhas que você lê não impede isso, porque a linha ainda não existia.

Lost update (atualização perdida)

Duas transações leem um valor, calculam um novo e o escrevem de volta. A segunda escrita sobrescreve a primeira, que se perde em silêncio.

Diagram: Lost update: two deposits of 10, only one counts

Esse é o bug por trás dos padrões "ler, alterar no código da aplicação, salvar", e o caso clássico de duas pessoas editando o mesmo registro.

Se o banco de dados o impede depende do motor e do nível. No meu teste no PostgreSQL 16, o Read Committed deixou acontecer (saldo final 110), enquanto o Repeatable Read fez a segunda escrita falhar com could not serialize access due to concurrent update (SQLSTATE 40001), de modo que a aplicação podia tentar de novo. Essa verificação é do PostgreSQL; o InnoDB do MySQL no Repeatable Read não levanta erro aqui, porque seus updates sempre agem sobre a última versão confirmada da linha. Não consegui executar o MySQL, então esse último ponto se apoia na documentação dele [1]. Não conte com o nível para salvar você: use uma das correções da seção 5.

Write skew

Duas transações leem cada uma o mesmo conjunto de linhas, cada uma decide que a regra ainda vale e cada uma escreve em uma linha diferente. Nenhuma sobrescreveu a outra, mas juntas quebram a regra.

O exemplo padrão: um hospital precisa de pelo menos um médico de plantão. Alice e Bob estão de plantão, e os dois pedem para sair do plantão no mesmo instante.

Diagram: Write skew: both doctors leave, nobody is on call

O write skew é a anomalia que as pessoas esquecem, e é a que separa os níveis de "snapshot" dos verdadeiramente serializáveis. Aparece em reservas (duas pessoas reservam o último quarto), em nomes de usuário únicos sem uma restrição de unicidade e em qualquer "verificar e depois agir" que atravesse várias linhas [2][3].

Read skew

Um primo da leitura não repetível, visto em duas linhas que andam juntas. T1 lê a primeira linha, T2 move dinheiro entre as duas linhas e confirma, depois T1 lê a segunda linha, e o retrato dos dados que T1 tem nunca existiu. Tome duas contas com 100 cada (200 no total). T1 lê a conta 1 (100). T2 move 50 da conta 1 para a conta 2 e confirma. T1 lê a conta 2:

Nível (PostgreSQL 16)O que T1 viuTotal
Read Committed100 + 150250 (dinheiro apareceu)
Repeatable Read100 + 100200 (um snapshot consistente)

Backups e relatórios são as vítimas clássicas, e é por isso que uma leitura longa precisa de um snapshot (seção 6).

Experimente o write skew você mesmo

Você pode ver o exemplo dos médicos acontecer em duas janelas do psql (A e B). Primeiro, uma vez:

CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

Depois, passo a passo (o número do passo é a ordem em que você aperta Enter):

PassoJanela AJanela B
1BEGIN ISOLATION LEVEL REPEATABLE READ;BEGIN ISOLATION LEVEL REPEATABLE READ;
2SELECT count(*) FROM doctors WHERE on_call; → 2SELECT count(*) FROM doctors WHERE on_call; → 2
3UPDATE doctors SET on_call = false WHERE name = 'alice';
4UPDATE doctors SET on_call = false WHERE name = 'bob';
5COMMIT;COMMIT;

Os dois commits têm sucesso, e SELECT count(*) FROM doctors WHERE on_call; agora devolve 0. Reinicie (UPDATE doctors SET on_call = true;) e repita com ISOLATION LEVEL SERIALIZABLE nas duas janelas. O primeiro commit passa; o segundo falha com ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001, e a dica diz que pode ter sucesso se repetido). Mais uma vez, no Read Committed, com SELECT name FROM doctors WHERE on_call FOR UPDATE; como primeira consulta em cada janela: B agora espera nessa consulta até A confirmar, depois vê apenas um médico de plantão e pode recusar.

3. Os quatro níveis padrão

O padrão SQL define os níveis pelas três primeiras anomalias que eles precisam prevenir:

NívelDirty readNon-repeatable readPhantom read
Read Uncommittedpossívelpossívelpossível
Read Committedprevenidopossívelpossível
Repeatable Readprevenidoprevenidopossível
Serializableprevenidoprevenidoprevenido

Essa tabela é a resposta de livro-texto, e é onde muita gente para. Ela também é incompleta: não diz nada sobre lost update nem write skew, e o artigo de 1995 "A Critique of ANSI SQL Isolation Levels" mostrou que as definições do padrão são ambíguas e deixam passar várias anomalias, incluindo o snapshot isolation, que muitos bancos de dados oferecem com o nome de "Repeatable Read" [2].

O que você realmente recebe

Os nomes significam coisas diferentes em bancos de dados diferentes. Leia sempre a documentação do seu banco, não a tabela do livro-texto.

Banco de dadosPadrão"Repeatable Read" é na verdade..."Serializable" é na verdade...
PostgreSQLRead CommittedSnapshot isolation: sem dirty reads, leituras não repetíveis ou fantasmas; o write skew é possívelSerializable Snapshot Isolation (SSI): verdadeiramente serializável, abortando transações que poderiam causar uma anomalia [4][5]
MySQL (InnoDB)Repeatable ReadAs leituras usam um snapshot tirado na primeira leitura; leituras com lock e escritas usam next-key locks, que também bloqueiam fantasmas [1]As leituras simples também tomam locks compartilhados, então leitores podem bloquear escritores [1]
SQL ServerRead Committed, que usa locks a menos que READ_COMMITTED_SNAPSHOT esteja ligado (ele vem ligado por padrão no Azure SQL Database, desligado no SQL Server)Baseado em locks. Um nível separado, SNAPSHOT, dá um snapshot para a transação toda, depois que ALLOW_SNAPSHOT_ISOLATION é ligado no banco de dadosBaseado em locks; veja a página da Microsoft para os detalhes dos locks [6]
OracleRead Committed(não oferecido; o Oracle oferece Read Committed, Serializable e um modo somente leitura)Consistência de leitura para a transação inteira, com ORA-08177: Cannot serialize access quando uma linha que ela quer alterar foi alterada depois de ela começar. Entende-se em geral que isso é snapshot isolation, então o write skew ainda seria possível; a página do Oracle não usa esse termo nem discute write skew [7]

O SQL Server tem dois interruptores que mudam muita coisa. Com READ_COMMITTED_SNAPSHOT ligado, o nível Read Committed comum lê versões de linhas (cada instrução vê um snapshot tirado no seu início) em vez de tomar locks compartilhados, então os leitores deixam de bloquear os escritores; é uma configuração do banco de dados inteiro, e aplicações que dependiam do bloqueio podem se comportar de outra forma. O isolamento SNAPSHOT dá um snapshot para a transação toda e precisa ser permitido por banco de dados antes [6].

Dois fatos da documentação do PostgreSQL valem ser lembrados: pedir Read Uncommitted dá Read Committed (dirty reads não são possíveis de forma alguma), e o Repeatable Read lá é mais forte que o padrão exige, porque também previne fantasmas [4].

4. Como os bancos de dados fazem isso

Há duas técnicas principais, e muitos sistemas misturam as duas.

Com MVCC, o nível de isolamento é sobretudo uma questão de quando o snapshot é tirado:

Nível (PostgreSQL)O snapshot é tirado...Então uma leitura repetida
Read Committedno início de cada instruçãopode ver novos commits entre as instruções
Repeatable Readno início da primeira instrução da transaçãosempre vê os mesmos dados
Serializablecomo o Repeatable Read, mais o rastreamento de dependências de leitura/escritaigual, e o banco de dados aborta uma transação quando um ciclo poderia se formar

Os snapshots explicam tanto o bom quanto o ruim do "Repeatable Read": os relatórios veem um retrato estável dos dados (bom), mas duas transações trabalham cada uma no seu próprio retrato e podem as duas agir sobre fatos que a outra está mudando (write skew).

5. Prevenindo os problemas

Você tem mais ferramentas do que apenas "subir o nível". Muitas vezes uma mudança menor é melhor.

Faça a mudança no banco de dados, em uma instrução

Uma lost update precisa de ler, calcular, escrever na aplicação. Faça disso uma única instrução e o banco de dados faz a aritmética sobre o valor atual:

-- unsafe: the application read the balance earlier
UPDATE accounts SET balance = 110 WHERE id = 1;

-- safe: the database calculates from the current value
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

No Read Committed do PostgreSQL, se outra transação mudou a linha nesse meio-tempo, o update espera que ela confirme, depois relê a nova versão da linha e aplica a mudança a ela [4]. É por isso que essa forma simples é segura.

Trave as linhas sobre as quais você decide

SELECT ... FOR UPDATE trava as linhas que devolve até o fim da transação, então quem mais quiser alterá-las ou travá-las espera:

BEGIN;
SELECT * FROM doctors WHERE on_call FOR UPDATE;   -- lock everyone currently on call
-- if the count is 1, refuse; otherwise:
UPDATE doctors SET on_call = false WHERE name = 'alice';
COMMIT;

Agora os dois médicos se serializam: a segunda transação espera pela primeira e depois vê o novo estado (ou, no Repeatable Read, recebe um erro de serialização e tenta de novo). Use FOR UPDATE quando você sabe quais linhas importam. Ele não ajuda contra fantasmas (uma linha que ainda não existe não pode ser travada).

Outras ferramentas de locking que vale conhecer

UPDATE jobs SET state = 'taken'
WHERE id = (SELECT id FROM jobs WHERE state = 'new' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED)
RETURNING id;

No meu teste, o worker A pegou o job 1 e o worker B, rodando ao mesmo tempo, pegou o job 2 sem esperar. (O resultado é "algum job", não uma ordem estrita, então isso serve a filas em que a ordem não é uma promessa.)

Locking otimista: uma coluna de versão

Para fluxos de "ler, editar por um tempo, salvar", como um formulário, não mantenha um lock enquanto uma pessoa digita. Guarde um número de versão e torne o update condicional a ele:

UPDATE documents
SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;     -- $3 is the version you loaded
-- 0 rows updated?  Someone else saved first: tell the user, or merge.

Deixe uma restrição ser o juiz

Se a regra pode ser escrita como uma restrição, deixe o banco de dados aplicá-la. Um índice UNIQUE impede que duas transações reivindiquem o mesmo nome de usuário, em qualquer nível de isolamento. Uma exclusion constraint do PostgreSQL pode dizer "duas reservas não se sobrepõem para o mesmo quarto". As restrições são verificadas contra os dados mais recentes, então fecham brechas que os snapshots deixam abertas.

Use Serializable, e repita

Quando a regra é complicada e você não consegue travá-la nem restringi-la, peça SERIALIZABLE e deixe o banco de dados detectar os problemas. O preço é que ele ocasionalmente aborta uma transação com uma falha de serialização (SQLSTATE 40001 no PostgreSQL), e seu código precisa repetir a transação inteira [4]. Um deadlock (40P01, seção 7) exige exatamente o mesmo tratamento, então os wrappers abaixo repetem nos dois casos. Isso significa que uma transação é uma função que você pode executar de novo, sem efeitos colaterais fora do banco de dados (sem e-mails enviados no meio, sem chamadas externas). Aqui está o wrapper de retry em quatro linguagens (escolha uma linguagem nas abas; sua escolha é lembrada):

import time
import psycopg
from psycopg import errors

RETRY = (errors.SerializationFailure, errors.DeadlockDetected)   # 40001 and 40P01


def run_serializable(conninfo, work, attempts=5):
    for attempt in range(1, attempts + 1):
        try:
            with psycopg.connect(conninfo) as conn:       # a pool in real code
                conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE
                with conn.transaction():
                    return work(conn)
        except RETRY:
            time.sleep(0.01 * attempt)                    # back off a little, then try again
    raise RuntimeError("too many serialization failures")
func RunSerializable(ctx context.Context, pool *pgxpool.Pool, work func(pgx.Tx) error) error {
	for attempt := 1; attempt <= 5; attempt++ {
		tx, err := pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
		if err != nil {
			return err
		}
		err = work(tx)
		if err == nil {
			err = tx.Commit(ctx) // a serialization failure can also surface here
		}
		if err == nil {
			return nil
		}
		_ = tx.Rollback(ctx)
		var pgErr *pgconn.PgError
		if !errors.As(err, &pgErr) || (pgErr.Code != "40001" && pgErr.Code != "40P01") {
			return err // not a serialization failure or a deadlock: do not retry
		}
		time.Sleep(time.Duration(attempt) * 10 * time.Millisecond) // back off a little
	}
	return errors.New("too many serialization failures")
}
async function runSerializable(pool, work, attempts = 5) {
  for (let attempt = 1; attempt <= attempts; attempt++) {
    const client = await pool.connect();
    try {
      await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await work(client);
      await client.query("COMMIT"); // a serialization failure can also surface here
      return result;
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {});
      if (err.code !== "40001" && err.code !== "40P01") throw err; // neither serialization failure nor deadlock
      await new Promise((resolve) => setTimeout(resolve, 10 * attempt)); // back off a little
    } finally {
      client.release();
    }
  }
  throw new Error("too many serialization failures");
}
using System.Data;
using Npgsql;

public static async Task<T> RunSerializableAsync<T>(
    NpgsqlDataSource db, Func<NpgsqlConnection, Task<T>> work, int attempts = 5)
{
    for (int attempt = 1; ; attempt++)
    {
        await using var conn = await db.OpenConnectionAsync();
        await using var tx = await conn.BeginTransactionAsync(IsolationLevel.Serializable);
        try
        {
            var result = await work(conn);
            await tx.CommitAsync();      // a serialization failure can also surface here
            return result;
        }
        catch (PostgresException e) when ((e.SqlState is PostgresErrorCodes.SerializationFailure or PostgresErrorCodes.DeadlockDetected) && attempt < attempts)
        {
            await tx.RollbackAsync();
            await Task.Delay(10 * attempt);   // back off a little, then try again
        }
    }
}

No PostgreSQL, o Repeatable Read pode falhar do mesmo jeito (um update de uma linha que outra transação mudou nesse meio-tempo levanta could not serialize access due to concurrent update), então o mesmo wrapper protege você lá.

6. Escolhendo um nível

SituaçãoUma escolha sensata
Requisição web típica: carregar uma linha, alterá-la, salvá-laRead Committed, com updates atômicos, colunas de versão otimistas e restrições
Um relatório ou uma exportação que precisa ver um momento consistenteRepeatable Read (somente leitura), para que os números fechem
Uma regra que atravessa várias linhas e que você não consegue travar nem restringirSerializable, com retries
Movimentação de dinheiroUpdates atômicos ou locks explícitos de linhas em uma ordem fixa (para evitar deadlocks), dentro do Read Committed ou mais forte; acrescente restrições como CHECK (balance >= 0)
Contadores e estatísticas em que pequenos erros não importamO que for mais barato

Para um relatório ou exportação longos que precisam ver um retrato consistente e nunca podem ser cancelados no meio, o PostgreSQL tem BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE. A transação pode esperar no início até conseguir um snapshot garantidamente livre de anomalias e depois roda sem nenhum risco de falha de serialização [4].

Um nível mais alto não é uma melhoria de graça: mais transações abortadas, mais memória para o rastreamento e a necessidade de repetir em código que talvez nunca tenha esperado uma falha. Suba-o para as transações específicas que precisam, não para o banco de dados inteiro. A maioria das aplicações PostgreSQL roda em Read Committed com um punhado de FOR UPDATE e restrições de unicidade, e está correta.

7. Deadlocks são um problema diferente

Deadlocks acontecem quando T1 espera por um lock que T2 segura enquanto T2 espera por um que T1 segura. O locking os cria, em qualquer nível de isolamento. O banco de dados detecta o ciclo e aborta uma transação (40P01 no PostgreSQL). Reduza-os travando as linhas em uma ordem consistente (sempre o menor id de conta primeiro), mantendo as transações curtas e repetindo quando um acontecer.

8. Isolamento entre mais de um banco de dados

Tudo acima trata de um banco de dados. Quando uma operação de negócio atravessa vários serviços, uma transação de banco de dados não consegue cobri-la, e você usa sagas, padrões de outbox e retries idempotentes. Termos relacionados que você pode ouvir:

9. Perguntas que os entrevistadores gostam de acrescentar

"Qual a diferença entre uma leitura não repetível e um fantasma?"

Uma leitura não repetível é uma linha que você já viu e que mudou ou desapareceu. Um fantasma é uma linha nova que agora corresponde à sua consulta. Locks de linha podem impedir a primeira, mas não a segunda.

"O Repeatable Read previne fantasmas?"

Pelo padrão, não. No PostgreSQL e nas leituras consistentes do InnoDB, sim (por meio de snapshots), mas o write skew ainda é possível na versão do PostgreSQL. É por isso que "depende do banco de dados" é o começo correto da resposta.

"Como você evita vender além do estoque o último item?"

UPDATE items SET stock = stock - 1 WHERE id = $1 AND stock > 0, e confira se uma linha foi atualizada. A condição e a mudança acontecem atomicamente; nenhum nível de isolamento além do Read Committed é necessário.

"Por que não usar sempre Serializable?"

Ele custa vazão e força retries. Muitas vezes uma restrição de unicidade ou um SELECT ... FOR UPDATE consegue a mesma segurança com menos custo. Use-o onde a invariante é difícil de expressar de outra forma.

"O que o MVCC custa?"

Versões antigas de linhas se acumulam e precisam ser limpas (VACUUM no PostgreSQL). Uma transação de longa duração mantém versões antigas vivas e pode inchar as tabelas. Mantenha as transações curtas.

10. Coisas a mencionar em uma entrevista

Resumo rápido

  1. O isolamento decide o que transações concorrentes podem ver umas das outras.
  2. Seis anomalias: dirty read, non-repeatable read, phantom, read skew, lost update, write skew.
  3. Os níveis padrão são Read Uncommitted, Read Committed, Repeatable Read, Serializable, mas cada banco de dados os implementa de forma diferente.
  4. O Repeatable Read do PostgreSQL é snapshot isolation (sem fantasmas, mas com write skew); só o Serializable é totalmente seguro.
  5. Corrija a maioria dos problemas com updates atômicos, SELECT ... FOR UPDATE, colunas de versão e restrições.
  6. Sob Serializable (e Repeatable Read), repita a transação inteira em 40001 (e em um deadlock, 40P01).
  7. Mantenha as transações curtas, trave em uma ordem consistente e suba o nível apenas onde for necessário.

Fontes

  1. Documentação do MySQL, InnoDB Transaction Isolation Levels.
  2. Berenson et al., A Critique of ANSI SQL Isolation Levels, SIGMOD 1995. As anomalias e o snapshot isolation.
  3. Martin Kleppmann, Designing Data-Intensive Applications, capítulo 7 (Transactions). Write skew e os níveis de isolamento fracos explicados em detalhe.
  4. Documentação do PostgreSQL, Transaction Isolation. O que cada nível faz no PostgreSQL e o conselho sobre retry.
  5. Ports e Grittner, Serializable Snapshot Isolation in PostgreSQL, VLDB 2012.
  6. Microsoft, SET TRANSACTION ISOLATION LEVEL (Transact-SQL). Os níveis do SQL Server, READ_COMMITTED_SNAPSHOT e SNAPSHOT.
  7. Oracle, Data Concurrency and Consistency. Os modos read committed, serializable e somente leitura, e o ORA-08177.
  8. Jepsen, Consistency Models. Um mapa dos modelos de isolamento e consistência, com serializabilidade e linearizabilidade lado a lado.

Pratique este tema: Databases

Mais guias