Bloqueios e deadlocks

Uma gravação pode ficar parada com a rede, o disco e os índices funcionando, porque outra transação segura a linha de que ela precisa, ou outro comando ocupa a tabela inteira. Nesse caso, reiniciar o serviço ou reconstruir a tabela não explica a espera e ainda acrescenta uma operação pesada à ocorrência. Identificar quem espera e quem bloqueia é o caminho para resolver com segurança.

Bloqueio, espera e deadlock

• Bloqueio: a reserva de um recurso, como uma linha ou uma tabela, por uma transação ou por um comando, para coordenar o acesso simultâneo.
• Espera: a situação de uma operação que precisa de um recurso reservado por outra e fica parada até a liberação.
• Deadlock: um ciclo de esperas. Duas ou mais transações seguram recursos e esperam umas pelas outras, e nenhuma consegue avançar.

Os bloqueios no InnoDB

O InnoDB bloqueia as linhas usadas por uma gravação até o fim da transação. Os bloqueios são feitos nas entradas dos índices percorridas pelo comando, e por isso um UPDATE cuja condição não tem índice pode bloquear muito mais linhas do que as que altera. Um índice adequado na condição reduz as linhas bloqueadas.

Um SELECT comum não espera os bloqueios de linha, porque lê a versão confirmada da linha. Quem espera são os comandos de alteração e as leituras com FOR UPDATE.

Também existem os bloqueios de metadados, que protegem a definição das tabelas. Um ALTER TABLE espera até que nenhuma transação aberta esteja usando a tabela, e, enquanto espera, as consultas seguintes à mesma tabela entram na fila atrás dele. O limite padrão dessa espera é de um ano, e na prática o ALTER TABLE espera até a transação terminar. Uma única transação esquecida pode, assim, parar todas as telas que usam a tabela. Os bloqueios de metadados existem a partir do MySQL 5.5, e o estado Waiting for table metadata lock não aparece nas versões anteriores, como a 5.1.

Os bloqueios no MyISAM

No MyISAM, o bloqueio é da tabela inteira e dura o tempo do comando, sem relação com as transações. Uma leitura longa, como a de um relatório, faz as gravações na mesma tabela esperarem, e uma gravação na fila faz as leituras seguintes esperarem também. Na prática, a espera dura até o fim do comando que bloqueia, sem um erro de tempo esgotado. Os bloqueios de tabela também não formam deadlocks, porque cada comando reserva de uma vez todas as tabelas de que precisa.

Encontrar quem espera e quem bloqueia

Alguns comandos mostram a situação no servidor e exigem uma conta com permissão para ver as conexões de outros usuários:

• SHOW FULL PROCESSLIST;: mostra cada conexão, com o usuário, o computador de origem, o estado, o tempo no estado e o comando em execução;
• SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age FROM sys.innodb_lock_waits;: mostra, a partir do MySQL 5.7, qual conexão espera, qual conexão bloqueia e há quanto tempo;
• SELECT trx_mysql_thread_id, trx_state, trx_started, trx_query FROM information_schema.INNODB_TRX;: mostra as transações abertas e o momento em que cada uma começou;
• SHOW ENGINE INNODB STATUS;: mostra o estado do InnoDB, com as transações e o último deadlock detectado.

Na lista de conexões, o estado Waiting for table metadata lock indica uma espera por bloqueio de metadados. A espera por bloqueio de linha não tem um estado próprio: o comando aparece em execução, com o tempo crescendo, e o sys.innodb_lock_waits confirma a espera.

A conexão que bloqueia costuma aparecer no estado Sleep, sem nenhum comando em execução. Ela executou uma alteração e ficou sem COMMIT ou ROLLBACK, e a transação continua aberta, com os bloqueios. O tempo mostrado na lista é o tempo desde o último comando, e não a idade da transação, que aparece na coluna trx_started do INNODB_TRX.

