XOOPS Brasil

 

5.3. Detalhes sobre Locks

5.3.1. Como o MySQL Trava as Tabelas

Você pode encontrar uma discussão sobre diferentes métodos de bloqueios no apêndice. Veja mais informações sobre isto na Seção E.4, “Métodos de Lock”.

Todos os bloqueios no MySQL são livres de deadlock, exceto para tipos de tabela InnoDB e BDB. Isto é gerenciado sempre requisitando todos os bloqueios necessários de uma vez no começo de uma consulta e sempre bloqueando as tabelas na mesma ordem.

Tipos de tabela InnoDB automaticamente adquire seus locks de registro e os tipos de tabela BDB seus locks de páginas, durante o processamento das instruções SQL, e não no início da transação.

O método de bloqueio que o MySQL utiliza para ESCRITA funciona da seguinte forma:

  • Se não existirem travas na tabela, coloque uma bloqueio de escrita na mesma.

  • Caso contrário, coloca a requisição de trava na fila de bloqueios para escrita.

O método de bloqueio que o MySQL utilizado para LEITURA funciona da seguinte maneira:

  • Se não existirem tarvas na tabela, coloca um bloqueio de leitura na mesma.

  • Caso contrário, coloca a requisição de trava na fila de bloqueios para leitura.

Quando um bloqueio é liberado, a trava fica disponível para as threads na fila de bloqueios de escrita, e então para as threads na fila de bloqueios de leitura.

Isto significa que se você possui várias atualizações em uma tabela, instruções SELECT irão esperar até que não existam mais atualizações.

Para contornar este problema no caso onde você precisa fazer várias operações de INSERT e SELECT em uma tabela, você pode inserir registros em uma tabela temporária e atualizar a tabela real com os registros da tabela temporária de uma só vez.

Isto pode ser feito usando o código a seguir:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

Você pode utilizar as opções LOW_PRIORITY com INSERT, UPDATE ou DELETE ou HIGH_PRIORITY com SELECT se você desejar priorizar a recuperação em alguns casos específicos. Também podei-se iniciar o mysqld com --low-priority-updates para obter o mesmo comportamento.

Utilizar SQL_BUFFER_RESULT pode também tornar a criação de locks de tabelas mais curtos.Veja mais informações sobre isto na Seção 6.4.1, “Sintaxe SELECT.

Você também pode alterar o código de bloqueioss no mysys/thr_lock.c para usar uma fila simples. Neste caso, bloqueios de escrita e leitura devem ter a mesma prioridade, o que pode ajudar em algumas aplicações.

5.3.2. Detalhes sobre Lock de Tabelas

O código de bloqueio de tabelas no MySQL é livre de deadlock.

O MySQL utiliza bloqueio de tabelas (no lugar de bloqueio de registros ou colnas) em todos os tipos de tabelas, exceto tabelas BDB, para obter uma alta velocidade nos bloqueios. Para grandes tabelas, bloqueio de tabelas é MUITO melhor que bloqueio de registros para a maioria das aplicações, mas existem, é claro, algumas desvantagens.

Para tabelas BDB e InnoDB, O MySQL só utiliza bloqueio de tabelas se você bloquear explicitamente a tabela com LOCK TABLES ou executar um comando quer irá modificar todos os registros na tabela, como ALTER TABLE. Para estes tipos de tabelas nós recomendamos a você não utilizar LOCK TABLES.

No MySQL versão 3.23.7 ou superior , você pode inserir registros em tabelas MyISAM ao mesmo tempo que outras threads estão lendo da mesma tabela. Perceba que atualmente isto funciona somente se não existirem buracos depois de registros apagados na tabela no momento que a inserção é feita. Quando todos os buracos forem preenchidos com novos dados, inserções concorrentes irão automaticamente ser habilitadas novamente.

O bloqueio de tabelas habilita várias threads para lerem de uma tabela ao mesmo tempo, mas se uma thread desejar escrever a uma tabela, ela primeiramente deve obter acesso exclusivo. Durante a atualização, todas outras threads que desejarem acessar esta tabela em particular irão esperar até que a atualização acabe.

Como atualizações em tabelas normalmente são consideradas mais importantes que SELECT, todas as instruções que atualizam uma tabela tem maior prioridade que instruções que simplesmente recuperam informações. Isto deve garantir que atualizações não fiquem na fila por terem sido passadas várias consultas pesadas em uma tabela específica. (Você pode alterar isto utilizando LOW_PRIORITY com a instrução que faz a atualização ou HIGH_PRIORITY com a instrução SELECT.)

A partir do MySQL versão 3.23.7 pode-se utilizadar a variável max_write_lock_count para forçar o MySQL a fornecer temporariamente a todas as instruções SELECT, que esperam por uma tabela, uma prioridade mais alta depois de um número específico de inserções em uma tabela.

O bloqueio de tabela não é, no entanto, muito bom sobre os seguintes cenários:

  • Um cliente emite uma SELECT que exige muito tempo para ser executada.

  • Outro cliente então executa um UPDATE na tabela usada. Este cliente terá que esperar até que a SELECT seja terminada.

  • Outro cliente executa outra instrução SELECT na mesma tabela. Como UPDATE tem maior prioridade que SELECT, esta SELECT irá esperar pelo término da UPDATE. Ela também irá esperar pelo término da primeira SELECT!

  • Uma thread está esperando por algo do tipo disco cheio, caso em que todas as threads que desejam acessar a tabela com problema irão ser colocadas em estado de espera até que mais espaço em disco seja disponível.

Algumas soluções possíveis para este problema são:

  • Tente deixar suas instruções SELECT sempre rápidas. Você pode ter que criar algumas tabelas de resumo para fazer isto.

  • Inicie o mysqld com --low-priority-updates. Isto irá fornecer a todas instruções que atualizam (modificam) uma tabela prioridade menor que uma instrução SELECT. Neste caso a última instrução SELECT no cenário anterior deveria executar antes da instrução INSERT.

    Você pode fornecer a uma instrução INSERT, UPDATE ou DELETE específica menor prioridade com o atributo LOW_PRIORITY.

  • Inicie o mysqld com um valor baixo para max_write_lock_count para fornecer bloqueios de LEITURA depois de um certo número de bloqueios de ESCRITA.

  • Você pode especificar que todas as atualizações de uma thread específica deve ser feita utilizando prioridade baixa com o comando SQL: SET SQL_LOW_PRIORITY_UPDATES=1. Veja mais informações sobre isto na Seção 5.5.6, “Sintaxe de SET.

  • Você pode especificar que uma SELECT específica é muito importante com o atributo HIGH_PRIORITY. Veja mais informações sobre isto na Seção 6.4.1, “Sintaxe SELECT.

  • Se você tiver problemas com INSERT combinado com SELECT, utilize as novas tabelas MyISAM, pois elas suportam SELECTs e INSERTs concorrentes.

  • Se você utiliza principalmente instruções INSERT e SELECT misturadas, o atributo DELAYED no INSERT provavelmente irá resolver seus problemas. Veja mais informações sobre isto na Seção 6.4.3, “Sintaxe INSERT.

  • Se você tiver problemas com SELECT e DELETE, a opção LIMIT para DELETE pode ajudar. Veja mais informações sobre isto na Seção 6.4.5, “Sintaxe DELETE.