Escalonamento de bloqueios (Mecanismo de Banco de Dados)
O escalonamento de bloqueios é o processo de conversão de vários bloqueios refinados em um número menor de bloqueios de alta granularidade, que reduz a sobrecarga do sistema e, ao mesmo tempo, aumenta a probabilidade de contenção de simultaneidade.
Ao mesmo tempo em que o Mecanismo de banco de dados do SQL Server adquire bloqueios de nível baixo, também coloca bloqueios intencionais nos objetos que contêm os objetos de nível mais baixo:
Ao bloquear linhas ou intervalos de chave de índice, o Mecanismo de Banco de Dados coloca um bloqueio intencional nas páginas que contêm linhas ou chaves.
Ao bloquear as páginas, o Mecanismo de Banco de Dados coloca um bloqueio intencional nos objetos de nível mais alto que contêm as páginas. Além do bloqueio intencional no objeto, são solicitados bloqueios intencionais de página nos seguintes objetos:
Páginas de nível folha dos índices não clusterizados
Páginas de dados de índices clusterizados
Heap de páginas de dados
O Mecanismo de Banco de Dados pode fazer o bloqueio de linha e de página da mesma instrução para minimizar o número de bloqueios e reduzir as probabilidades que o escalonamento de bloqueios necessita. Por exemplo, o Mecanismo de Banco de Dados pode colocar bloqueios de página em um índice não clusterizado (se um número suficiente de chaves contíguas no nó do índice for selecionado para atender a consulta) e bloqueios de linha nos dados.
Para escalonar bloqueios, o Mecanismo de Banco de Dados tenta alterar os bloqueios intencionais na tabela para o bloqueio completo correspondente, por exemplo, altera um bloqueio exclusivo da tentativa (IX) para um bloqueio exclusivo (X) ou um bloqueio de tentativa compartilhada (IS) para um bloqueio compartilhado (S). Se a tentativa de escalonamento de bloqueios tiver êxito e o bloqueio de tabela completo for adquirido, todos os bloqueios de heap ou árvore B, página (PAGE) ou nível de linha (RID) mantidos pela transação no heap ou no índice serão liberados. Se o bloqueio completo não for adquirido, não acontecerá nenhum escalonamento de bloqueios naquele momento e o Mecanismo de Banco de Dados continuará a adquirir bloqueios de linha, chave ou página.
O Mecanismo de Banco de Dados não escalona bloqueios de linha ou intervalo de chave para bloqueios de páginas, mas os escalona diretamente para bloqueios de tabela. Do mesmo modo, os bloqueios de página são sempre escalonados para bloqueios de tabela. No SQL Server 2008, o bloqueio de tabelas particionadas pode escalonar o nível HoBT para a partição associada em vez do bloqueio de tabela. Um bloqueio de nível de HoBT não bloqueia, necessariamente, os HoBTs alinhados para a partição.
.gif "Observação") Observação |
|---|
Os bloqueios de nível de HoBT geralmente aumentam a simultaneidade, mas apresentam o potencial para deadlocks quando as transações que estão bloqueando partições diferentes querem expandir seus bloqueios exclusivos para outras partições. Em poucas instâncias, a granularidade de bloqueio TABLE poderia ter um desempenho melhor. |
Se uma tentativa de escalonamento de bloqueios falhar devido a bloqueios conflitantes mantidos por transações simultâneas, o Mecanismo de Banco de Dados repetirá o escalonamento de bloqueios a cada 1.250 bloqueios adicionais adquiridos pela transação.
Cada evento de escalonamento funciona principalmente no nível de uma única instrução Transact-SQL. Quando o evento é iniciado, o Mecanismo de Banco de Dados tenta escalonar todos os bloqueios que pertencem à transação atual, em qualquer tabela referenciada pela instrução ativa fornecida, desde que atenda os requisitos de limites de escalonamento. Se o evento de escalonamento for iniciado antes de a instrução ter acessado a tabela, não será realizada nenhuma tentativa para escalonar os bloqueios nessa tabela. Se o escalonamento de bloqueios tiver êxito, qualquer bloqueio adquirido pela transação em uma instrução anterior e que ainda seja mantido no momento que o evento for iniciado será escalonado se a tabela for referenciada pela instrução atual e for incluída no evento de escalonamento.
Por exemplo, imagine que uma sessão executa estas operações:
Começa uma transação.
Atualiza TableA. Isso gera bloqueios de linha exclusivos na TableA que são mantidos até que a transação seja concluída.
Atualiza TableB. Isso gera bloqueios de linha exclusivos na TableB que são mantidos até que a transação seja concluída.
Executa SELECT para associar TableA a TableC. O plano de execução de consulta chama as linhas a serem recuperadas da TableA antes que as linhas sejam recuperadas da TableC.
A instrução SELECT aciona o escalonamento de bloqueios enquanto está recuperando as linhas da TableA e antes de acessar a TableC.
Se o escalonamento de bloqueios tiver êxito, somente os bloqueios mantidos pela sessão na TableA serão escalonados. Isso inclui os bloqueios compartilhados da instrução SELECT e os bloqueios exclusivos da instrução UPDATE anterior. Embora somente os bloqueios que a sessão adquiriu na TableA para a instrução SELECT sejam contados para determinar se o escalonamento deve ser realizado, depois que o escalonamento obtiver êxito, todos os bloqueios mantidos pela sessão na TableA serão escalonados para um bloqueio exclusivo na tabela e todos os outros bloqueios de granularidade inferior, incluindo os bloqueios intencionais, serão liberados na TableA.
Não foram realizadas tentativas para escalonar bloqueios na TableB porque não havia referência ativa à TableB na instrução SELECT. Do mesmo modo, não foram realizadas tentativas para escalonar os bloqueios na TableC que não foi escalada porque não havia sido acessada quando ocorreu o escalonamento.
Limites de escalonamento de bloqueios
O escalonamento de bloqueios é acionado quando ele não está desabilitado na tabela ao usar a opção ALTER TABLE SET LOCK_ESCALATION e quando as condições abaixo não existirem:
Uma única instrução Transact-SQL adquire pelo menos 5.000 bloqueios em uma única tabela ou índice não particionados.
Uma única instrução Transact-SQL adquire pelo menos 5.000 bloqueios em uma única partição de uma tabela particionada e a opção ALTER TABLE SET LOCK_ESCALATION está definida como AUTO.
O número de bloqueios em uma instância do Mecanismo de Banco de Dados excede a memória ou os limites de configuração.
Se os bloqueios não puderem ser escalonados devido a conflitos de bloqueios, o Mecanismo de Banco de Dados acionará o escalonamento de bloqueios a cada 1.250 novos bloqueios adquiridos.
Limite de escalonamento para uma instrução Transact-SQL
Quando o Mecanismo de Banco de Dados verifica se há escalonamento possível a cada 1.250 bloqueios recém-adquiridos, o escalonamento de bloqueio ocorre somente se uma instrução Transact-SQL tiver adquirido no mínimo 5.000 bloqueios em uma única referência de tabela. O escalonamento de bloqueio é acionado quando uma instrução Transact-SQL adquire no mínimo 5.000 bloqueios em uma única referência de tabela. Por exemplo, o escalonamento de bloqueios não será acionado se uma instrução adquirir 3.000 bloqueios em um índice e 3.000 bloqueios em outro índice da mesma tabela. Do mesmo modo, o escalonamento de bloqueios não será acionado se uma instrução tiver uma autojunção em uma tabela e cada referência da tabela adquirir 3.000 bloqueios na mesma tabela.
O escalonamento de bloqueios só ocorre em tabelas que foram acessadas no momento em que o escalonamento foi acionado. Suponha que uma única instrução SELECT seja uma junção que acessa três tabelas nesta sequência: TableA, TableB e TableC. A instrução adquire 3.000 bloqueios de linha em um índice clusterizado para a TableA e pelo menos 5.000 bloqueios de linha no índice clusterizado para a TableB, mas ainda não acessou a TableC. Quando o Mecanismo de Banco de Dados detecta que a instrução adquiriu pelo menos 5.000 bloqueios de linha na TableB, tenta escalonar todos os bloqueios mantidos pela transação atual na TableB. Ele também tenta escalonar todos os bloqueios mantidos pela transação atual na TableA, mas como o número de bloqueios na TableA é < 5000, o escalonamento não terá êxito. Nenhum escalonamento de bloqueios foi tentado para a TableC porque ela ainda não havia sido acessada quando o escalonamento ocorreu.
Limite de escalonamento para uma instância do Mecanismo de Banco de Dados
Sempre que o número de bloqueios é maior do que o limite de memória para o escalonamento de bloqueios, o Mecanismo de Banco de Dados aciona o escalonamento de bloqueios. O limite de memória depende da definição da opção de configuração de bloqueios:
Se a opção bloqueios estiver definida para a configuração padrão 0, o limite de escalonamento de bloqueios será atingido quando a memória usada pelos objetos de bloqueio for 24% da memória usada pelo Mecanismo de Banco de Dados, excluindo a memória AWE. A estrutura de dados usada para representar um bloqueio é de aproximadamente 100 bytes. Esse limite é dinâmico porque o Mecanismo de Banco de Dados adquire e libera memória dinamicamente para se ajustar às cargas de trabalho variáveis.
Se a opção bloqueios for um valor diferente de 0, o limite de escalonamento de bloqueios será 40% (ou menos, se houver uma pressão na memória) do valor da opção bloqueios.
O Mecanismo de Banco de Dados pode escolher qualquer instrução ativa de qualquer sessão para o escalonamento e, para cada 1.250 bloqueios novos, escolher as instruções para escalonamento enquanto a memória de bloqueio usada na instância permanecer abaixo do limite.
Escalonando tipos de bloqueio misto
Quando ocorre o escalonamento de bloqueios, o bloqueio selecionado para o heap ou para o índice é forte o suficiente para atender os requisitos de bloqueio de nível inferior mais restritivo.
Por exemplo, imagine que uma sessão:
Começa uma transação.
Atualiza uma tabela contendo um índice clusterizado.
Emite uma instrução SELECT que referencia a mesma tabela.
A instrução UPDATE adquire estes bloqueios:
Bloqueios exclusivos (X) nas linhas de dados atualizadas.
Bloqueios exclusivos da tentativa (IX) nas páginas de índice clusterizado que contêm essas linhas.
Um bloqueio IX no índice clusterizado e outro na tabela.
A instrução SELECT adquire estes bloqueios:
Bloqueios compartilhados (S) em todas as linhas de dados que ela lê, exceto a linha que já está protegida por um bloqueio X de uma instrução UPDATE.
Bloqueios compartilhados intencionais em todas a páginas de índice clusterizado contendo essas linhas, exceto na página que já está protegida por um bloqueio IX.
Nenhum bloqueio no índice clusterizado ou na tabela porque eles já estão protegidos por bloqueios IX.
Se uma instrução SELECT adquirir bloqueios suficientes para acionar o escalonamento de bloqueios e o escalonamento tiver êxito, o bloqueio IX da tabela será convertido para um bloqueio X e todos os bloqueios de linha, de página e de índice serão liberados. As atualizações e as leituras são protegidas pelo bloqueio X na tabela.
Reduzindo bloqueio e escalonamento
Na maioria dos casos, o Mecanismo de Banco de Dados tem um desempenho superior quando trabalha com suas configurações padrão para bloqueio e escalonamento de bloqueios. Se uma instância do Mecanismo de Banco de Dados gerar muitos bloqueios e estiver tendo muitos escalonamentos de bloqueios, você precisará considerar a redução da quantidade de bloqueios fazendo o seguinte:
Usando um nível de isolamento que não gere bloqueios compartilhados para operações de leitura.
O nível de isolamento READ COMMITTED quando a opção de banco de dados READ_COMMITTED_SNAPSHOT está definida como ON.
Nível de isolamento SNAPSHOT.
Nível de isolamento READ UNCOMMITTED. Só pode ser usado para sistemas que funcionam com leituras sujas.
| Observação |
|---|
A alteração do nível de isolamento afeta todas as tabelas na instância do Mecanismo de Banco de Dados. |
Usando as dicas de tabela PAGLOCK ou TABLOCK para fazer o Mecanismo de Banco de Dados usar os bloqueios de página, heap ou índice em vez de bloqueios de linha. No entanto, o uso dessa opção aumenta os problemas de usuários bloqueando outros usuários ao tentarem acessar os mesmos dados. Ela não deve ser usada em sistemas com mais do que alguns usuários simultâneos.
Para tabelas particionadas, use a opção LOCK_ESCALATION de ALTER TABLE para escalonar bloqueios para o nível HoBT em vez da tabela ou desabilite o escalonamento de bloqueios.
Você também pode usar os sinalizadores de rastreamento 1211 e 1224 para desabilitar todos ou alguns escalonamentos de bloqueio. Para obter mais informações, consulte Sinalizadores de rastreamento (Transact-SQL). Além disso, monitore o escalonamento de bloqueios usando o evento SQL Server Profiler Lock:Escalation; e consulte Usando o SQL Server Profiler.
Consulte também