Bloqueio otimizado

Artigo
02/05/2025

Aplica-se a:Banco de Dados SQL do Azure Banco de Dados SQL no Microsoft Fabric

Este artigo apresenta o bloqueio otimizado, uma funcionalidade do mecanismo de banco de dados que oferece um mecanismo de bloqueio de transações aprimorado para reduzir o consumo de memória de bloqueio e o bloqueio de transações simultâneas.

O que é bloqueio otimizado?

O bloqueio otimizado ajuda a reduzir a memória de bloqueio, pois se retém poucos bloqueios mesmo para grandes transações. Além disso, o bloqueio otimizado evita escalonamentos de bloqueio. Isso permite maior acesso simultâneo à tabela.

O bloqueio otimizado é composto por dois componentes principais: bloqueio de TID (ID da transação) e LAQ (bloqueio após qualificação).

A TID (ID da transação) é um identificador exclusivo da transação. Cada linha é rotulada com a última TID que a modificou. Em vez de utilizar muitos bloqueios de identificador de chave ou de linha, usa-se um único bloqueio na TID. Para mais informações, veja bloqueio de TID (ID da transação).
O LAQ (bloqueio após qualificação) é uma otimização que avalia predicados de consulta usando a última versão confirmada da linha sem adquirir um bloqueio, melhorando assim a simultaneidade. Para mais informações, veja LAQ (bloqueio após a qualificação).

Por exemplo:

Sem bloqueio otimizado, a atualização de mil linhas em uma tabela pode exigir mil bloqueios de linha exclusivos (X) mantidos até o final da transação.
Com o bloqueio otimizado, a atualização de 1.000 linhas em uma tabela pode exigir 1.000 bloqueios de linha X, mas cada bloqueio é liberado assim que a linha correspondente é atualizada, e apenas um bloqueio TID é mantido até o final da transação. Como os bloqueios são liberados rapidamente, o uso de memória de bloqueio é reduzido e o escalonamento de bloqueios é muito menos provável de ocorrer, melhorando a simultaneidade da carga de trabalho.

Observação

Habilitar o bloqueio otimizado reduz ou elimina bloqueios de linha e página adquiridos pelas instruções DML (linguagem de modificação de dados), como INSERT, UPDATE, DELETE e MERGE. Ele não tem efeito em outros tipos de bloqueios de banco de dados e objetos, como bloqueios de esquema.

Disponibilidade

O bloqueio otimizado está disponível no Banco de Dados SQL do Azure e no banco de dados SQL , somente no Microsoft Fabric, em todas as camadas de serviço e tamanhos de computação.

O bloqueio otimizado não está disponível no momento na Instância Gerenciada de SQL do Azure nem no SQL Server.

O bloqueio otimizado está habilitado?

O bloqueio otimizado é habilitado por banco de dados de usuário. Conecte-se ao banco de dados e use a seguinte consulta para verificar se o bloqueio otimizado está habilitado:

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

Result	Descrição
`0`	O bloqueio otimizado está desabilitado.
`1`	O bloqueio otimizado está habilitado.
`NULL`	O bloqueio otimizado não está disponível.

O bloqueio otimizado se baseia em outros recursos de banco de dados:

O bloqueio otimizado requer a ADR (Recuperação Acelerada de Banco de Dados) habilitada no banco de dados.
Para aproveitar ao máximo o bloqueio otimizado, o RCSI (isolamento de instantâneo de leitura confirmada) deve estar habilitado no banco de dados. O componente LAQ do bloqueio otimizado só estará em vigor se o RCSI estiver ativado.

A ADR e o RCSI são habilitados por padrão no banco de dados SQL do Azure. Para verificar se essas opções estão habilitadas no banco de dados atual, conecte-se ao banco de dados e execute esta consulta T-SQL:

SELECT name,
       is_read_committed_snapshot_on,
       is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = DB_NAME();

Visão geral do bloqueio

Esse é um breve resumo do comportamento quando o bloqueio otimizado não está habilitado. Para obter mais informações, leia o Guia de controle de versão de linha e bloqueio de transações.

No mecanismo de banco de dados, o bloqueio é um mecanismo que impede que várias transações atualizem os mesmos dados ao mesmo tempo para garantir as propriedades ACID das transações.

Quando precisa modificar dados, a transação solicita um bloqueio nos dados. O bloqueio é concedido se nenhum outro bloqueio conflitante estiver retido nos dados, e a transação pode prosseguir com a modificação. Se outro bloqueio conflitante estiver retido nos dados, a transação deverá aguardar que o bloqueio seja liberado antes de prosseguir.

