SQLite WAL: concorrência entre leitores e escritores

O modo padrão e seus limites

Por padrão, o SQLite usa um rollback journal. Quando você grava algo, o SQLite copia as páginas originais para um arquivo -journal, altera o banco principal e apaga o journal no commit. Se o processo cair no meio da escrita, o journal é reproduzido ao contrário para desfazer a alteração parcial.

É simples e seguro, mas tem um problema chato: leitores e escritores brigam pelo mesmo arquivo. Enquanto um escritor segura o lock do banco, nenhum leitor consegue iniciar uma nova transação. Enquanto há leitores ativos, o escritor fica esperando. Numa aplicação movimentada — um servidor web com algumas requisições concorrentes, por exemplo — os erros SQLITE_BUSY aparecem mais rápido do que você gostaria.

O modo WAL muda esse jogo.

O que o WAL faz na prática

O write-ahead logging inverte a lógica. Em vez de alterar o arquivo principal do banco no lugar, o escritor acrescenta as páginas commitadas em um arquivo separado com sufixo -wal. Os leitores continuam lendo o arquivo principal, mas também dão uma olhada no WAL para pegar versões mais novas das páginas de que precisam.

O resultado: um escritor e quantos leitores você quiser podem estar ativos ao mesmo tempo. Cada leitor enxerga um snapshot consistente do momento em que sua transação começou, e o escritor segue adicionando coisas ao WAL sem encostar no que os leitores estão lendo.

PRAGMA journal_mode=WAL;

Esse PRAGMA sozinho já muda o banco. O modo é persistente — fica gravado no cabeçalho do arquivo, então toda conexão futura entra em WAL automaticamente. Não precisa rodar isso a cada conexão, só uma vez quando você provisiona o banco (ou dentro do seu runner de migrations).

O PRAGMA devolve o novo modo. Se retornar wal, está tudo certo. Se vier outra coisa, provavelmente o sistema de arquivos não suporta memória compartilhada (falo mais disso abaixo).

Como ativar o modo WAL no SQLite e conferir se funcionou

Você pode checar o modo atual a qualquer momento:

PRAGMA journal_mode=WAL;
PRAGMA journal_mode;

