Transações e isolamento

Uma correção pode aparecer na conexão do técnico e ainda não aparecer no caixa 01, e um ROLLBACK pode chegar tarde, depois de a alteração já ter sido confirmada. No InnoDB, a transação, a confirmação automática e o nível de isolamento explicam esses estados e mostram como conferir uma alteração antes de confirmá-la.

A transação

A transação é um conjunto de alterações tratado como uma unidade: ou todas valem, ou nenhuma vale. O comando COMMIT confirma a transação, e o comando ROLLBACK desfaz as alterações ainda não confirmadas. A transação pertence à conexão que a abriu, e só essa conexão pode confirmá-la ou desfazê-la.

A transação cobre apenas o banco em que as alterações foram feitas. O envio de dados à matriz, a gravação de um arquivo e a comunicação com um serviço externo ficam fora dela.

A confirmação automática

O MySQL trabalha, por padrão, com a confirmação automática ligada, chamada de autocommit. Cada comando de alteração é confirmado sozinho, assim que termina. Para reunir vários comandos, a conexão abre uma transação com START TRANSACTION e a encerra com COMMIT ou ROLLBACK. Um comando mostra a conexão atual e o estado da confirmação automática:

• SELECT CONNECTION_ID(), @@autocommit;

O valor 1 indica a confirmação automática ligada. Esse valor não prova que não existe uma transação aberta, porque o START TRANSACTION abre uma transação sem mudar essa configuração.

Algumas ferramentas gráficas de consulta trabalham com a confirmação automática desligada. Nelas, uma alteração em uma tabela InnoDB fica pendente até o COMMIT, invisível para as outras conexões e segurando os bloqueios das linhas alteradas.

Conferir antes de confirmar

Uma correção feita direto no banco pode ser conferida dentro de uma transação, antes da confirmação:

1. START TRANSACTION;: abre a transação;
2. UPDATE clientes SET email = 'jose.silva@example.com' WHERE id = 10;: faz a correção;
3. SELECT id, nome, email FROM clientes WHERE id = 10;: confere o resultado na mesma conexão.

A transação termina de uma de duas formas:

• COMMIT;: confirma a correção, quando o resultado está certo;
• ROLLBACK;: desfaz a correção, quando o resultado está errado.

Na ferramenta de linha de comando, o MySQL informa, depois do UPDATE, quantas linhas a condição encontrou e quantas foram mudadas, como em Rows matched: 1 Changed: 1. Um número diferente do esperado é o sinal para desfazer a transação antes de qualquer confirmação. Um ROLLBACK enviado por outra janela da ferramenta, com outra conexão, não desfaz a transação original.

O que a transação não cobre

• Tabelas MyISAM: cada alteração vale e fica visível para as outras conexões assim que o comando termina. O ROLLBACK não a desfaz e termina com o aviso 1196, de tabelas não transacionais que não puderam ser desfeitas.
• Mudanças de estrutura: comandos como ALTER TABLE, CREATE TABLE e DROP TABLE confirmam a transação em andamento antes de executar, e o que veio antes não pode mais ser desfeito. Essa confirmação se chama confirmação implícita.
• Regras da aplicação: a transação não executa as conferências da aplicação nem recalcula as colunas derivadas.

O isolamento

O isolamento define o que uma transação enxerga das alterações feitas por outras conexões. O InnoDB tem quatro níveis:

• REPEATABLE READ: é o padrão do InnoDB. As leituras comuns de uma transação usam uma visão dos dados fixada na primeira leitura, e as alterações confirmadas por outras conexões depois disso só aparecem depois do fim da transação.
• READ COMMITTED: cada leitura enxerga os dados confirmados até aquele momento, e duas leituras iguais na mesma transação podem trazer resultados diferentes.
• READ UNCOMMITTED: as leituras podem enxergar alterações ainda não confirmadas, que talvez nunca venham a valer.
• SERIALIZABLE: as leituras passam a bloquear as linhas lidas, o que aumenta as esperas entre as conexões.

O nível da conexão aparece com um comando:

• SELECT @@transaction_isolation;

Até o MySQL 5.7.19, essa variável se chama tx_isolation. A partir do 5.7.20, existe também o nome transaction_isolation, que é o único aceito no MySQL 8.

Uma transação sempre enxerga as próprias alterações, e as leituras com FOR UPDATE e os comandos de alteração trabalham com os dados mais recentes. Uma janela de consulta com uma transação aberta há horas continua mostrando os dados do início da transação, mesmo depois de outras conexões confirmarem alterações. O COMMIT ou o ROLLBACK encerra a transação, e a leitura seguinte passa a enxergar os dados atuais.

Erros, quedas e transações longas

Um erro no meio de uma transação nem sempre desfaz a transação inteira, e por isso o estado da conexão precisa ser conferido antes de repetir um comando ou de confirmar. Quando a conexão cai com uma transação aberta, o MySQL desfaz o que estava pendente, e o que já tinha recebido COMMIT continua valendo.

Uma transação longa segura os bloqueios das linhas alteradas e obriga o InnoDB a guardar as versões antigas dessas linhas para as leituras das outras conexões. Por isso, uma conferência dentro de uma transação termina logo, sem deixar a conexão aberta à espera de uma decisão.