Como parte de nosso processo de implantação automatizada para um aplicativo da Web em execução em uma pilha LAMP, descartamos todos os nossos gatilhos e procedimentos armazenados e os recriamos a partir do controle de origem. Acontece que havia um perigo oculto nessa abordagem que não havíamos pensado.
Alguns dias atrás, conseguimos acabar com o banco de dados para (a versão de teste de) nosso aplicativo da web travado em um estado horrivelmente travado após a seguinte sequência de eventos:
- Eu me conecto ao banco de dados remoto de nosso escritório (através do MySQLdb do Python, por acaso) e executo algumas consultas SELECT na tabela Foo.
- Deixo a conexão aberta, porque estou com preguiça.
- Meu chefe faz algumas alterações em seu laptop, envia para o repositório remoto no servidor da web e vai almoçar sem olhar para a saída
- O gancho de implantação no servidor da Web tenta atualizar os gatilhos e os procedimentos armazenados no banco de dados, mas não consegue nem DROP o primeiro gatilho porque o gatilho envolve a tabela Foo, da qual minha conexão atualmente adormecida já havia feito alguns SELECTs.
- Agora ninguém pode SELECT da tabela Foo, porque a conexão que está tentando DROP o gatilho já removeu um bloqueio na tabela Foo que impede qualquer outra conexão de acessar a tabela Foo de qualquer maneira - mesmo que ainda esteja esperando pelo conexão adormecida seja fechada antes que possa realmente fazer qualquer coisa.
- Processos de negócios cruciais que dependem da tabela Foo param, soam alarmes e nosso aplicativo da web para de atender os clientes. Meu chefe fica furioso e declara que cabeças vão rolar se a causa do problema não for encontrada e corrigida para que isso nunca mais aconteça. (Brincadeirinha, era apenas nosso servidor de teste e meu chefe é muito amigável.)
O interessante é que esse cenário não foi causado por nenhum tipo de impasse; foi causado por uma conexão adormecida segurando implicitamente algum tipo de bloqueio que impedia a DROP TRIGGERexecução da instrução, apenas por ter feito um SELECTna mesma tabela anteriormente. Nenhum dos recursos anti-deadlock do MySQL poderia matar automaticamente um processo e salvar a situação, porque no final das contas tudo poderia continuar assim que meu processo original - o ocioso que só fazia SELECTs - fosse morto. O fato de os bloqueios do MySQL se comportarem dessa maneira por padrão parece perverso para mim, mas esse não é o ponto. Estou tentando descobrir uma maneira de garantir que o cenário de desastre descrito acima não volte a ocorrer (especialmente em nosso servidor ativo). Como você sugere que eu faça isso?
Conversamos sobre o problema no escritório e vimos algumas soluções hipotéticas:
Altere algumas configurações em algum lugar para que os processos adormecidos expirem após 10 segundos por padrão, para que um processo adormecido nunca fique bloqueado. Melhor ainda, faça com que eles liberem todos os bloqueios após 10 segundos para que eu ainda possa ir almoçar e deixar meu shell MySQL aberto, ou minha janela Python aberta com uma conexão MySQLdb ativa, depois voltar e usá-lo, sem medo de quebrar nada .
- Isso pode ser realmente irritante ao tentar executar consultas manualmente, especialmente aquelas que exigem agrupamento em uma transação.
Faça alguma mágica nas consultas que tentam substituir os gatilhos e procedimentos armazenados para que a aquisição de bloqueios necessários para os DROPs e CREATEs relevantes seja transformada em uma operação atômica - algo como, se a consulta não puder adquirir todos os bloqueios de que precisa imediatamente em sequência, então ele os libera e tenta novamente periodicamente até funcionar.
- Isso pode fazer com que nosso processo de implantação nunca seja concluído, no entanto, se o banco de dados estiver muito ocupado para conseguir obter todos os bloqueios de uma só vez.
Reduza drasticamente a frequência de consultas de modificação de esquema que fazemos (parece que apenas essas podem ser bloqueadas de iniciar por uma conexão que é feita apenas SELECTs), por exemplo, fazendo com que nosso script de implantação verifique se um procedimento armazenado ou gatilho no controle de origem mudou da versão no banco de dados antes de DROPping e recriar aquela no banco de dados.
- Isso apenas atenua o problema, na verdade não o elimina.
Não temos certeza se alguma das duas primeiras soluções que consideramos são possíveis no MySQL, ou se estamos perdendo uma solução melhor (somos desenvolvedores, não DBAs, e isso está fora de nossa zona de conforto). O que você recomendaria?
Na verdade, esse é totalmente o ponto, porque bloqueios de
SELECTinstruções é algo que o MySQL normalmente não faz ... então, por algum mecanismo ainda desconhecido, você pediu para fazer isso.A explicação mais provável é que você (ou o que quer que esteja usando como cliente, possivelmente não intencionalmente de sua perspectiva) iniciou uma transação que não foi confirmada ou revertida e fez as seleções no contexto dessa transação, ou você desativou o autocommit .
Se tudo o que você fez foram
SELECTdeclarações, esta é a única explicação que posso apresentar, porque fora de uma transação, isso não poderia acontecer e não há outro motivo dentro de uma transação para o InnoDB ter bloqueado os metadados da tabela devido a simplesSELECT. Na verdade, até agora, só consegui duplicar isso usando oSERIALIZABLEnível de isolamento.A maior parte do restante desta discussão assume que você está usando
InnoDB. Se não for esse o caso, estou realmente perdido, porque não há nada sobre umSELECTque possa bloquear uma tabela em um mecanismo de armazenamento não transacional.Entender o que realmente causou esses bloqueios deve aproximá-lo de evitar o problema no futuro.
Você tecnicamente pode fazer isso, mas não faça. Se você fizer isso, perderá qualquer valor real do pool de conexões e absolutamente nunca, jamais, poderá permitir a reconexão automática com segurança, porque descobrirá que as transações que você pensava que estavam abertas desapareceram, os bloqueios que você pensou que mantinha agora estão ausentes, a sessão as variáveis que você pensou em definir agora são
NULL. Não, não tente isso.Felizmente, este é impossível, o que é bom, porque então você não teria nada para dizer se ainda mantém as fechaduras que pensava ter intencionalmente.
Mas nenhuma dessas coisas deve ser uma necessidade se você puder identificar o que seu cliente está fazendo que está causando bloqueios em
SELECT.Seu servidor provavelmente tem a tabela information_schema.innodb_trx , e uma consulta dessa tabela seria um bom teste - embora não à prova de falhas - antes de alternar suas alterações de esquema. Se houver alguma transação, provavelmente você deve esperar até que não haja.
Você quase certamente deve bloquear suas tabelas com
WRITEbloqueios antes de começar a soltar os gatilhos, pois sempre há a possibilidade de que uma consulta possa ser inserida/atualizada/excluída durante o curto período de tempo em que o gatilho desaparece até que você o coloque de volta.Se você bloquear todas as tabelas com uma instrução, ele obterá bloqueios individualmente conforme eles estiverem disponíveis e bloqueará até que todos os bloqueios possam ser obtidos... mesa e, em seguida, prosseguir para a próxima mesa, você deve ser bom, assumindo que suas transações em outras sessões fazem o que as transações devem fazer - entre, trabalhe, saia, não fique por perto - mas em qualquer caso, tudo (lock, drop trigger, create trigger, unlock table) deve ser feito em uma sessão -- na mesma conexão -- onde você obteve o(s) lock(s).
Menos confuso pode ser bloquear uma tabela, fazer alterações e desbloqueá-la novamente.
Há uma frase na documentação sobre bloqueio de tabela e interação de transação que é ambígua:
O "qualquer" nessa frase é enganoso. Não significa qualquer transação no servidor, refere-se apenas a qualquer transação que você tenha ativa na sessão em que emitir o
LOCK TABLESextrato, que não deveria haver.De qualquer forma, essa é realmente uma boa ideia, porque quanto mais você mexe em um sistema ativo, mais provável é que as coisas possam dar errado... inconsistências que podem surgir durante essas minúsculas janelas de tempo, já que uma combinação de
DROPeCREATEnão pode ser feita em conjunto, atomicamente.Isso provavelmente ocorre porque a confirmação automática está desativada por padrão, conforme especificado pelo PEP 249. Isso parece fazer com que qualquer SELECT bloqueie a tabela de metadados. Provavelmente, você pode ativar a confirmação automática (desde que seja seguro com base no código do aplicativo), o que fechará a transação implícita associada ao SELECT imediatamente. Como alternativa, use transações explícitas.