Nas versões 5.6 e anteriores, o esquema sys não existe, e a relação entre espera e bloqueio fica nas tabelas INNODB_TRX e INNODB_LOCK_WAITS do information_schema. No MySQL 5.1, essas tabelas só existem quando o InnoDB Plugin está ativo, e, sem ele, a seção TRANSACTIONS do SHOW ENGINE INNODB STATUS mostra as transações abertas e as esperas por bloqueio.

Nas tabelas MyISAM, a própria lista de conexões mostra a espera. Os comandos que esperam aparecem no estado Locked, no MySQL 5.1, ou Waiting for table level lock, nas versões seguintes, e a conexão que bloqueia costuma ser um comando em execução na mesma tabela, iniciado antes dos que esperam. Os comandos do InnoDB e o esquema sys não mostram essas esperas, e, em um servidor sem o InnoDB, o SHOW ENGINE INNODB STATUS falha com o erro 1286, de engine desconhecido.

Uma situação de bloqueio muda de um segundo para outro, e por isso convém registrar o horário e os identificadores das conexões antes de qualquer ação.

Os erros de espera e de deadlock

• Erro 1205, de tempo de espera esgotado: no InnoDB, a instrução esperou um bloqueio de linha além do limite, de 50 segundos por padrão, e foi desfeita. A transação continua aberta, com as alterações anteriores pendentes, e alguém precisa decidir entre confirmar e desfazer.
• Erro 1213, de deadlock: o InnoDB detectou o ciclo e desfez uma das transações por inteiro para liberar as outras. A aplicação precisa repetir do começo a transação desfeita.

Aumentar o limite de espera só faz os usuários esperarem mais. Reiniciar o serviço encerra todas as conexões e desfaz todas as transações abertas, mas não explica por que a transação ficou aberta, e transações grandes podem levar muito tempo para serem desfeitas.

Encerrar uma conexão que bloqueia

• KILL 4821;: encerra a conexão 4821, e o MySQL desfaz a transação aberta dela;
• KILL QUERY 4821;: interrompe apenas o comando em execução, e a transação continua aberta, com os bloqueios.

O número é o identificador da conexão, mostrado na primeira coluna do SHOW FULL PROCESSLIST, e encerrar a conexão de outro usuário exige uma conta com permissão administrativa. Antes do encerramento, convém identificar a origem da conexão, pelo usuário e pelo computador, e o trabalho pendente. Nas tabelas InnoDB, o encerramento desfaz tudo o que a conexão alterou desde o início da transação, e um ROLLBACK grande pode levar tanto tempo quanto as alterações levaram, ou mais. Encerrar a conexão de um caixa no meio de uma venda desfaz a venda, e não a conclui.

Nas tabelas MyISAM, o encerramento não desfaz nada. As linhas que o comando interrompido já tinha alterado continuam alteradas, e uma venda interrompida fica pela metade, com parte das gravações feitas. Por isso, depois do encerramento de uma gravação, os dados que ela atingia precisam ser conferidos. Encerrar uma leitura, como a de um relatório, libera a tabela sem alterar os dados. Encerrar um REPAIR TABLE ou um OPTIMIZE TABLE, ao contrário, deixa a tabela danificada e fora de uso até um novo reparo completo.

Evitar a repetição

A equipe de desenvolvimento reduz os bloqueios com transações curtas, com o acesso às tabelas sempre na mesma ordem e com índices nas condições das alterações. Uma transação não deve ficar aberta à espera de uma tela, de uma resposta da rede ou de uma decisão de alguém. Nas tabelas MyISAM, a medida mais direta é levar os relatórios longos para os horários de pouco movimento.

Um deadlock isolado pode acontecer em uma aplicação correta, com muitas conexões ao mesmo tempo. Os deadlocks frequentes pedem a análise da seção LATEST DETECTED DEADLOCK do SHOW ENGINE INNODB STATUS, que mostra as duas transações e os comandos envolvidos.