Redo, Undo e Locks

Conceito

O InnoDB mantém dois logs, com propósitos opostos, e é fácil confundi-los:

E há um terceiro log, que não é do InnoDB e sim do servidor: o binlog, log lógico para replicação (5. Replicação e Alta Disponibilidade).

Durabilidade: o parâmetro que define ACID

innodb_flush_log_at_trx_commit = 1   # ACID: fsync do redo a cada COMMIT
                               = 2   # escreve no cache do SO; perde ~1s se o SO cair
                               = 0   # flush a cada 1s; perde ~1s até se só o mysqld cair

Com = 1, cada COMMIT custa um fsync. O InnoDB amortiza isso com group commit: várias transações que confirmam ao mesmo tempo compartilham um único fsync. É por isso que throughput de COMMIT melhora com concorrência — até o ponto de contenção.

MVCC por read view

Quando uma transação faz a primeira leitura consistente, o InnoDB cria uma read view: a lista de transações ativas naquele instante. Ao ler uma linha, ele compara o DB_TRX_ID do registro:

linha alterada por transação invisível para mim?
   → siga o DB_ROLL_PTR até o undo log e RECONSTRUA a versão que eu deveria ver

A diferença estrutural com o Postgres: aqui a versão antiga é reconstruída sob demanda a partir do undo, e não guardada na tabela. Sem bloat de tabela — mas com uma consequência própria: se uma transação fica aberta por muito tempo, o undo dela não pode ser purgado, e a history list cresce. Leituras que precisam reconstruir versões muito antigas ficam progressivamente mais lentas.

-- o indicador a monitorar (equivalente moral do bloat no Postgres)
SELECT count FROM information_schema.innodb_metrics
 WHERE name = 'trx_rseg_history_len';   -- ou 'History list length' no SHOW ENGINE INNODB STATUS

Locks: a parte que causa deadlock

O InnoDB não trava linhas — ele trava entradas de índice. Essa frase é a chave para entender os deadlocks estranhos do MySQL.

O ponto crítico:

Em REPEATABLE READ (o padrão do MySQL), um UPDATE ... WHERE faixa trava também os gaps — ou seja, trava linhas que não existem. Isso previne leitura fantasma e cria uma classe de deadlock que não existe no Postgres com a mesma carga.

E há uma consequência ainda mais surpreendente:

-- Sem índice em `status`, este UPDATE precisa varrer a tabela
-- e trava PRATICAMENTE TODAS as linhas no caminho.
UPDATE pedidos SET revisado = 1 WHERE status = 'novo';

Falta de índice não deixa a query só lenta: transforma um lock de poucas linhas num lock de tabela inteira. É um dos incidentes mais comuns do MySQL.

Deadlock

O InnoDB detecta ciclos de espera automaticamente e aborta a transação mais "barata", devolvendo ERROR 1213. Isso é diferente de esperar innodb_lock_wait_timeout (50 s por padrão), que é o que acontece quando não há ciclo, só espera.

SHOW ENGINE INNODB STATUS\G   -- seção LATEST DETECTED DEADLOCK
-- e, melhor, para histórico:
SET GLOBAL innodb_print_all_deadlocks = ON;   -- vai para o error log

Trade-offs

Undo em vez de bloat troca um problema por outro. O MySQL não precisa de VACUUM, e em troca: (a) rollback pode ser muito lento (é preciso desfazer tudo, registro a registro), (b) transação longa faz a history list crescer e degrada leitura, (c) o purge thread pode não acompanhar ritmo de escrita muito alto.

REPEATABLE READ padrão é mais seguro para relatório e mais propenso a deadlock. Muitos times mudam para READ COMMITTED (como no Postgres/Oracle) justamente para eliminar gap locks — o que reduz deadlocks e exige revisar qualquer lógica que dependia do snapshot estável.

Detecção de deadlock custa CPU. Em carga altíssima com muitas transações concorrentes, o algoritmo de detecção pode ser um gargalo; innodb_deadlock_detect = OFF o desliga e faz você depender de innodb_lock_wait_timeout. É um ajuste de último recurso, para carga extrema.