CREATE TABLE messages (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO messages (body) VALUES ('olá'), ('mundo');
SELECT * FROM messages;

A primeira chamada ativa o WAL e devolve o novo modo. A segunda (sem o =) só consulta o estado atual. A partir daí, o diretório do seu messages.db vai conter três arquivos quando houver atividade: messages.db, messages.db-wal e messages.db-shm. Os dois últimos aparecem e somem conforme há conexões abertas.

Os arquivos -wal e -shm do SQLite

O modo WAL traz dois arquivos extras, e vale entender para que cada um serve:

• -wal guarda as transações já commitadas que ainda não foram aplicadas no banco principal. Cresce conforme acontecem escritas e encolhe (ou é zerado) na hora do checkpoint.
• -shm é um arquivo de memória compartilhada. Funciona como um índice do WAL, para que todas as conexões saibam em qual posição está cada página sem precisar varrer o WAL a cada consulta.

Consequência prática: nunca copie um banco em modo WAL pegando só o arquivo .db. Os dados mais recentes estão no -wal, e sem ele sua cópia fica desatualizada ou corrompida. Ou você copia os três arquivos com nenhuma conexão escrevendo, ou — bem melhor — usa a API de backup do SQLite (assunto do próximo capítulo).

Concorrência no SQLite: um escritor, vários leitores

O WAL não habilita escritas concorrentes. O SQLite continua serializando-as: em qualquer instante, apenas uma transação detém o lock de escrita. O que mudou é que escritas não travam leituras, e leituras não travam escritas.

Na prática, uma aplicação web típica rodando em WAL se comporta assim:

• Endpoints de leitura intensa rodam em paralelo, sem disputa.
• Endpoints de escrita formam uma fila curta entre si, mas não bloqueiam as leituras.
• Leituras longas (relatórios, exportações, analytics) não fazem o escritor esperar.

Se duas conexões tentarem escrever ao mesmo tempo, a segunda recebe SQLITE_BUSY. A solução costuma ser definir um busy timeout razoável — pedir ao SQLite que espere um pouco antes de desistir:

CREATE TABLE messages (id INTEGER PRIMARY KEY, body TEXT);

PRAGMA busy_timeout=5000;

BEGIN IMMEDIATE;
INSERT INTO messages (body) VALUES ('escrita enfileirada');
COMMIT;

SELECT * FROM messages;

busy_timeout=5000 significa: "se houver um lock ativo, espere até 5 segundos antes de estourar erro". Junto com o WAL, isso resolve a maior parte da contenção que aplicações reais enfrentam no dia a dia. Já a forma BEGIN IMMEDIATE adquire o lock de escrita logo no início da transação, em vez de esperar a primeira escrita — assim você evita uma categoria chata de deadlocks de upgrade que acontece quando duas conexões pretendem escrever ao mesmo tempo.

Checkpoint: devolvendo o WAL ao banco principal

O arquivo WAL não pode crescer indefinidamente. O checkpoint é justamente o processo que pega as páginas já commitadas dentro do WAL, grava no banco principal e zera o WAL.

O SQLite dispara um checkpoint automaticamente quando o WAL passa de ~1000 páginas (esse é o padrão do wal_autocheckpoint). Na maioria dos casos, dá pra deixar como está. Mas, se você quiser ajustar esse valor ou forçar um checkpoint manual:

PRAGMA wal_autocheckpoint;
PRAGMA wal_autocheckpoint=2000;

PRAGMA wal_checkpoint(TRUNCATE);

O pragma wal_checkpoint aceita um modo:

• PASSIVE — faz checkpoint do máximo possível sem atrapalhar leitores/escritores. É o padrão.
• FULL — espera os escritores ativos terminarem e então faz checkpoint de tudo que já foi commitado.
• RESTART — igual ao FULL, mas também impede que novos leitores usem o WAL antigo.
• TRUNCATE — igual ao RESTART, mas ainda reduz o arquivo WAL para zero byte.

A maioria dos servidores nunca precisa chamar isso na mão. Mas, se você está distribuindo um aplicativo desktop e quer manter os arquivos enxutos ao desligar, rodar um checkpoint TRUNCATE antes de fechar a última conexão é um hábito saudável.

Pragmas que combinam bem com o modo WAL

O WAL sozinho já ajuda bastante. Só que, na prática, aplicações em produção costumam combiná-lo com mais alguns ajustes:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;

Um tour rápido:

• synchronous=NORMAL é a combinação recomendada com o WAL. É seguro contra travamentos da aplicação e do sistema operacional; só uma queda de energia em um instante muito específico pode fazer você perder as últimas transações — e, mesmo assim, o banco continua consistente. O padrão FULL é mais seguro, mas bem mais lento.
• busy_timeout já comentamos acima.
• foreign_keys=ON não tem relação com WAL, mas vale a pena ativar em toda conexão — por compatibilidade retroativa, o SQLite deixa a checagem de chaves estrangeiras desligada por padrão.

Esses ajustes valem por conexão (com exceção do journal_mode, que persiste). Rode-os logo depois de abrir a conexão no código da sua aplicação.

Quando o modo WAL não é a melhor escolha

O WAL é a recomendação padrão, mas algumas situações pedem outra abordagem:

• Sistemas de arquivos em rede. O WAL depende de memória compartilhada (mmap) entre os processos que acessam o banco. NFS, SMB e afins não dão suporte confiável a isso. Se o seu banco mora em um compartilhamento de rede, fique com o rollback journal — ou, melhor ainda, não coloque SQLite em compartilhamento de rede.
• Mídia somente leitura. O WAL precisa escrever os arquivos -wal e -shm. Um banco em CD-ROM ou similar tem que usar um modo de journaling que não escreva (ou ser aberto como somente leitura com mode=ro).
• Jobs em lote com um único escritor e sem leitores concorrentes. O WAL não atrapalha, mas também não traz ganho nenhum. O rollback journal padrão dá conta.

Para 95% das aplicações — backends web, apps desktop, apps mobile, dispositivos embarcados com armazenamento local — o WAL é a escolha certa.

Uma configuração realista

A maioria dos setups SQLite em produção tem mais ou menos esta cara, condensada em pragmas prontos para rodar:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;
PRAGMA temp_store=MEMORY;

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    payload TEXT NOT NULL
);

INSERT INTO events (payload) VALUES ('login'), ('cadastro'), ('compra');
SELECT id, created_at, payload FROM events;

temp_store=MEMORY mantém tabelas e índices temporários na RAM em vez do disco — um ganho pequeno e gratuito, desde que você tenha memória sobrando.

Configure isso uma única vez, na hora de abrir a conexão, dentro do setup do banco da sua aplicação. Com isso, você já cobre boa parte do que um app baseado em SQLite precisa para se comportar bem sob carga concorrente.

Próximo passo: backup e restauração

Agora que seu banco tem os arquivos companheiros -wal e -shm, simplesmente copiar o arquivo .db deixou de ser uma estratégia segura de backup. O próximo capítulo mostra a forma correta de fazer backup de um banco SQLite em produção — o comando .backup, a online backup API e o que fazer quando você precisa de um snapshot consistente sem tirar a aplicação do ar.