Locks no MySQL

Ola pessoal,

Depois de falarmos sobre transações e níveis de isolamento, vamos fechar este assunto com os conceitos de bloqueio utilizados no MySQL.

O MySQL usa uma arquitetura multi-thread para gerenciar as conexões a todos os usuários, onde cada conexão tera uma thread como um controlador. O bloqueio adotado pelo MySQL impede a ocorrência de problemas com dados acessados de forma simultânea por vários clientes, onde uma conexão não ira interferir na outra.

Quando varios clientes tentam manipular o mesmo dado, o MySQL atribui um bloqueio a um cliente com base na thread, onde enquanto este bloqueio não seja liberado, os outros clientes não conseguem manipular o mesmo dado, ou seja, cada um tem a sua vez.

O efeito do mecanismo de bloqueio é serializar o acesso aos dados, onde clientes que tentam efetuar operações conflitantes deverão aguardar a sua vez.

Mas nem todo o tipo de acesso gera um conflito, um exemplo:

– Caso a conexão “A” e “B” precisem ler algum dado, isso não irá gerar um conflito e os dois poderão ler ao mesmo tempo.

– Caso a conexão “A” inicie uma leitura e a “B” precise gravar um novo dado, a conexão “B” deverá aguardar a leitura da conexão “A” ser finalizada, para assim conseguir gravar o novo dado.

– Agora se a conexão “A” iniciar uma gravação de dados, todas as conexões abaixo deverão aguardar a gravação ser concluida, seja ela uma conexão de leitura ou gravação.

Uma operação de leitura deve bloquear uma operação de gravação, porém uma operação de gravação deve bloquear as demais seja leitura ou gravação até a sua finalização

Tipos de bloqueio:

— Implicito

Os bloqueios sobre os dados podem ser de forma implícita, explícita ou com advisory locks.

Caso você trabalhe com o autocommit habilitado no MySQL, o servidor adquiri bloqueios implicitamente para cada comando, e cancela o mesmo após a conclusão.

Exemplo: Um Select atribui um lock de escrita para um Insert, onde após a sua finalização, este Lock é desfeito com um Unlock tables interno.

— Explicito

Existe também a opção de bloqueios explicitos, onde você pode efetuar um lock nas tabelas e após terminar a operação, ‘libera-la’ com o unlock tables.

Exemplo:

Advisory Locking

Um tipo de bloqueio que não é gerenciado pelo servidor MySQL, onde este é trabalhado diretamente em clientes de conexão a partir de funções dos mesmos.

Bloqueio Explicito de linhas InnoDB

Em outros posts já citamos que o nível de bloqueio do InnoDB é através da própria linha e não da tabela como o MyISAM. Porém tambem podemos implementar o bloqueio explícito de linhas no InnoDB, para isso, temos que adicionar dois modificadores no final das instruções de SELECT.

– Lock in Share Mode

Este modo bloqueia a linha a um nível compartilhado, onde outras conexões conseguem ler todas as linhas, porém nenhuma vai conseguir editar os dados. Porém caso já exista uma transação em andamento que só tenha alterado o dado e não o tenha commitado.

Ex:

– For Update

Este modo bloqueia todas as linhas selecionadas com bloqueio exclusivo, impedindo qualquer update, insert, algum modulo de bloqueio, até mesmo o Lock in Share Mode. Impede também algum acesso de leitura dependendo do nível de isolamento da instância.

Para liberar ambos os módulos, basta efetuar um commit ou um rollback na transação atual.

Para mais informações sobre estes níveis de bloqueio do InnoDB, recomendo a leitura da documentação do MySQL:

https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html

– Deadlocks

Apesar de ser um problema clássico, os Deadlocks não são perigosos, porém devem ser tratados principalmente pela aplicação com o preparo de reexecutar a transação caso esse evento ocorra.

Se não for levado a serio, o problema podera chegar a um ponto de não executar certas transações.

Os Deadlocks ocorrem quando:

– As transações adquirem bloqueios para diversas tabelas em ordem oposta.
– Várias transações precisam dos dados que outras transações já estão utilizando.

Imagine que estão disponíveis 3 garrafas, onde você sendo a transação “A” pega duas garrafas e outra transação “B” pegue uma garrafa. A transação “A” precisa da terceira garrafa lockada na transação “B” para terminar a transação, enquanto a transação “B” precisa apenas de uma garrafa que esta em lock da transação “A” para finalizar.

Dai acontece o Dead lock, onde o banco de dados(InnoDB) irá finalizar uma transação com rollback para que a outra seja concluída.

– É possível saber qual transação sofrerá o Deadlock?

O próprio InnoDB trabalha com os recursos para identificar o Dead Lock e aplicar o rollback em uma das transações, no caso o rollback é executado na menor transação (Que movimenta um menor numero de bytes).

Caso o InnoDB efetue um rollback completo na transação, todos os bloqueios atribuidos a ela são liberados. Porém pode ocorrer do rollback ser efetuado apenas em uma instrução SQL invocada da transação, logo alguns bloqueios definidos poderão ser preservados.

Isso ocorre porque o InnoDB armazena os bloqueios de linha em um formato de modo que ele não consegue identificar qual bloqueio foi definido por qual intrução. A forma de como o InnoDB trabalha com os Deadlocks é bem complexo, por isso também recomendo a leitura abaixo:

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

Apesar de alguns modos serem semelhantes, cada um tem sua particularidade, por isso é importante saber diferencia-los.

Referencias utilizadas para transcrever este post:

http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlock-detection.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html
#http://pt.slideshare.net/Wagnerbianchi/7-my-sql-56-transacoes

É isso ai pessoal.

Dúvidas, criticas ou sugestões? Fiquem a vontade, todo retorno é construtivo 🙂

Jose Wilson

Deixe uma resposta