Isso surgiu da pergunta relacionada , na qual eu queria saber como forçar duas transações a ocorrer sequencialmente em um caso trivial (onde ambos estão operando em uma única linha). Recebi uma resposta: use SELECT ... FOR UPDATE como a primeira linha de ambas as transações, mas isso gera um problema: Se a primeira transação nunca for confirmada ou revertida, a segunda transação será bloqueada indefinidamente. A variável innodb_lock_wait_timeout define o número de segundos após o qual o cliente que está tentando fazer a segunda transação será informado "Desculpe, tente novamente" ... mas, até onde eu sei, eles estariam tentando novamente até o próximo reinicialização do servidor. Então:
ROLLBACK se uma transação estiver demorando para sempre? Devo recorrer ao uso de um daemon para matar essas transações e, em caso afirmativo, como seria um daemon desse tipo? wait_timeout ou interactive_timeout mid-transaction, a transação será revertida? Existe uma maneira de testar isso no console? Esclarecimento : innodb_lock_wait_timeout define o número de segundos que uma transação aguardará até que um bloqueio seja liberado antes de desistir; o que eu quero é uma forma de forçar um bloqueio a ser lançado.
Atualização 1 : Veja um exemplo simples que demonstra por que innodb_lock_wait_timeout não é suficiente para garantir que a segunda transação não seja bloqueada pela primeira:
START TRANSACTION;
SELECT SLEEP(55);
COMMIT;
Com a configuração padrão de innodb_lock_wait_timeout = 50 , essa transação é concluída sem erros após 55 segundos. E se você adicionar uma UPDATE antes da linha SLEEP , inicie uma segunda transação de outro cliente que tente SELECT ... FOR UPDATE a mesma linha, é a segunda transação que expira, não a que adormeceu.
O que eu estou procurando é uma maneira de forçar o fim do sono repentino dessa transação.
Atualização 2 : Em resposta às preocupações do hobodave sobre quão realista é o exemplo acima, aqui está um cenário alternativo: Um DBA se conecta a um servidor ativo e é executado
START TRANSACTION
SELECT ... FOR UPDATE
em que a segunda linha bloqueia uma linha que o aplicativo grava freqüentemente. Então o DBA é interrompido e vai embora, esquecendo de terminar a transação. O aplicativo fica paralisado até que a linha seja desbloqueada. Gostaria de minimizar o tempo que o aplicativo está preso como resultado desse erro.
Mais da metade deste segmento parece ser sobre como fazer perguntas no ServerFault. Acho que a pergunta faz sentido e é bem simples: como reverter automaticamente uma transação paralisada?
Uma solução, se você estiver disposto a eliminar toda a conexão, é definir wait_timeout / interactive_timeout. Consulte link .
Como sua pergunta é feita aqui no ServerFault, é lógico supor que você está procurando uma solução MySQL para um problema do MySQL, particularmente no domínio do conhecimento do qual um administrador de sistema e / ou um DBA teria conhecimento. , a seção a seguir aborda suas dúvidas:
If the first transaction is never committed or rolled back, then the second transaction will be blocked indefinitely
Não, não vai. Acho que você não está entendendo innodb_lock_wait_timeout . Faz exatamente o que você precisa.
Ele retornará com um erro, conforme indicado no manual:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_timeout segundos. Por padrão, a transação não será revertida. É responsabilidade do código do seu aplicativo decidir como lidar com esse erro, seja tentar novamente ou retroceder.
Se você quiser a reversão automática, isso também é explicado no manual:
The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the
--innodb_rollback_on_timeoutoption.
Primeiro, você declarou em seus comentários que "quis dizer" que deseja uma maneira de expirar a transação primeira que está bloqueando indefinidamente. Isso não é aparente na sua pergunta original e entra em conflito com "Se a primeira transação nunca for confirmada ou revertida, a segunda transação será bloqueada indefinidamente".
No entanto, eu posso responder a essa pergunta também. O protocolo MySQL não possui um "tempo limite de consulta". Isso significa que você não pode expirar a primeira transação bloqueada. Você deve esperar até que seja finalizado ou matar a sessão. Quando a sessão é eliminada, o servidor automaticamente reverta a transação.
A única outra alternativa seria usar ou escrever uma biblioteca mysql que utilize E / S sem bloqueio que permitiria que o aplicativo mate o thread / fork fazendo a consulta após N segundos. A implementação e o uso de tal biblioteca estão além do escopo do ServerFault. Esta é uma pergunta apropriada para o StackOverflow .
Em segundo lugar, você declarou o seguinte em seus comentários:
I was actually more concerned in my question with a scenario in which the client app hangs (say, gets caught in an infinite loop) during the course of a transaction than with one in which the transaction takes a long time on MySQL's end.
Isso não era aparente em sua pergunta original, e ainda não é. Isso só poderia ser discernido depois que você compartilhasse esse detalhe importante no comentário.
Se este é realmente o problema que você está tentando resolver, então eu tenho medo que você tenha perguntado no fórum errado. Você descreveu um problema de programação em nível de aplicativo que requer uma solução de programação, que o MySQL não pode fornecer, e está fora do escopo desta comunidade. Sua resposta mais recente responde à pergunta "Como evito que um programa Ruby seja infinitamente"? Essa pergunta é fora do tópico para essa comunidade e deve ser feita em StackOverflow .
Em uma situação semelhante, minha equipe decidiu usar pt-kill ( link ). Pode relatar ou eliminar consultas que (entre outras coisas) estão sendo executadas por muito tempo. É então possível executá-lo em um cron a cada X minutos.
O problema era que uma consulta que tinha um tempo de execução inesperadamente longo (por exemplo, indefinido) bloqueava um procedimento de backup que tentava limpar tabelas com bloqueio de leitura, que bloqueava todas as outras consultas em "esperando por tabelas". nunca expirou porque eles não estão esperando por um bloqueio, o que resultou sem erros no aplicativo, o que acabou resultando sem alertas para administradores e interrupção de aplicativos.
A correção era, obviamente, para corrigir a consulta inicial, mas para evitar que a situação acontecesse acidentalmente no futuro, seria ótimo se fosse possível eliminar automaticamente (ou relatar) transações / consultas que duram mais que tempo específico, que o autor da pergunta parece estar perguntando. Isso é possível com pt-kill.
Uma solução simples é ter um procedimento armazenado para eliminar as consultas que levam mais tempo do que o tempo timeout necessário e usar a opção innodb_rollback_on_timeout junto com ele.
Depois de pesquisar por algum tempo, parece que não há uma maneira direta de definir um limite de tempo por transação. Aqui está a melhor solução que consegui encontrar:
O comando
SHOW PROCESSLIST;
fornece uma tabela com todos os comandos atualmente em execução e o tempo (em segundos) desde que eles começaram. Quando um cliente parou de fornecer comandos, eles são descritos como dando um comando Sleep , com a coluna Time sendo o número de segundos desde que o último comando foi concluído. Assim, você poderia inserir manualmente e KILL tudo o que tiver um valor de Time em, digamos, 5 segundos, se tiver certeza de que nenhuma consulta deve demorar mais de 5 segundos em seu banco de dados. Por exemplo, se o processo com o id 3 tiver um valor de 12 na coluna Time , você poderia fazer
KILL 3;
A documentação da sintaxe KILL sugere que uma transação que está sendo realizada pelo thread com esse id seria revertido (embora eu não tenha testado isso, então seja cauteloso).
Mas como automatizar isso e matar todos os scripts de horas extras a cada 5 segundos? O primeiro comentário sobre essa mesma página nos dá uma dica, mas o código está em PHP ; parece que não podemos fazer um SELECT on SHOW PROCESSLIST de dentro do cliente MySQL. Ainda assim, supondo que temos um daemon PHP que executamos a cada $MAX_TIME segundos, pode parecer algo assim:
$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
$process_id=$row["Id"];
if ($row["Time"] > $MAX_TIME) {
$sql="KILL $process_id";
mysql_query($sql);
}
}
Observe que isso teria o efeito colateral de forçar que todas as conexões de clientes ociosos sejam reconectadas, não apenas aquelas que estão se comportando mal.
Se alguém tiver uma resposta melhor, adoraria ouvi-la.
Editar: Existe pelo menos um sério risco de usar KILL desta maneira. Suponha que você use o script acima para KILL de todas as conexões inativas por 10 segundos. Após uma conexão ficar inativa por 10 segundos, mas antes que o KILL seja emitido, o usuário cuja conexão está sendo interrompida emite um comando START TRANSACTION . Eles são então KILL ed. Eles enviam um UPDATE e, pensando duas vezes, emitem um ROLLBACK . No entanto, como o KILL recuperou a transação original, a atualização passou imediatamente e não pode ser revertida. Consulte esta postagem para um caso de teste.
Como hobodave sugerido em um comentário, é possível em alguns clientes (embora não, aparentemente, o utilitário de linha de comando mysql ) definir um limite de tempo para uma transação. Aqui está uma demonstração usando o ActiveRecord em Ruby:
require 'rubygems'
require 'timeout'
require 'active_record'
Timeout::timeout(5) {
Foo.transaction do
Foo.create(:name => 'Bar')
sleep 10
end
}
Neste exemplo, a transação expira após 5 segundos e é revertida automaticamente . ( Atualização em resposta ao comentário do hobodave: Se o banco de dados demorar mais de 5 segundos para responder, a transação será revertida assim que ocorrer - não antes.) Se você quisesse garantir que todos seu tempo limite de transações após n segundos, você pode criar um wrapper em torno do ActiveRecord. Eu estou supondo que isso também se aplica às bibliotecas mais populares em Java, .NET, Python, etc, mas eu não testei ainda. (Se você tiver, por favor, envie um comentário sobre esta resposta.)
As transações no ActiveRecord também têm a vantagem de serem seguras se um KILL for emitido, ao contrário das transações feitas na linha de comando. Consulte link .
Não parece ser possível impor um tempo máximo de transação no lado do servidor, exceto por meio de um script como o que publiquei na minha outra resposta.