O trade-off central: o InnoDB serializa por índice, não por linha. Índice bom é simultaneamente performance e concorrência; índice ausente transforma qualquer UPDATE num evento de contenção global. No MySQL, indexar é uma decisão de concorrência, não só de velocidade.

Exemplo prático

O deadlock clássico e como evitá-lo

-- Transação A                        -- Transação B
BEGIN;                                BEGIN;
UPDATE contas SET s=s-10 WHERE id=1;  UPDATE contas SET s=s-10 WHERE id=2;
UPDATE contas SET s=s+10 WHERE id=2;  UPDATE contas SET s=s+10 WHERE id=1;
--        ↑ espera B                            ↑ espera A  → DEADLOCK (1213)

A correção não é retry (embora retry seja obrigatório): é ordem determinística de aquisição de locks.

-- ✅ sempre trave em ordem crescente de id
BEGIN;
SELECT id FROM contas WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE contas SET s = s - 10 WHERE id = 1;
UPDATE contas SET s = s + 10 WHERE id = 2;
COMMIT;

// E no app, retry é parte do contrato — 1213 é um erro esperado, não um bug
for tentativa := 1; tentativa <= 3; tentativa++ {
	err := transferir(ctx, db, de, para, valor)
	if err == nil { break }
	var me *mysql.MySQLError
	if errors.As(err, &me) && (me.Number == 1213 || me.Number == 1205) { // deadlock / lock wait
		time.Sleep(time.Duration(tentativa*50) * time.Millisecond)
		continue
	}
	return err
}

Diagnosticar locks ao vivo (8.0+)

-- quem espera quem, com a query de cada lado
SELECT r.trx_id AS esperando, r.trx_mysql_thread_id AS thread_esp,
       LEFT(r.trx_query, 60) AS query_esperando,
       b.trx_id AS bloqueando, b.trx_mysql_thread_id AS thread_blq,
       LEFT(b.trx_query, 60) AS query_bloqueando,
       r.trx_wait_started
  FROM performance_schema.data_lock_waits w
  JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
  JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id;

-- quais locks existem agora, e em qual índice (repare: o índice aparece!)
SELECT object_name, index_name, lock_type, lock_mode, lock_status, lock_data
  FROM performance_schema.data_locks LIMIT 20;

-- transações abertas há muito tempo: a raiz de quase tudo
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS segundos,
       trx_rows_locked, LEFT(trx_query, 80)
  FROM information_schema.innodb_trx
 ORDER BY trx_started;

Como isso falha em produção

Aprofundamento aplicado

A ausência de uma linha também pode participar da disputa

No InnoDB, locks de registro, gap e next-key explicam por que uma operação pode bloquear inserções em um intervalo. O comportamento depende do isolamento, índice e tipo de leitura; um SELECT consistente comum não equivale a SELECT ... FOR UPDATE. Referência: tipos de lock.

Cenário: duas rotinas tentam reservar recursos por faixa. Se uma busca percorre um intervalo amplo por falta de índice apropriado, a área disputada pode ser maior do que a aplicação imagina. O SQL lógico não revela sozinho a quantidade de registros examinados e bloqueados.

Exercício: em duas sessões de estudo, use uma tabela indexada por valor e uma leitura bloqueante de intervalo. Tente inserir dentro e fora da faixa a partir da segunda sessão, registrando o isolamento e o plano. Resultado esperado: explique os bloqueios observados pelo caminho de acesso.

Ao ocorrer deadlock, repita a transação de negócio quando apropriado, com limite; repetir cegamente uma instrução pode manter decisões baseadas em estado antigo.

Relacionado

• 2. InnoDB por Dentro
• 4. Índices e o Otimizador
• 5. Replicação e Alta Disponibilidade
• 3. MVCC e Isolamento na Prática
• 4. MVCC, Tuplas e VACUUM
• 6. Transações e ACID

---

Parte de MOC MySQL · InnoDB Locking (docs)