Lição 8.4 · Tempo de leitura: ~10 min

Uma transação SQL é um grupo de comandos que o banco de dados executa como uma única unidade: ou todos têm efeito, ou nenhum. Nesta lição você vai ver START TRANSACTION, COMMIT e ROLLBACK, como o SAVEPOINT desfaz parte de uma transação, o que acontece em caso de erro e de quais problemas os níveis de isolamento protegem quando vários usuários alteram os mesmos dados.

Transações em SQL: COMMIT, ROLLBACK e níveis de isolamento

Nas lições anteriores você alterou dados com INSERT, UPDATE e DELETE, um comando por vez. Operações reais costumam exigir vários comandos: alugar um filme no Sakila significa inserir uma linha em rental e uma linha em payment. Se o segundo comando falhar, o primeiro não pode ficar sozinho.

Os bancos de exemplo do SQLtest são somente leitura, por isso estes comandos retornam lá um erro de permissão. Os exemplos usam o esquema Sakila e foram verificados no MySQL 8.0 e no PostgreSQL 17.

O que é uma transação?

Uma transação transforma várias alterações em uma única operação. O banco de dados garante as quatro propriedades ACID, que você viu na lição 1.3:

• Atomicidade: todos os comandos da transação têm efeito, ou nenhum.
• Consistência: depois da transação, todas as restrições continuam válidas.
• Isolamento: transações concorrentes não veem as alterações não concluídas umas das outras.
• Durabilidade: depois de confirmadas, as alterações sobrevivem a uma falha ou a uma queda de energia.

O exemplo clássico é uma transferência bancária: debitar de uma conta e creditar na outra. Sem transação, uma falha entre os dois passos faz o dinheiro sumir.

START TRANSACTION, COMMIT e ROLLBACK

START TRANSACTION;

-- statements that change data

COMMIT;    -- save all changes
-- or
ROLLBACK;  -- undo all changes

• START TRANSACTION (ou BEGIN) abre uma transação.
• COMMIT torna todas as suas alterações permanentes e visíveis para os outros usuários.
• ROLLBACK desfaz todas as alterações feitas desde o início da transação.

As palavras-chave variam um pouco entre os bancos de dados:

Exemplo: alugar um filme e receber o pagamento

START TRANSACTION;

INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES ('2005-08-24 10:00:00', 1, 1, 1);

INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (1, 1, LAST_INSERT_ID(), 0.99, '2005-08-24 10:00:00');

COMMIT;

Resultado: as duas linhas são salvas juntas. No MySQL, LAST_INSERT_ID() retorna o rental_id que acabou de ser gerado; o PostgreSQL usa INSERT ... RETURNING rental_id para isso.

Exemplo: desfazer um erro

START TRANSACTION;

UPDATE film
SET rental_rate = rental_rate * 2;

SELECT SUM(rental_rate) FROM film;  -- the new prices, visible inside the transaction

ROLLBACK;

Resultado: depois do ROLLBACK, todos os preços voltam ao que eram. Antes do COMMIT, os outros usuários não veem os preços dobrados (exceto no nível READ UNCOMMITTED, descrito abaixo).

Autocommit: cada comando é uma transação

Se você não abrir uma transação, o banco de dados usa uma mesmo assim: cada comando é executado na sua própria transação e confirmado automaticamente. Esse modo se chama autocommit e vem ativado por padrão no MySQL, no PostgreSQL e no SQL Server.

SELECT @@autocommit;  -- MySQL: 1 means autocommit is on

Por isso, um UPDATE isolado sem START TRANSACTION não pode ser desfeito com ROLLBACK: ele foi confirmado no momento em que terminou. START TRANSACTION suspende o autocommit até o COMMIT ou o ROLLBACK.

SAVEPOINT: desfazer parte de uma transação

Um ponto de salvamento (savepoint) marca um ponto dentro de uma transação. ROLLBACK TO SAVEPOINT desfaz apenas as alterações feitas depois dele; a transação continua aberta.

START TRANSACTION;

UPDATE film SET rental_rate = 1.99 WHERE film_id = 1;

SAVEPOINT before_second;

UPDATE film SET rental_rate = 9.99 WHERE film_id = 2;

ROLLBACK TO SAVEPOINT before_second;

COMMIT;

Resultado: o filme 1 recebe o novo preço 1.99; a alteração do filme 2 é desfeita e ele mantém o preço antigo.

Pontos de salvamento são úteis em operações longas, em que um passo pode falhar e você quer repeti-lo sem perder o trabalho já feito.

O que acontece quando um comando falha

É aqui que os bancos de dados se comportam de forma diferente, e isso é uma fonte comum de bugs.

MySQL e MariaDB desfazem só o comando que falhou. A transação continua aberta, e o COMMIT salva tudo o que deu certo:

START TRANSACTION;

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (1, 1, 2.99, '2005-08-24 10:00:00');   -- succeeds

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (999, 1, 4.99, '2005-08-24 10:00:00'); -- fails: customer 999 does not exist

COMMIT;

Resultado no MySQL: a segunda inserção falha com erro de chave estrangeira, mas o COMMIT ainda salva o primeiro pagamento. Para ter o "tudo ou nada", a aplicação precisa verificar os erros e executar o ROLLBACK por conta própria.

O PostgreSQL marca a transação inteira como falha depois de um erro. Todo comando seguinte é rejeitado com "current transaction is aborted", e o COMMIT na verdade executa um rollback: nada é salvo.

Comandos que confirmam implicitamente