Quando várias transações tentam acessar os mesmos dados ao mesmo tempo, o mecanismo de banco de dados deve resolver conflitos potencialmente complexos com leituras e gravações simultâneas. O bloqueio é um dos mecanismos usados pelo mecanismo para fornecer a semântica aos níveis de isolamento da transação ANSI SQL. Embora o bloqueio em bancos de dados seja essencial, a redução da simultaneidade, dos deadlocks, da complexidade e da sobrecarga de bloqueio pode afetar o desempenho e a escalabilidade.

Bloqueio de ID de Transação (TID)

Quando o controle de versão de linha baseado em níveis de isolamento está em uso, ou quando o ADR está habilitado, cada linha no mecanismo de banco de dados contém internamente uma TID (ID da transação). Essa TID é persistida no disco. Toda transação que modifica uma linha aplicará um selo nessa linha com sua TID.

Com o bloqueio de TID, em vez de tirar o bloqueio da chave da linha, retira-se o bloqueio da TID da linha. A transação de modificação reterá um bloqueio X na TID. Outras transações adquirem um bloqueio S na TID para aguardar até que a primeira transação seja concluída. Com o bloqueio de TID, os bloqueios de página e de linha continuam em uso para atualizações, mas cada bloqueio de página e de linha é liberado quando cada linha é atualizada. O único bloqueio retido até o final da transação é o bloqueio X único no recurso TID, substituindo múltiplos bloqueios de página e linha (chave).

Considere o exemplo a seguir que mostra bloqueios para a sessão atual enquanto uma transação de gravação está ativa:

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRANSACTION;

UPDATE t0
SET b = b + 10;

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
      AND
      resource_type IN ('PAGE','RID','KEY','XACT');

COMMIT TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

Se o bloqueio otimizado estiver habilitado, a solicitação manterá apenas um único bloqueio X no recurso XACT (transação).

Caso o bloqueio otimizado não esteja habilitado, a mesma solicitação possui quatro bloqueios: um bloqueio IX (intenção exclusiva) na página que contém as linhas e três bloqueios de chave X em cada linha.

A DMV (exibição de gerenciamento dinâmico) sys.dm_tran_locks é útil para examinar ou solucionar problemas de bloqueio, como observar o bloqueio otimizado em ação.

LAQ (bloquear após a qualificação)

Com base na infraestrutura TID, o bloqueio otimizado altera a forma como as instruções DML, como INSERT, UPDATEe DELETE, adquirem bloqueios.

Sem o bloqueio otimizado, os predicados de consultas são verificados linha por linha em uma verificação fazendo primeiro um bloqueio de linha de atualização (U). Se o predicado for satisfeito, o bloqueio de linha exclusivo (X) será feito antes de atualizar a linha e retido até o fim da transação.

Com o bloqueio otimizado e quando o nível de isolamento do instantâneo READ COMMITTED (RCSI) está habilitado, os predicados podem ser verificados com otimismo na versão confirmada mais recente da linha sem realizar bloqueios. Se o predicado não for satisfatório, a consulta passará para a linha seguinte na varredura. Se o predicado for satisfeito, o bloqueio de linha X será feito para atualizar a linha.

Em outras palavras, o bloqueio é feito após a qualificação da linha para modificação. O bloqueio de linha X é liberado assim que a atualização de linha é concluída, antes do fim da transação.

Como a avaliação do predicado é executada sem adquirir bloqueios, consultas simultâneas que modificam linhas diferentes não bloqueiam umas às outras.

Por exemplo:

CREATE TABLE t1
(
a int NOT NULL,
b int NULL
);

INSERT INTO t1
VALUES (1,10),(2,20),(3,30);
GO

