Transações no SQLite: BEGIN, COMMIT e ROLLBACK

Uma transação é um pacote tudo-ou-nada

Uma transação agrupa várias instruções de modo que ou todas surtem efeito, ou nenhuma surte. Se algo der errado no meio do caminho, basta dar rollback e o banco volta exatamente ao estado inicial.

O exemplo clássico é a transferência de dinheiro entre contas:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

Os dois UPDATEs formam uma dupla inseparável. Se o banco travasse entre um e outro, Ada ficaria 2000 centavos mais pobre e Boris não receberia nada. Envolver os dois em BEGIN ... COMMIT torna a operação atômica — ou os dois acontecem, ou nenhum acontece.

Autocommit: o padrão que você já está usando no SQLite

Cada comando SQL que você rodou até aqui já foi uma transação. Por padrão, o SQLite opera em modo autocommit: cada instrução ganha seu próprio BEGIN e COMMIT implícitos por trás dos panos.

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

INSERT INTO notes (body) VALUES ('primeiro');
INSERT INTO notes (body) VALUES ('segundo');
INSERT INTO notes (body) VALUES ('terceiro');

SELECT * FROM notes;

Três INSERTs, três transações separadas, três idas ao disco para fazer o fsync da alteração. Tudo bem quando é uma gravação isolada, mas vira um gargalo em cargas em massa — e ainda por cima você não consegue desfazer um conjunto de comandos como uma unidade. O BEGIN desliga o autocommit até o próximo COMMIT ou ROLLBACK.

ROLLBACK: fingindo que nada aconteceu

O ROLLBACK descarta tudo o que foi feito desde o BEGIN correspondente. O banco volta ao estado anterior à transação.

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

Tanto o UPDATE quanto o DELETE somem — a tabela volta a ficar exatamente como estava antes do BEGIN. É essa rede de segurança que permite ao código da aplicação abortar de forma limpa quando algo dá errado no meio de uma operação com vários comandos.

A propósito, uma violação de constraint dentro de uma transação não dispara um rollback automático do bloco inteiro. O SQLite só desfaz o comando que falhou e mantém a transação aberta, esperando você decidir o que fazer. Se quiser comportamento tudo-ou-nada, a aplicação precisa emitir um ROLLBACK ao detectar o erro.

Acelerando inserts em massa com transações

Como cada comando em modo autocommit faz seu próprio fsync, agrupar um lote inteiro dentro de uma única transação costuma ser até 100x mais rápido:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

Um único fsync no COMMIT, em vez de um por linha. Se você já tentou importar milhares de linhas e ficou se perguntando por que estava tão lento, pode apostar: é isso.

DEFERRED, IMMEDIATE e EXCLUSIVE

O BEGIN aceita um modo que controla quando o SQLite adquire os locks:

• BEGIN DEFERRED (o padrão) — não pega lock nenhum até você ler ou escrever. O lock de escrita é obtido de forma preguiçosa, no primeiro comando que altera dados.
• BEGIN IMMEDIATE — pega o lock de escrita na hora. As outras conexões ainda conseguem ler, mas nenhuma outra consegue começar a escrever.
• BEGIN EXCLUSIVE — igual ao IMMEDIATE, só que nenhuma outra conexão consegue nem ler. No modo WAL, esse modo se comporta igual ao IMMEDIATE; a diferença só faz sentido no modo antigo de rollback journal.

BEGIN DEFERRED;     -- igual ao BEGIN comum
BEGIN IMMEDIATE;    -- reserva o bloqueio de escrita agora
BEGIN EXCLUSIVE;    -- reserva tudo (modo rollback-journal)

A escolha importa para concorrência. Com um BEGIN simples, duas conexões podem abrir uma transação ao mesmo tempo, ler tranquilamente e aí dar ruim na hora da escrita — a segunda que pedir o lock de escrita leva um SQLITE_BUSY e, pior ainda, já fez leituras que agora precisam ser descartadas.

O BEGIN IMMEDIATE resolve isso: se você já sabe que vai escrever, peça o lock de escrita logo de cara. A segunda conexão bloqueia (ou falha rapidinho) na hora, antes de fazer qualquer trabalho que teria que jogar fora.

Regra prática: se a transação vai escrever, use BEGIN IMMEDIATE.

Leitura dentro de uma transação enxerga um snapshot

Enquanto uma transação está aberta, suas leituras enxergam um snapshot consistente do banco — do estado em que ele estava quando a transação começou (no modo WAL) ou quando você fez a primeira leitura (no modo rollback-journal). Mudanças commitadas por outras conexões não aparecem do nada nas suas consultas.

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, sua própria escrita está visível
COMMIT;

SELECT n FROM counter;

Você enxerga suas próprias escritas ainda não confirmadas; outras conexões, não. Assim que você dá COMMIT, o novo valor passa a ser visível para todo mundo. É a isso que as pessoas se referem quando dizem que o SQLite é serializable — não existe botão de READ COMMITTED para girar, porque o padrão já é o nível mais forte de isolamento.

Uma transação no código da aplicação

Num programa de verdade, o padrão costuma ser um try/except (ou try/catch) envolvendo o corpo da operação, com um ROLLBACK no caminho de erro:

-- Pseudocódigo para qualquer biblioteca cliente
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

A maioria das bibliotecas cliente (o sqlite3 do Python, o better-sqlite3 e afins) já encapsulam isso com um bloco with ou um helper transaction(). Vale dar uma olhada na documentação da sua lib — os padrões nem sempre são o que você imagina. O sqlite3 do Python, em particular, sempre teve um comportamento meio esquisito de autocommit; versões mais recentes incluíram um parâmetro autocommit decente para resolver isso.

Pegadinhas que costumam pegar todo mundo

• DDL dentro de transação funciona. Dá para fazer rollback de CREATE TABLE, ALTER TABLE e até DROP TABLE. O SQLite é incomum nesse aspecto — muitos outros bancos fazem commit automático em DDL.
• VACUUM não roda dentro de uma transação. Alguns outros comandos de manutenção também não. Esses precisam rodar em modo autocommit.
• Um COMMIT que falha é uma falha de verdade. Se o COMMIT retornar SQLITE_BUSY (raro, mas acontece), a transação não foi confirmada. Seu código precisa tratar isso — normalmente com uma nova tentativa.
• Transações longas travam quem quer escrever. Uma transação que fica aberta por minutos vai bloquear outros writers por minutos. Abra tarde, faça commit rápido.

A seguir: savepoints

BEGIN e COMMIT são tudo ou nada. Às vezes você quer desfazer só uma parte da transação — abandonar um passo arriscado, mas manter o resto. É justamente para isso que existem os savepoints, e é o que vem na sequência.