No MySQL, no MariaDB e no Oracle, comandos DDL como CREATE TABLE, ALTER TABLE e DROP TABLE confirmam a transação atual antes de serem executados. Um ROLLBACK posterior não consegue desfazer as alterações feitas antes deles:

START TRANSACTION;

UPDATE film SET rental_rate = 5.99 WHERE film_id = 1;

CREATE TABLE film_backup (film_id INT);  -- implicit COMMIT here

ROLLBACK;  -- too late: the UPDATE is already saved

Resultado no MySQL: o novo preço 5.99 permanece. O PostgreSQL e o SQL Server executam DDL dentro da transação, então lá o ROLLBACK desfaz tanto o UPDATE quanto o CREATE TABLE.

Problemas de concorrência: leituras sujas, não repetíveis e fantasmas

Quando várias transações trabalham com as mesmas linhas ao mesmo tempo, uma transação pode ver os dados em um estado em que não deveria. O padrão SQL define três anomalias de leitura:

Uma leitura suja passo a passo:

Níveis de isolamento de transações

O nível de isolamento define de quais dessas anomalias uma transação está protegida. Quanto mais forte o isolamento, menos surpresas, mas mais as transações esperam umas pelas outras.

O nível padrão depende do banco de dados:

• MySQL e MariaDB (InnoDB): REPEATABLE READ. Um SELECT comum lê um instantâneo feito na primeira leitura da transação, então na prática também não vê fantasmas.
• PostgreSQL, SQL Server, Oracle: READ COMMITTED. O PostgreSQL trata READ UNCOMMITTED como READ COMMITTED, por isso leituras sujas nunca acontecem nele; o Oracle suporta apenas READ COMMITTED e SERIALIZABLE.

Para mudar o nível:

-- for the next transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- for all following transactions of this connection (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

No PostgreSQL, SET TRANSACTION ISOLATION LEVEL ... é escrito logo depois do BEGIN. No nível SERIALIZABLE, o banco pode cancelar uma de duas transações em conflito com um erro de serialização; a aplicação deve então repeti-la.

Regras práticas

• Mantenha as transações curtas: enquanto uma transação está aberta, ela mantém bloqueios pelos quais outros usuários esperam. Nunca espere uma entrada do usuário dentro de uma transação.
• Verifique o resultado de cada comando e execute ROLLBACK em caso de erro, principalmente no MySQL, que não faz isso por você.
• Não misture DDL com transações de dados no MySQL: ele confirma implicitamente.
• Duas transações que bloqueiam as mesmas linhas em ordem inversa podem travar uma à outra: isso é um deadlock. O banco cancela uma delas, e a aplicação deve repeti-la.

Principais pontos:

• Uma transação agrupa comandos em uma unidade: COMMIT salva todos eles, ROLLBACK desfaz todos.
• Sem uma transação explícita, cada comando é confirmado imediatamente (autocommit).
• SAVEPOINT e ROLLBACK TO SAVEPOINT desfazem só parte de uma transação.
• Depois de um erro, o MySQL mantém a transação aberta, enquanto o PostgreSQL a aborta; no MySQL, execute ROLLBACK você mesmo.
• No MySQL, comandos DDL confirmam implicitamente a transação atual.
• Os níveis de isolamento protegem contra leituras sujas, não repetíveis e fantasmas; o padrão é REPEATABLE READ no MySQL e READ COMMITTED no PostgreSQL, no SQL Server e no Oracle.

Perguntas Frequentes

O que é uma transação em SQL?

Uma transação é um grupo de comandos SQL que o banco de dados executa como uma única unidade: ou todas as alterações são salvas com COMMIT, ou todas são desfeitas com ROLLBACK. As transações mantêm os dados consistentes quando uma operação exige vários comandos ou quando vários usuários alteram os dados ao mesmo tempo.

Qual é a diferença entre COMMIT e ROLLBACK?

COMMIT encerra a transação e torna suas alterações permanentes e visíveis para os outros usuários. ROLLBACK encerra a transação e desfaz todas as suas alterações, devolvendo os dados ao estado anterior ao START TRANSACTION.

Dá para desfazer depois do COMMIT?

Não. Depois do COMMIT, as alterações são permanentes; para revertê-las, é preciso um novo comando que altere os dados de volta, ou um backup. O mesmo vale para comandos executados no modo autocommit.

Qual é o nível de isolamento padrão?

REPEATABLE READ no MySQL e no MariaDB com InnoDB; READ COMMITTED no PostgreSQL, no SQL Server e no Oracle.

Perguntas de entrevista

O que ACID significa para uma transação?

Atomicidade: tudo ou nada. Consistência: as restrições valem antes e depois. Isolamento: transações concorrentes não veem as alterações não confirmadas umas das outras. Durabilidade: dados confirmados sobrevivem a falhas.

O que é uma leitura suja e qual nível de isolamento a impede?

Leitura suja é ler uma alteração não confirmada de outra transação, que pode ser desfeita depois. READ COMMITTED e todos os níveis mais fortes a impedem.

Qual é a diferença entre leitura não repetível e leitura fantasma?

Uma leitura não repetível é um valor alterado em uma linha que você já leu; uma leitura fantasma é uma nova linha que aparece no resultado de uma consulta repetida com a mesma condição.

O que acontece no MySQL se um comando dentro de uma transação falhar?

Só esse comando é desfeito; a transação continua aberta, e o COMMIT salva os comandos que deram certo. Para que a operação inteira seja atômica, a aplicação precisa capturar o erro e executar ROLLBACK.