Sessão 1	Sessão 2
`BEGIN TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

Sem o bloqueio otimizado, a sessão 2 é bloqueada porque a sessão 1 mantém um bloqueio U na linha que a sessão 2 precisa ser atualizada. Porém, com o bloqueio otimizado, a sessão 2 não é bloqueada devido à ausência de bloqueios U e porque a coluna a é igual a 1 na última versão confirmada da linha 1, o que não satisfaz o predicado da sessão 2.

O LAQ é executado com otimismo supondo que uma linha não é modificada após a verificação do predicado. Se o predicado for atendido e a linha não tiver sido modificada após a verificação do predicado, ela será modificada pela transação atual.

Como bloqueios U não são feitos, uma transação simultânea pode modificar a linha após a avaliação do predicado. Se houver uma transação ativa mantendo um bloqueio TID X na linha, o mecanismo de banco de dados aguardará a conclusão dela. Se a linha tiver sido alterada depois que o predicado foi avaliado anteriormente, o mecanismo de banco de dados reavalia (ressignifica) o predicado novamente antes de modificar a linha. Se o predicado ainda for satisfeito, a linha será modificada.

A requalificação de predicados é suportada por um subconjunto dos operadores do motor de consulta. Se a reavaliação do predicado for necessária, mas o plano de consulta usar um operador que não dê suporte à requalificação do predicado, o mecanismo de banco de dados abortará internamente o processamento da declaração e o reiniciará sem LAQ. Quando essa anulação ocorre, o evento lock_after_qual_stmt_abort estendido é disparado.

Algumas instruções, por exemplo, UPDATE instruções com atribuição de variável e instruções com a cláusula OUTPUT, não podem ser anuladas e reiniciadas sem alterar a semântica. Para essas declarações, o LAQ não é usado.

No exemplo a seguir, o predicado é reavaliado porque outra transação alterou a linha:

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO

Sessão 1	Sessão 2
`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

Heurísticas LAQ

Conforme descrito em LAQ (bloqueio após qualificação), quando o LAQ é usado, algumas instruções podem ser reiniciadas internamente e processadas sem o uso do LAQ. Se isso acontecer com frequência, a sobrecarga do processamento repetido poderá se tornar significativa. Para manter essa sobrecarga no mínimo, o bloqueio otimizado usa um mecanismo de heurística para acompanhar o processamento repetido. Esse mecanismo desabilita o LAQ para o banco de dados se a sobrecarga exceder um limite.

Para fins do mecanismo de heurística, o trabalho feito por uma instrução é medido no número de páginas processadas (leituras lógicas). Se o mecanismo de banco de dados estiver modificando uma linha que foi modificada por outra transação após o início do processamento da instrução, o trabalho feito pela instrução será tratado como potencialmente desperdiçado porque a instrução pode ser anulada e reiniciada. O sistema acompanha o total de trabalho potencialmente desperdiçado e o total de trabalho realizado por todas as instruções no banco de dados.

O LAQ será desabilitado para o banco de dados se o percentual do trabalho potencialmente desperdiçado exceder um limite. O LAQ também será desabilitado se o número de instruções reiniciadas ultrapassar um limite.

Se o trabalho desperdiçado e o número de instruções reiniciadas estiverem abaixo dos respectivos limites, o LAQ será habilitado novamente para o banco de dados.

Alterações de comportamento da consulta com bloqueio otimizado e RCSI

Cargas de trabalho simultâneas no RCSI (isolamento do instantâneo de leitura confirmada) que dependem de ordem de execução estrita de transações, podem apresentar diferenças no comportamento de consulta quando o bloqueio otimizado estiver habilitado.

Veja o exemplo a seguir, em que a transação T2 está atualizando a tabela t4 com base na coluna b que foi atualizada durante a transação T1.

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

