O bloqueio é essencial para o sucesso do processamento de transações do SQL Server e foi projetado para permitir que o SQL Server funcione perfeitamente em um ambiente multiusuário. O travamento é a forma como o SQL Server gere a simultaneidade das transacções. Essencialmente, os bloqueios são estruturas in-memory que têm proprietários, tipos, e o hash do recurso que deve proteger. Um bloqueio como uma estrutura in-memory é de 96 bytes em tamanho.
Para entender melhor o bloqueio no SQL Server, é importante entender que o bloqueio é projetado para garantir a integridade dos dados no banco de dados, já que força cada transação do SQL Server a passar no teste ACID.
O teste ACID consiste em 4 requisitos que cada transação tem que passar com sucesso:
- Atomicidade – requer que uma transação que envolve duas ou mais partes discretas de informação deve submeter todas as partes ou nenhuma
- Consistência – requer que uma transação deve criar um estado válido de novos dados, ou deve reverter todos os dados para o estado que existia antes que a transação fosse executada
- Isolamento – requer que uma transação que ainda está em execução e ainda não submeteu todos os dados, deve permanecer isolado de todas as outras transações
- Durabilidade – requer que os dados comprometidos sejam armazenados usando um método que irá preservar todos os dados em estado correto e disponíveis para um usuário, mesmo em caso de falha
Bloqueio do SQL Server é a parte essencial do requisito de isolamento e serve para bloquear os objetos afetados por uma transação. Enquanto os objetos são bloqueados, o SQL Server irá impedir que outras transações façam qualquer alteração dos dados armazenados nos objetos afetados pelo bloqueio imposto. Uma vez que o bloqueio é liberado ao submeter as alterações ou ao reverter as alterações ao estado inicial, outras transações serão permitidas para fazer as alterações de dados necessárias.
Traduzido para a linguagem do SQL Server, isto significa que quando uma transação impõe o bloqueio a um objeto, todas as outras transações que requerem o acesso a esse objeto serão forçadas a esperar até que o bloqueio seja liberado e essa espera será registrada com o tipo de espera adequado
Os bloqueios do SQL Server podem ser especificados através dos modos de bloqueio e granularidade do bloqueio
Modos de bloqueio
Modo de bloqueio considera vários tipos de bloqueio que podem ser aplicados a um recurso que tem que ser bloqueado:
- Exclusivo (X)
- Partilhado (S)
- Actualização (U)
- Intenção (I)
- Esquema (Sch)
- Actualização a granel (BU)
Fechadura exclusiva (X) – Este tipo de fechadura, quando imposta, garantirá que uma página ou linha será reservada exclusivamente para a transação que impôs o bloqueio exclusivo, desde que a transação detenha o bloqueio.
O bloqueio exclusivo será imposto pela transação quando desejar modificar os dados da página ou linha, que é no caso de declarações DELETE, INSERT e UPDATE. Um bloqueio exclusivo só pode ser imposto a uma página ou linha se não houver outro bloqueio partilhado ou exclusivo já imposto ao alvo. Isto praticamente significa que apenas um bloqueio exclusivo pode ser imposto a uma página ou linha, e uma vez imposto, nenhum outro bloqueio pode ser imposto aos recursos bloqueados
Travamento compartilhado (S) – este tipo de travamento, quando imposto, irá reservar uma página ou linha para estar disponível apenas para leitura, o que significa que qualquer outra transação será impedida de modificar o registro bloqueado enquanto o bloqueio estiver ativo. Entretanto, um bloqueio compartilhado pode ser imposto por várias transações ao mesmo tempo na mesma página ou linha e, dessa forma, várias transações podem compartilhar a capacidade de leitura de dados, uma vez que o processo de leitura em si não afetará de forma alguma os dados reais da página ou linha. Além disso, um bloqueio compartilhado permitirá operações de escrita, mas nenhuma alteração DDL será permitida
Update lock (U) – este bloqueio é similar a um bloqueio exclusivo, mas foi projetado para ser mais flexível de certa forma. Um bloqueio de atualização pode ser imposto a um registro que já possui um bloqueio compartilhado. Nesse caso, o bloqueio de atualização imporá outro bloqueio compartilhado na linha de destino. Quando a transação que mantém o bloqueio de atualização estiver pronta para alterar os dados, o bloqueio de atualização (U) será transformado em um bloqueio exclusivo (X). É importante entender que o bloqueio de atualização é assimétrico em relação aos bloqueios compartilhados. Enquanto o bloqueio de atualização pode ser imposto a um registro que possui o bloqueio compartilhado, o bloqueio compartilhado não pode ser imposto ao registro que já possui o bloqueio de atualização
Travas internas (I) – este bloqueio é um meio utilizado por uma transação para informar outra transação sobre sua intenção de adquirir um bloqueio. O objetivo de tal bloqueio é assegurar que a modificação de dados seja executada corretamente, impedindo que outra transação adquira um bloqueio no objeto seguinte na hierarquia. Na prática, quando uma transação deseja adquirir um bloqueio na linha, adquire um bloqueio de intenção em uma tabela, que é um objeto de hierarquia superior. Ao adquirir o bloqueio de intenção, a transação não permitirá que outras transações adquiram o bloqueio exclusivo naquela tabela (caso contrário, o bloqueio exclusivo imposto por alguma outra transação cancelaria o bloqueio de linha).
Este é um tipo de bloqueio importante do aspecto de desempenho, pois o mecanismo de banco de dados do SQL Server inspecionará os bloqueios de intenção somente no nível da tabela para verificar se é possível que a transação adquira um bloqueio de forma segura naquela tabela e, portanto, o bloqueio de intenção elimina a necessidade de inspecionar cada bloqueio de linha/página em uma tabela para garantir que a transação possa adquirir o bloqueio em toda a tabela
Existem três bloqueios de intenção regulares e três os chamados bloqueios de conversão:
Travas de intenção regular:
Intent exclusive (IX) – quando um bloqueio com intenção exclusiva (IX) é adquirido indica ao SQL Server que a transação tem a intenção de modificar alguns dos recursos hierárquicos inferiores adquirindo bloqueios exclusivos (X) individualmente nesses recursos hierárquicos inferiores
Intent shared (IS) – quando um bloqueio com intenção compartilhada (IS) é adquirido indica ao SQL Server que a transação tem a intenção de ler alguns recursos inferiores da hierarquia através da aquisição de bloqueios compartilhados (S) individualmente nos recursos inferiores da hierarquia
Atualização da intenção (IU) – quando um bloqueio compartilhado (IS) é adquirido indica ao SQL Server que a transação tem a intenção de ler alguns dos recursos inferiores da hierarquia através da aquisição de bloqueios compartilhados (S) individualmente nos recursos inferiores da hierarquia. O intent update lock (IU) pode ser adquirido somente no nível da página e assim que a operação de atualização ocorre, ele converte para o intent exclusive lock (IX)
Conversion lock:
Shared with intent exclusive (SIX) – quando adquirido, este lock indica que a transação pretende ler todos os recursos em uma hierarquia inferior e assim adquirir o bloqueio compartilhado em todos os recursos inferiores na hierarquia e, por sua vez, modificar parte deles, mas não todos. Ao fazer isso, ela adquirirá um bloqueio exclusivo (IX) de intenção nos recursos da hierarquia inferior que devem ser modificados. Na prática, isto significa que uma vez que a transação adquira um bloqueio SIX na tabela, adquirirá um bloqueio exclusivo de intenção (IX) nas páginas modificadas e um bloqueio exclusivo (X) nas linhas modificadas.
Apenas uma transação compartilhada com bloqueio exclusivo de intenção (SIX) pode ser adquirida em uma tabela de cada vez e ela bloqueará outras transações de fazer atualizações, mas não impedirá outras transações de ler os recursos de hierarquia inferior que podem adquirir o bloqueio de intenção compartilhada (IS) na tabela
Partilhado com atualização de intenção (SIU) – este é um bloqueio um pouco mais específico, pois é uma combinação dos bloqueios compartilhados (S) e de atualização de intenção (IU). Um exemplo típico deste bloqueio é quando uma transação está usando uma consulta executada com a dica e consulta PAGELOCK, depois a consulta de atualização. Após a transação adquirir um bloqueio SIU na tabela, a consulta com a dica PAGELOCK adquirirá o bloqueio compartilhado (S) enquanto a consulta de atualização adquirirá o bloqueio de atualização de intenção (IU)
Update with intent exclusive (UIX) – quando o bloqueio de atualização (U) e o bloqueio de intenção exclusiva (IX) são adquiridos em recursos hierárquicos inferiores na tabela simultaneamente, a actualização com bloqueio exclusivo de intenção será adquirida ao nível da tabela como consequência
Fechaduras de Esquema (Sch) – O motor de base de dados do SQL Server reconhece dois tipos de fechaduras de esquema: Bloqueio de modificação de esquema (Sch-M) e bloqueio de estabilidade de esquema (Sch-S)
- Um bloqueio de modificação de esquema (Sch-M) será adquirido quando uma instrução DDL for executada, e impedirá o acesso aos dados do objeto bloqueado quando a estrutura do objeto estiver sendo modificada. O SQL Server permite um único bloqueio de modificação de esquema (Sch-M) em qualquer objeto bloqueado. A fim de modificar uma tabela, uma transação deve esperar para adquirir um bloqueio Sch-M no objeto de destino. Uma vez adquirido o bloqueio de modificação de esquema (Sch-M), a transação pode modificar o objeto e, após a conclusão da modificação, o bloqueio será liberado. Um exemplo típico do bloqueio Sch-M é uma reconstrução de índice, já que uma reconstrução de índice é um processo de modificação de tabela. Assim que o ID de reconstrução do índice for emitido, um bloqueio de modificação de esquema (Sch-M) será adquirido nessa tabela e só será liberado após a conclusão do processo de reconstrução do índice (quando usado com a opção ONLINE, a reconstrução do índice adquirirá o bloqueio Sch-M logo no final do processo)
- Um bloqueio de estabilidade de esquema (Sch-S) será adquirido enquanto uma consulta dependente de esquema estiver sendo compilada e executada e um plano de execução for gerado. Esse bloqueio em particular não bloqueará outras transações para acessar os dados do objeto e é compatível com todos os modos de bloqueio, exceto com o bloqueio de modificação de esquema (Sch-M). Essencialmente, os bloqueios de estabilidade do esquema serão adquiridos por cada DML e seleção de consulta para garantir a integridade da estrutura da tabela (garantir que a tabela não mude enquanto as consultas estiverem sendo executadas).
Bulk Update locks (BU) – este bloqueio foi projetado para ser usado por operações de importação a granel quando emitido com um argumento/dica TABLOCK. Quando um bloqueio de atualização em massa é adquirido, outros processos não poderão acessar uma tabela durante a execução da carga a granel. Entretanto, um bloqueio de atualização a granel não impedirá que outra carga a granel seja processada em paralelo. Mas tenha em mente que a utilização do TABLOCK em uma tabela de índice agrupada não permitirá a importação paralela em bloco. Mais detalhes sobre isso estão disponíveis em Guidelines for Optimizing Bulk Import
Locking hierarchy
SQL Server introduziu a hierarquia de bloqueio que é aplicada quando a leitura ou alteração dos dados é realizada. A hierarquia de bloqueio começa com o banco de dados no nível hierárquico mais alto e desce através de tabela e página até a linha no nível mais baixo
Essencialmente, há sempre um bloqueio compartilhado no nível do banco de dados que é imposto sempre que uma transação é conectada a um banco de dados. O bloqueio compartilhado no nível da base de dados é imposto para evitar a queda da base de dados ou a restauração de uma cópia de segurança da base de dados sobre a base de dados em uso. Por exemplo, quando uma instrução SELECT é emitida para ler alguns dados, um bloqueio compartilhado (S) será imposto no nível da base de dados, um bloqueio intencional compartilhado (IS) será imposto na tabela e no nível da página, e um bloqueio compartilhado (S) na própria linha
No caso de uma instrução DML (ou seja inserir, atualizar, excluir) um bloqueio compartilhado (S) será imposto no nível da base de dados, um bloqueio exclusivo (IX) ou bloqueio de atualização intencional (IU) será imposto na tabela e no nível da página, e um bloqueio exclusivo ou de atualização (X ou U) na linha
Os bloqueios serão sempre adquiridos de cima para baixo, pois dessa forma o SQL Server está impedindo a ocorrência da chamada condição de Corrida.
Agora que os modos de bloqueio e a hierarquia de bloqueios foram explicados, vamos aprofundar mais sobre os modos de bloqueio e como estes se traduzem em uma hierarquia de bloqueios.
Nem todos os modos de bloqueio podem ser aplicados em todos os níveis.
No nível da linha, os três seguintes modos de bloqueio podem ser aplicados:
- Exclusivo (X)
- Partilhado (S)
- Actualização (U)
Para compreender a compatibilidade desses modos, consulte a tabela seguinte:
| Exclusivo (X) | Partilhado (S) | Actualização (U) | |
| Exclusivo (X) | ✗ | ✗ | ✗ |
| Partilhado (S) | ✗ | ✓ | ✓ |
| > Actualização (U) | ✗ | ✓ | ✗ |
✓ – Compatível ✗ – Incompatível
Ao nível da tabela, há cinco tipos diferentes de fechaduras:
- Exclusivo (X)
- Partilhado (S)
- Exclusivo (IX)
- Partilhado com intenção exclusiva (SI)
- Partilhado com intenção exclusiva (SIX)
A compatibilidade destes modos pode ser vista na tabela abaixo
| (X) | (S) | (IX) | (IS) | (SEIS) | ||
| (X) | ✗ | ✗ | ✗ | ✗ | ✗ | |
| (S) | ✗ | ✓ | ✗ | ✓ | ✗ | |
| (IX) | ✗ | ✗ | ✓ | ✓ | ✗ | |
| (IS) | ✗ | ✓ | ✓ | ✓ | ✓ | ✓ |
| (SEIS) | ✗ | ✗ | ✗ | ✓ | ✓ | ✗ |
✓ – Compatível ✗ – Incompatível
Um esquema de bloqueio (Sch) também é um bloqueio de nível de tabela, mas não é um bloqueio relacionado a dados
Para entender melhor a compatibilidade entre estes tipos de bloqueio, por favor consulte esta tabela:
Escalonamento de bloqueio
Para evitar uma situação em que o bloqueio esteja a utilizar demasiados recursos, o SQL Server introduziu o recurso de escalonamento de bloqueio.
Sem escalonamento, os bloqueios podem requerer uma quantidade significativa de recursos de memória. Vamos tomar um exemplo onde um bloqueio deve ser imposto nas 30.000 linhas de dados, onde cada linha tem 500 bytes de tamanho, para realizar a operação de exclusão. Sem escalonamento, será imposto um bloqueio compartilhado (S) na base de dados, 1 bloqueio exclusivo de intenção (IX) na tabela, 1.875 bloqueios exclusivos de intenção (IX) nas páginas (8KB página contém 16 linhas de 500 bytes, o que faz com que 1.875 páginas contenham 30.000 linhas) e 30.000 bloqueios exclusivos (X) nas próprias linhas. Como cada cadeado tem 96 bytes de tamanho, 31.877 bloqueios ocupam cerca de 3 MB de memória para uma única operação de eliminação. Executar um grande número de operações em paralelo pode requerer alguns recursos significativos apenas para garantir que o gerenciador de travamento possa realizar a operação sem problemas
Para evitar tal situação, o SQL Server utiliza o escalonamento de travamento. Isto significa que numa situação em que mais de 5.000 bloqueios são adquiridos num único nível, o SQL Server irá escalar esses bloqueios para um único nível de bloqueio de tabela. Por padrão, o SQL Server sempre irá escalar para o nível da tabela diretamente, o que significa que o escalonamento para o nível da página nunca ocorre. Ao invés de adquirir numerosas linhas e páginas bloqueadas, o SQL Server irá escalar para o bloqueio exclusivo (X) em um nível de tabela
Enquanto isso reduzirá a necessidade de recursos, bloqueios exclusivos (X) em uma tabela significam que nenhuma outra transação será capaz de acessar a tabela bloqueada e todas as consultas que tentarem acessar essa tabela serão bloqueadas. Portanto, isto reduzirá a sobrecarga do sistema, mas aumentará a probabilidade de contenção da concorrência
A fim de fornecer controle sobre o escalonamento, começando com o SQL Server 2008 R2, a opção LOCK_EXCALATION é introduzida como parte da declaração da TABELA DE ALTERAÇÃO
Cada uma dessas opções é definida para permitir o controle específico sobre o processo de escalonamento de bloqueio:
Table – Esta é a opção padrão para qualquer tabela recém criada, pois por padrão o SQL Server sempre executará o escalonamento de lock para o nível da tabela, que também inclui tabelas particionadas
Auto – Esta opção permite o escalonamento de lock para um nível de partição quando uma tabela é particionada. Quando 5.000 travamentos são adquiridos em uma única partição, o escalonamento de travamento irá adquirir um travamento exclusivo (X) nessa partição enquanto a tabela irá adquirir travamento exclusivo intencional (IX). Caso essa tabela não seja particionada, o lock escalation adquirirá o lock no nível da tabela (igual à opção Tabela).
Embora pareça uma opção muito útil, tem de ser usada com muito cuidado pois pode facilmente causar um deadlock. Numa situação em que temos duas transações em duas partições onde o bloqueio exclusivo (X) é adquirido, e as transações tentam acessar a data da partição utilizada por outra transação, um deadlock será encontrado
Assim, é muito importante controlar cuidadosamente o padrão de acesso aos dados, se esta opção estiver ativada, o que não é fácil de conseguir, e é por isso que esta opção não é a configuração padrão no SQL Server
Disable – Esta opção irá desabilitar completamente a escalação de bloqueio para uma tabela. Mais uma vez, esta opção deve ser usada cuidadosamente para evitar que o gerenciador de bloqueio do SQL Server seja forçado a usar uma quantidade excessiva de memória
Como pode ser visto, o escalonamento de bloqueio pode ser um desafio para os DBAs. Se o projeto da aplicação exigir a exclusão ou atualização de mais de 5.000 linhas de uma vez, uma solução para evitar o escalonamento de bloqueios, e os efeitos resultantes, é dividir a única transação em duas ou mais transações onde cada uma irá lidar com menos de 5.000 linhas, pois desta forma o escalonamento de bloqueios poderia ser evitado
Encontrar informações sobre bloqueios ativos do SQL Server
SQL Server fornece o sistema Dynamics Management View (DMV).dm_tran_locks que retorna informações sobre os recursos do gerenciador de bloqueios que estão em uso atualmente, o que significa que ele exibirá todos os bloqueios “ao vivo” adquiridos por transações. Mais detalhes sobre este DMV podem ser encontrados no artigo sys.dm_tran_locks (Transact-SQL).
As colunas mais importantes utilizadas para a identificação do bloqueio são resource_type, request_mode, e resource_description. Se necessário, mais colunas como recurso adicional de informação podem ser incluídas durante a solução de problemas
Aqui está o exemplo da consulta
A cláusula onde nesta consulta é utilizada como filtro no resource_type a eliminar. A partir dos resultados, aqueles bloqueios geralmente compartilhados adquiridos na base de dados já que estão sempre presentes no nível da base de dados
Uma breve explicação das três colunas aqui apresentadas:
resource_type – Exibe um recurso da base de dados onde os bloqueios estão sendo adquiridos. A coluna pode exibir um dos seguintes valores: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID
request_mode – exibe o modo de bloqueio que é adquirido no recurso
resource_description – exibe uma breve descrição do recurso e não é preenchida para todos os modos de bloqueio. Na maioria das vezes a coluna contém o id da linha, página, objeto, arquivo, etc
- Autor
- Recent Posts
Militar devoto da aviação e modelador de aeronaves de núcleo duro. Fã de esportes radicais; instrutor de pára-quedismo e bungee jump. Uma vez sério, agora apenas um fotógrafo de tempo livre
Veja todos os posts de Nikola Dimitrijevic
- Guia de bandeiras de rastreio do SQL Server; de -1 a 840 – 4 de março de 2019
- Como lidar com o tipo de espera WRITELOG do SQL Server – 13 de junho de 2018
- Contadores de desempenho do SQL Server (Batch Requests/sec ou Transactions/sec): o que monitorar e porquê – 5 de junho de 2018