MySQL 5.1 suporta bloqueio em nível de tabela para tabelas MyISAM e MEMORY, bloqueio em nível de página para tabelas BDB e InnoDB tabelas. Em muitos casos, é possível adivinhar com base no treinamento qual tipo de bloqueio é melhor para uma aplicação, mas geralmente é difícil dizer se um determinado tipo de bloqueio é melhor que outro. Tudo depende da aplicação, diferentes partes da aplicação podem exigir diferentes tipos de bloqueio. Para determinar se deseja usar um mecanismo de armazenamento de bloqueio em nível de linha, você deve observar o que seu aplicativo faz e qual combinação de instruções select e update ele usa. Por exemplo, a maioria dos aplicativos da Web executa muitas seleções e poucas exclusões, apenas atualizações de valores-chave e apenas algumas inserções de tabelas específicas. A configuração básica do MySQL MyISAM está bem ajustada.
No MySQL, para mecanismos de armazenamento que usam bloqueio em nível de tabela, não haverá conflito quando a tabela estiver bloqueada. Isso é gerenciado sempre solicitando todos os bloqueios necessários imediatamente no início de uma consulta e sempre bloqueando a tabela na mesma ordem.
Para WRITE, o método de bloqueio de tabela usado pelo MySQL funciona da seguinte maneira:
◆ Se não houver bloqueio na tabela, coloque um bloqueio de gravação nela.
◆ Caso contrário, coloque a solicitação de bloqueio na fila de bloqueio de gravação.
Para READ, o método de bloqueio usado pelo MySQL funciona da seguinte forma:
◆Se não houver bloqueio de gravação na tabela, coloque um bloqueio de leitura nela.
◆ Caso contrário, coloque a solicitação de bloqueio na fila de bloqueio de leitura.
Quando um bloqueio é liberado, ele pode ser adquirido por threads na fila de bloqueio de gravação e, em seguida, por threads na fila de bloqueio de leitura.
Isso significa que se você tiver muitas atualizações em uma tabela, a instrução SELECT aguardará até que não haja mais atualizações.
Se as instruções INSERT não entrarem em conflito, você poderá misturar livremente instruções INSERT e SELECT paralelas para uma tabela MyISAM sem bloqueio.
O InnoDB usa bloqueio de linha e o BDB usa bloqueio de página. Com ambos os mecanismos de armazenamento, impasses são possíveis. Isso ocorre porque o InnoDB obtém automaticamente bloqueios de linha e o BDB obtém bloqueios de página durante o processamento da instrução SQL, e não quando a transação é iniciada.
Vantagens do bloqueio em nível de linha:
· Existem apenas alguns conflitos de bloqueio quando linhas diferentes são acessadas em muitos threads.
· Reverta com apenas um pequeno número de alterações.
· Uma única linha pode ficar bloqueada por um longo período.
Desvantagens do bloqueio em nível de linha:
· Ocupa mais memória do que o bloqueio em nível de página ou tabela.
· Quando usado em grandes porções de uma tabela, é mais lento que o bloqueio em nível de página ou em nível de tabela porque você deve adquirir mais bloqueios.
· Se você executa frequentemente operações GROUP BY na maioria dos seus dados ou precisa verificar frequentemente a tabela inteira, isso será significativamente mais lento do que outros bloqueios.
· Com o bloqueio de alto nível, você também pode dimensionar facilmente seu aplicativo, oferecendo suporte a diferentes tipos de bloqueio, pois o custo do bloqueio é menor do que o bloqueio em nível de linha.
Os bloqueios de tabela têm precedência sobre os bloqueios de nível de página ou de linha quando:
· A maioria das instruções na tabela são usadas para leituras.
· Ler e atualizar com chaves restritas, você pode atualizar ou excluir uma linha que pode ser extraída com uma única chave de leitura:
• UPDATE nome_tabela SET coluna = valor WHERE chave_única_col = valor_chave
• DELETE FROM nome_tabela WHERE chave_única_col = valor_chave
· SELECT combinado com
;instruções INSERT paralelas e muito poucas instruções UPDATE ou DELETE.
· Existem muitas varreduras ou operações GROUP BY em toda a tabela sem nenhuma operação de gravação.
Opções que diferem do bloqueio em nível de linha ou em nível de página:
· Versionamento (por exemplo, a técnica usada no MySQL para inserções paralelas), onde pode haver uma operação de escrita e muitas operações de leitura ao mesmo tempo. Isso significa que o banco de dados ou tabela suporta diferentes visualizações dos dados, dependendo de quando o acesso é iniciado. Outros termos comuns são “rastreamento de tempo”, “cópia por gravação” ou “cópia sob demanda”.
· A replicação sob demanda tem precedência sobre o bloqueio em nível de página ou de linha em muitos casos. No entanto, na pior das hipóteses, pode utilizar mais memória do que o bloqueio normal.
· Além do bloqueio em nível de linha, você pode usar bloqueio em nível de aplicativo, como GET_LOCK() e RELEASE_LOCK() no MySQL. Esses são bloqueios de aconselhamento e só funcionarão em aplicativos que estejam funcionando bem.
Para atingir a velocidade máxima de bloqueio, o MySQL usa bloqueio de tabela (em vez de bloqueio de página, linha ou coluna) para todos os mecanismos de armazenamento, exceto InnoDB e BDB. Para tabelas InnoDB e BDB, o MySQL só usa bloqueio de tabela se você bloquear explicitamente a tabela com LOCK TABLES; se você não usar LOCK TABLES, porque o InnoDB usa bloqueio automático em nível de linha e o BDB usa bloqueio em nível de página para garantir o isolamento da transação.
Mas para tabelas grandes, o bloqueio de tabela é melhor que o bloqueio de linha para a maioria dos aplicativos, mas tem algumas desvantagens. O bloqueio de tabela permite que muitos threads leiam simultaneamente uma tabela, mas se um thread quiser gravar na tabela, ele deverá primeiro obter acesso exclusivo. Durante a atualização, todos os outros threads que desejam acessar a tabela devem aguardar até que a atualização seja concluída.
As atualizações de tabelas são geralmente consideradas mais importantes do que as recuperações de tabelas, por isso recebem maior prioridade. Isso deve garantir que a atividade de atualização de uma tabela não morra de fome, mesmo se houver muita atividade SELECT na tabela.
O bloqueio de tabela pode causar problemas em situações como quando um thread está aguardando porque o disco rígido está cheio e deve haver espaço livre antes que o thread possa ser processado. Neste caso, todas as threads que desejam acessar a tabela em questão também são colocadas em estado de espera até que mais espaço no disco rígido fique disponível.
O bloqueio de tabela também é problemático nas seguintes situações:
· Um cliente emite uma consulta de longa duração.
· Em seguida, outro cliente atualiza a mesma tabela. O cliente deve esperar até que o SELECT seja concluído.
· Outro cliente emite outra instrução SELECT na mesma tabela. Como UPDATE tem uma prioridade mais alta que SELECT, a instrução SELECT aguarda a conclusão de UPDATE e a conclusão do primeiro SELECT.
Alguns métodos são descritos abaixo para evitar ou reduzir a contenção causada por bloqueios de tabelas:
· Tente fazer com que a instrução SELECT seja executada mais rapidamente. Talvez seja necessário criar algumas tabelas de resumo para fazer isso.
· Inicie o mysqld com --low-priority-updates. Isto dará a todas as instruções que atualizam (modificam) uma tabela uma prioridade mais baixa do que as instruções SELECT. Neste caso, a segunda instrução SELECT da situação anterior será executada antes da instrução UPDATE sem esperar a conclusão do primeiro SELECT.
· Você pode usar a instrução SET LOW_PRIORITY_UPDATES=1 para especificar que todas as atualizações em uma conexão específica devem usar baixa prioridade.
· Você pode usar o atributo LOW_PRIORITY para atribuir uma prioridade mais baixa a uma instrução INSERT, UPDATE ou DELETE específica.
· Você pode usar o atributo HIGH_PRIORITY para dar uma prioridade mais alta a uma instrução SELECT específica.
· Inicie o mysqld especificando um valor baixo para a variável de sistema max_write_lock_count para forçar o MySQL a aumentar temporariamente a prioridade de todas as instruções SELECT aguardando por uma tabela após um número específico de inserções ter sido concluído. Isso permite que um bloqueio READ seja fornecido após um certo número de bloqueios WRITE.
· Se você tiver problemas com INSERT combinado com SELECT, passe a usar as novas tabelas MyISAM, pois elas suportam SELECT e INSERT simultâneos.
· Se você misturar inserções e exclusões na mesma tabela, INSERT DELAYED será de grande ajuda.
· Se você tiver problemas ao misturar instruções SELECT e DELETE na mesma tabela, a opção LIMIT de DELETE pode ajudar.
· Usar SQL_BUFFER_RESULT para instruções SELECT pode ajudar a reduzir o tempo de bloqueio da tabela.
· O código de bloqueio em mysys/thr_lock.c pode ser alterado para usar uma única fila. Nesse caso, os bloqueios de gravação e de leitura terão a mesma prioridade, o que pode ser útil para alguns aplicativos.
Aqui estão algumas dicas relacionadas ao bloqueio de tabelas no MySQL:
· Se você não misturar atualizações com seleções que exigem verificação de muitas linhas na mesma tabela, você pode fazer operações paralelas.
· Você pode usar LOCK TABLES para melhorar a velocidade, porque muitas atualizações em um bloqueio são muito mais rápidas do que atualizações sem bloqueios. Dividir o conteúdo da tabela em diversas tabelas também pode ajudar.
· Se você encontrar problemas de velocidade ao bloquear tabelas no MySQL, poderá converter a tabela em uma tabela InnoDB ou BDB para melhorar o desempenho.