Sessão 1	Sessão 2
`BEGIN TRANSACTION T1;` `UPDATE t4` `SET b = 2` `WHERE a = 1;`
	`BEGIN TRANSACTION T2;` `UPDATE t4` `SET b = 3` `WHERE b = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

Vamos avaliar o resultado do cenário anterior com e sem bloqueio após a qualificação (LAQ).

Sem LAQ

Sem o LAQ, a instrução UPDATE na transação T2 é bloqueada, aguardando a transação T1 ser concluída. Depois que T1 for concluído, T2 atualizará a coluna de configuração de linha b para 3 porque seu predicado é atendido.

Após a confirmação das duas transações, a tabela t4 contém estas linhas:

 a | b
 1 | 3

Com LAQ

Com o LAQ, a transação T2 usa a versão confirmada mais recente da linha em que a coluna b é igual a 1 para avaliar seu predicado (b = 2). A linha não se qualifica; portanto, ela é ignorada e a instrução é concluída sem ter sido bloqueada pela transação T1. Neste exemplo, o LAQ remove o bloqueio, mas gera resultados diferentes.

Após a confirmação das duas transações, a tabela t4 contém estas linhas:

 a | b
 1 | 2

Importante

Mesmo sem LAQ, os aplicativos não devem presumir que o mecanismo de banco de dados assegura uma ordenação estrita sem usar dicas de bloqueio quando os níveis de isolamento baseados em controle de versão de linha são usados. Nossa recomendação geral para clientes que executam cargas de transações simultâneas no RCSI que dependem de ordem de execução rigorosa de transações (como visto no exemplo anterior) é usar níveis de isolamento mais rigorosos, como REPEATABLE READ e SERIALIZABLE.

Adições de diagnóstico para bloqueio otimizado

As melhorias a seguir ajudam a monitorar e solucionar problemas de bloqueio e deadlocks quando o bloqueio otimizado está habilitado:

Tipos de espera para bloqueio otimizado
- Os tipos de espera XACT para o bloqueio S no TID e descrições de recursos no sys.dm_os_wait_stats (Transact-SQL):
  - LCK_M_S_XACT_READ - Ocorre quando uma tarefa está aguardando um bloqueio compartilhado em um tipo XACT do wait_resource, com umaa intenção de ler.
  - LCK_M_S_XACT_MODIFY - Ocorre quando uma tarefa está aguardando um bloqueio compartilhado em um tipo XACT do wait_resource, com uma intenção de modificar.
  - LCK_M_S_XACT – ocorre quando uma tarefa está aguardando um bloqueio compartilhado em um tipo XACTwait_resource, em que a intenção não pode ser inferida. Esse cenário não é comum.
Bloqueando a visibilidade dos recursos
- XACT bloqueando recursos. Veja mais informações em resource_description em sys.DM tran_locks (Transact-SQL).
Aguardar visibilidade do recurso
- O XACT aguarda recursos. Veja mais informações em wait_resource em sys.dm_exec_requests (Transact-SQL).
Gráfico de deadlock
- Em cada recurso na <resource-list> do relatório de deadlock, cada elemento <xactlock> relata os recursos subjacentes e as informações específicas para bloqueios de cada membro de um deadlock. Veja mais informações e um exemplo em Bloqueio otimizado e deadlocks.
Eventos estendidos
- O evento lock_after_qual_stmt_abort é acionado quando uma instrução é anulada internamente e reiniciada devido a um conflito com outra transação. Para mais informações, veja LAQ (bloqueio após a qualificação).

Melhores práticas com bloqueio otimizado

Habilitar nível de RCSI (isolamento de instantâneo confirmado por leitura)

Para maximizar os benefícios do bloqueio otimizado, é recomendável habilitar o RCSI (isolamento do instantâneo de leitura confirmada) no banco de dados e usar o isolamento READ COMMITTED como o nível de isolamento padrão. Se ainda não estiver habilitado, habilite o RCSI conectando-se ao banco de dados master e executando a seguinte instrução:

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

No banco de dados SQL do Azure, o RCSI é habilitado por padrão, e READ COMMITTED é o nível de isolamento padrão. Com o RCSI habilitado e ao usar o nível de isolamento READ COMMITTED, os leitores leem uma versão da linha do instantâneo tirado no início da instrução. Com LAQ, os gravadores qualificam as linhas de acordo com o predicado, com base na versão confirmada mais recente da linha e sem adquirir bloqueios U. Com o LAQ, uma consulta aguardará apenas se a linha se qualificar e houver uma transação de gravação ativa nessa linha. Qualificar com base na versão confirmada mais recente e bloquear apenas as linhas qualificadas reduz o bloqueio e aumenta a simultaneidade.

Além do bloqueio reduzido, a memória de bloqueio necessária também é reduzida. Isso ocorre porque os leitores não aceitam bloqueios, e os gravadores usam apenas bloqueios de curta duração, em vez de bloqueios que são retidos até o fim da transação. Quando você usa níveis de isolamento mais rigorosos, como REPEATABLE READ ou SERIALIZABLE, o mecanismo de banco de dados mantém bloqueios de linha e página até o final da transação, mesmo com bloqueio otimizado habilitado, o que resulta em maior bloqueio para leitores e escritores e aumentos no uso de memória para bloqueios.

Evite dicas de bloqueio

Embora as dicas de tabela e consulta, como UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK, etc., sejam respeitadas quando o bloqueio otimizado está habilitado, elas reduzem o benefício do bloqueio otimizado. As sugestões de bloqueio forçam o mecanismo de banco de dados a utilizar bloqueios de linha ou de página e a mantê-los até o final da transação, para cumprir o propósito das sugestões de bloqueio. Alguns aplicativos têm lógica em que as dicas de bloqueio são necessárias, por exemplo, ao ler uma linha com a dica UPDLOCK e atualizá-la depois. Recomendamos usar dicas de bloqueio apenas quando for necessário.

Com o bloqueio otimizado, não há restrições em consultas existentes e as consultas não precisam ser reescritas. As consultas que não utilizam dicas se beneficiam mais do bloqueio otimizado.

Uma dica de tabela aplicada a uma tabela em uma consulta não desabilita o bloqueio otimizado para outras tabelas na mesma consulta. Além disso, o bloqueio otimizado afeta somente o comportamento de bloqueio das tabelas que estão sendo atualizadas por uma instrução DML, como INSERT, UPDATE, DELETE ou MERGE. Por exemplo:

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

INSERT INTO t5 VALUES (1,10),(2,20),(3,30);
INSERT INTO t6 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

No exemplo de consulta anterior, apenas a tabela t6 é afetada pela dica de bloqueio, enquanto t5 ainda poderá se beneficiar do bloqueio otimizado.

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

No exemplo de consulta anterior, somente a tabela t5 usa o nível de isolamento REPEATABLE READ e retém bloqueios até o fim da transação. Outras atualizações para t5 ainda podem se beneficiar do bloqueio otimizado. Também se aplica à dica HOLDLOCK.

Perguntas frequentes

O bloqueio otimizado é ativado por padrão em bancos de dados novos e já existentes?

No banco de dados SQL do Azure, sim.

Como detectar se o bloqueio otimizado está habilitado?

Confira O bloqueio otimizado está habilitado?

O que acontece quando a ADR (recuperação acelerada de banco de dados) não está habilitada no meu banco de dados?

Se a ADR estiver desabilitada, o bloqueio otimizado também será desabilitado automaticamente.

E se eu quiser forçar o bloqueio de consultas mesmo com o bloqueio otimizado?

Para clientes que usam RCSI, para forçar o bloqueio entre duas consultas com o bloqueio otimizado habilitado, use a dica de consulta READCOMMITTEDLOCK.

O bloqueio otimizado é usado em réplicas secundárias somente leitura?

Não, pois as instruções DML não podem ser executadas em réplicas somente leitura, e os bloqueios de linha e página correspondentes não são realizados.

O bloqueio otimizado é usado ao modificar dados em tempdb e em tabelas temporárias?

Não no momento.

Compartilhar via

Bloqueio otimizado

O que é bloqueio otimizado?

Disponibilidade

O bloqueio otimizado está habilitado?

Visão geral do bloqueio

Bloqueio de ID de Transação (TID)

LAQ (bloquear após a qualificação)

Heurísticas LAQ

Alterações de comportamento da consulta com bloqueio otimizado e RCSI

Adições de diagnóstico para bloqueio otimizado

Melhores práticas com bloqueio otimizado

Habilitar nível de RCSI (isolamento de instantâneo confirmado por leitura)

Evite dicas de bloqueio

Perguntas frequentes

O bloqueio otimizado é ativado por padrão em bancos de dados novos e já existentes?

Como detectar se o bloqueio otimizado está habilitado?

O que acontece quando a ADR (recuperação acelerada de banco de dados) não está habilitada no meu banco de dados?

E se eu quiser forçar o bloqueio de consultas mesmo com o bloqueio otimizado?

O bloqueio otimizado é usado em réplicas secundárias somente leitura?

O bloqueio otimizado é usado ao modificar dados em tempdb e em tabelas temporárias?

Comentários

Recursos adicionais

Compartilhar via

Bloqueio otimizado

O que é bloqueio otimizado?

Disponibilidade

O bloqueio otimizado está habilitado?

Visão geral do bloqueio

Bloqueio de ID de Transação (TID)

LAQ (bloquear após a qualificação)

Heurísticas LAQ

Alterações de comportamento da consulta com bloqueio otimizado e RCSI

Adições de diagnóstico para bloqueio otimizado

Melhores práticas com bloqueio otimizado

Habilitar nível de RCSI (isolamento de instantâneo confirmado por leitura)

Evite dicas de bloqueio

Perguntas frequentes

O bloqueio otimizado é ativado por padrão em bancos de dados novos e já existentes?

Como detectar se o bloqueio otimizado está habilitado?

O que acontece quando a ADR (recuperação acelerada de banco de dados) não está habilitada no meu banco de dados?

E se eu quiser forçar o bloqueio de consultas mesmo com o bloqueio otimizado?

O bloqueio otimizado é usado em réplicas secundárias somente leitura?

O bloqueio otimizado é usado ao modificar dados em tempdb e em tabelas temporárias?

Conteúdo relacionado

Comentários

Recursos adicionais