Personalizando bloqueio de um índice

O Mecanismo de Banco de Dados do SQL Server usa uma estratégia de bloqueio dinâmico que escolhe automaticamente a melhor granularidade de bloqueio para as consultas, na maioria dos casos. É recomendável substituir os níveis de bloqueio padrão que têm bloqueio de página e de linha ativado, a não ser que os padrões de acesso a tabela e ao índice sejam bem compreendidos e consistentes e haja um problema de contenção de recursos a ser resolvida. A substituição de um nível de bloqueio pode impedir significativamente o acesso simultâneo a uma tabela ou índice. Por exemplo, a especificação de apenas bloqueios em nível de tabela em uma tabela grande que os usuários acessam excessivamente pode provocar afunilamentos, porque os usuários precisam esperar que o bloqueio em nível de tabela seja liberado para acessar a tabela.

Há alguns casos em que a desabilitação do bloqueio de página ou de linha poderá ser benéfico, se os padrões de acesso forem bem entendidos e consistentes. Por exemplo, um aplicativo de banco de dados usa uma tabela de pesquisa que é atualizada semanalmente em um processo em lotes. Leitores simultâneos acessam a tabela com um bloqueio compartilhado (S) e a atualização em lotes semanal acessa a tabela com um bloqueio exclusivo (X). A desativação do bloqueio de página e de linha na tabela reduz a sobrecarga do bloqueio durante a semana permitindo que os leitores acessem a tabela simultaneamente por meio de bloqueios de tabelas compartilhado. Quando o trabalho em lotes é executado, ele pode concluir a atualização de maneira eficiente porque obtém um bloqueio de tabela exclusivo.

A desativação do bloqueio de página e de linha pode ou não ser aceitável porque a atualização semanal em lotes bloqueia o acesso dos leitores simultâneos à tabela enquanto a atualização está em execução. Se o trabalho em lotes só alterar algumas linhas ou páginas, você poderá alterar o nível de bloqueio para permitir bloqueio em nível de linha ou de página, o que permite que outras seções leiam a tabela sem bloqueio. Se o trabalho em lotes tiver um grande número de atualizações, obter um bloqueio exclusivo na tabela poderá ser a melhor maneira de garantir que o trabalho em lotes seja concluído de maneira eficiente.

Ocasionalmente um deadlock ocorre quando duas operações concorrentes adquirirem bloqueios de linha na mesma tabela e então bloqueiam a página porque ambas precisam bloquear a página. A desabilitação de bloqueios de linha força uma operação a esperar, evitando o deadlock.

A granularidade de bloqueio usada em um índice pode ser definida usando as instruções CREATE INDEX e ALTER INDEX. As configurações de bloqueio se aplicam a páginas de índice e a páginas de tabela. Além disso, podem ser usadas as instruções CREATE TABLE e ALTER TABLE para definir a granularidade de bloqueio nas restrições PRIMARY KEY e UNIQUE. Para compatibilidade com versões anteriores, o procedimento de sistema armazenado de sp_indexoption pode também definir a granularidade. Para exibir a opção atual de bloqueio para um determinado índice, use a função INDEXPROPERTY. Podem não ser permitidos bloqueios no nível de página, no nível de linha ou uma combinação de bloqueios no nível de página e no nível de linha, para um determinado índice.