Problemas com Deadlock

[JoaoRodarte 0]

Estou tanto um grande problema em meu banco de dados, devido às seguintes questões:

 

1 - O mesmo está MUITO normalizado, com chaves estrangeiras para tudo quando é lado e muitas tabelas de relacionamento.

2 - Ele está sendo acessado por várias pessoas (cerca de 300) simultaneamente, ou seja: As linhas de suas tabelas são alteradas a todo instante por usuários diferentes.

3 - Rotinas grandes que buscam dados muito específicos. Muito INNER JOIN entre tabelas robustas.

4 - Banco de Dados muito extenso. Mais de 70 tabelas, algumas delas com mais de 10 milhões de linhas.

 

Bom, sei que foi feita muita coisa errada na montagem deste banco e que o mesmo não deveria estar assim, mas como estou sozinho neste projeto, não tenho tempo, saco e nem condições de re-estruturá-lo sozinho.

 

O problema real é: Às vezes updates simples (como UPDATE X SET Y = Y + Z + 1) retornam o erro "Deadlock found when trying to get lock." e isso está causando erros terríveis na integridade do mesmo.

 

Péssima solução adotada por mim: Tentar dar o UPDATE 5 vezes. Assim que o retorno do banco for positivo, parar de tentar.

 

Porém não tem como eu colocar esse WHILE em todos os lugares! Tem alguma variável do banco que permite pré configurar isso?

 

Exemplo tosco para facilitar o entendimento: procuro algo do tipo repeat_query_when_deadlock_is_found = 5.

 

Agradeço desde já a atenção de todos. Peço desculpas pela falta de fluidez do texto e qualquer ajuda ou observação é válida.

[Prog 183]

Dê uma olhada nesse parâmetro:

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog

 

Você esta usando qual versão do MySQL?

[JoaoRodarte 0]

Olá Prog,

 

Meu servidor MySQL está na versão 5.0.95.

 

Poderia me dar uma luz e dizer o que o parâmetro innodb_locks_unsafe_for_binlog faz?

 

Eu estou lendo com atenção às especificações contidas no link que você me enviou mas não tenho certeza se estou compreendendo corretamente.

 

Muito obrigado pela ajuda,

João Rodarte

[Prog 183]

Basicamente... você vai desabilitar o recurso chamado Gap Lock, pode ser que influencie pouco na sua aplicação, mas é o primeiro caminho. O Gap Lock ocorre sempre que um índice é acessado, para evitar resultados inconsistentes.

 

Este artigo pode esclarecer melhor:

http://www.mysqlperformanceblog.com/2012/03/27/innodbs-gap-locks/

 

Pode dar mais detalhes sobre a aplicação?

- É web ou desktop? Qual linguagem?

- Qual componente de acesso aos dados esta utilizando (JDBC, ADO, ODBC, Componente de terceiro, Nativo, ...)?

[Motta 645]

Como funciona o lock de registro no MySql ?

Será que o Sistema não está fazendo locks de forma desnecessária ?

O que seria um sistema

MUITO normalizado

? Normalizar tem este nome não é de graça (opinião pessoal).

[Prog 183]

Pode ser um problema no projeto da aplicação também... não entendo como 2 ou mais pessoas estariam fazendo alterações no mesmo registro com tanta frequência.

Também acabei questionando sobre o componente de conexão utilizado, a fonte do problema pode estar na parametrização deste componente.

 

Alguma literatura relacionada:

http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlock-detection.html

http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

[JoaoRodarte 0]

Trata-se de um jogo em flash para redes sociais cujo endereço é http://www.olemania.net/. Caso sintam necessidade, podem entrar no site e se cadastrar para melhor entender da aplicação.

 

Eu tenho minha equipe, e ela tem uma linha no banco de dados, porém outra pessoa qualquer pode desafiá-la e alterar algumas informações da mesma, como por exemplo o número de partidas, o número de gols de cada um dos meus atletas e outras coisas do tipo.

 

O Flash chama um Gateway que faz comunicação com o MySQL através de arquivos .php que utilizam a classe PDO (O processo é conhecido como AMFPHP).

 

Como o site tem um número extremamente alto de usuários simultâneos, diversas linhas são alteradas no Banco de Dados a todo o momento, e acontece muito de uma mesma linha ser alterada por diversas pessoas ao mesmo tempo (como o número de pessoas inscritas em um campeonato, por exemplo).

 

De qualquer forma, poderiam me explicar quais seriam os possíveis efeitos colaterais de desabilitar o Gap Lock? Sei que eu perderia a garantia de integridade em alguns casos, mas ainda não fui capaz de compreender como essa perda de integridade afetaria o meu banco. Seria ótimo se alguém pudesse me dar um exemplo a respeito deste assunto.

 

Sei que não sou nenhum especialista em MySQL, e é justamente por isso que estou vindo buscar ajuda.

 

Muito obrigado a todos que estão me apoiando, aprender nunca é demais.

 

Atenciosamente,

João Rodarte

[Prog 183]

Joao...

 

O Gap Lock existe para impedir problemas de consistências de informação, como já havia comentado. Num determinado momento você faz um SELECT, UPDATE ou DELETE na tabela pelo ID=12, quando isto ocorre esse registro fica bloqueado para que outros comandos (SELECT, UPDATE ou DELETE) não possam alterar ou exibir uma informação antiga ou que esta sendo apagada, entre outros. Pense numa linha do tempo:

 

HORA     COMANDO
15:18:33 DELETE FROM tabela WHERE ID=12;
15:18:34 SELECT * FROM tabela WHERE ID=12;

 

A resposta esperada deve ser "registro inexistente", afinal, no momento anterior o registro foi excluído, mas se esses comandos ocorrerem num intervalo muito curto entre um e o outro, a ponto do processo de exclusão não ter concluído todos os procedimentos (apagar o registro da tabela e apaga o registro do índice)? Por isso usam o Gap Lock, desta forma evitando informações equivocadas ou procedimento equivocados. Só vale para procedimentos que acessam o índice (WHERE), ou seja, comandos SELECT, UPDATE e DELETE (independente de ordem).

 

O innodb_locks_unsafe_for_binlog=1 desliga esse lock. :)

 

Se você faz muitas operações de UPDATE e SELECT, o que pode acontecer é mostrar uma informação que já foi alterada ainda com o valor antigo, mas para isto ocorrer, o UPDATE e o SELECT precisam acontecer em intervalo de tempo muito curto, ou... as operações do seu banco de dados estarem muito lentas.

 

Todos os Gap Lock serão desabilitados do meu sistema?

Não... O MySQL continua fazendo Gap Lock para outras atividades, como validação de restrições e chave duplicada.

 

Isso vai resolver o meu problema?

Não sei se vai resolver completamente, acredito que vai minimizar.

 

Você tem alguma dica para resolver essa questão sem precisar alterar muito o sistema?

Usar um procedimento assíncrono, por exemplo, para aplicar as alterações de 10 em 10 segundos.