Bloqueio otimizado

Aplica-se a:Banco de Dados SQL do Azure

Este artigo apresenta o recurso de bloqueio otimizado, uma nova capacidade do mecanismo de banco de dados SQL Server que oferece um mecanismo de bloqueio de transação avançado que reduz o consumo de memória de bloqueio e o bloqueio em 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 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. Veja mais informações na seção sobre bloqueio de TID (ID da transação).
• O LAQ (bloqueio após qualificação) é uma otimização que avalia predicados de uma consulta na última versão confirmada da linha sem adquirir um bloqueio, melhorando assim a simultaneidade. Veja mais informações na seção sobre LAQ (bloqueio após a qualificação).

Por exemplo:

• Sem o bloqueio otimizado, atualizar 1 milhão de linhas em uma tabela pode exigir 1 milhão de bloqueios de linha exclusivos (X), que são retidos até o final da transação.
• Com o bloqueio otimizado, atualizar 1 milhão de linhas em uma tabela pode exigir 1 milhão de bloqueios de linha X, mas cada bloqueio é liberado assim que a linha é atualizada, e apenas um bloqueio TID será retido até o final da transação.

Este artigo aborda em detalhes esses dois conceitos essenciais de bloqueio otimizado.

Disponibilidade

Atualmente, o bloqueio otimizado está disponível apenas no banco de dados SQL do Azure. Para obter mais informações, confira Onde o bloqueio otimizado está disponível?

O bloqueio otimizado está habilitado?

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

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

Se você não tiver conexão ao banco de dados especificado no DATABASEPROPERTYEX, o resultado será NULL. Você deve receber 0 (o bloqueio otimizado está desabilitado) ou 1 (habilitado).

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.

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, use 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

Este é um 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 simultaneamente, para proteger a integridade e a consistência dos dados.

Quando precisa modificar dados, a transação pode solicitar 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 múltiplas transações têm permissão para acessar os mesmos dados simultaneamente, 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 de banco de dados para fornecer a semântica aos níveis de isolamento da transação ANSI SQL. O bloqueio em bancos de dados é essencial, mas a simultaneidade reduzida, os deadlocks, a complexidade e a sobrecarga de bloqueio podem afetar o desempenho e a escalabilidade.

Bloqueio otimizado e bloqueio de TID (ID da transação)

Cada linha no mecanismo de banco de dados contém internamente uma TID (ID da transação) quando o controle de versão de linha está em uso. Essa TID é persistida no disco. Toda transação que modifica uma linha aplicará um sela 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 terão um bloqueio S na TID para verificar se a primeira transação ainda está ativa. 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 no recurso TID, substituindo os bloqueios de página e linha (chave), como veremos na próxima demonstração. (Outros bloqueios padrão de banco de dados e de objetos não são afetados pelo bloqueio otimizado.)

O bloqueio otimizado ajuda a reduzir a memória de bloqueio, pois se retém poucos bloqueios para grandes transações. Além disso, o bloqueio otimizado evita escalonamentos de bloqueio. Assim, outras transações simultâneas podem acessar a tabela.

Veja este cenário de exemplo T-SQL que procura bloqueios na sessão atual do usuário:

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

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

BEGIN TRAN
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 TRAN
GO
DROP TABLE IF EXISTS t0;

Sem o benefício do bloqueio otimizado, a mesma consulta cria quatro bloqueios:

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

Bloqueio otimizado e LAQ (bloqueio após qualificação)

Com base na infraestrutura de TID, o bloqueio otimizado altera a forma como os predicados de consulta protegem os bloqueios.

Sem o bloqueio otimizado, os predicados das 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 X será feito antes de atualizar a linha.

Com o bloqueio otimizado e quando o nível de RCSI (isolamento do instantâneo de leitura confirmada) está habilitado, os predicados são aplicados na versão confirmada mais recente sem usar bloqueios de linha. Se o predicado não for satisfeito, a consulta será movida para a próxima linha da verificação. Se o predicado for satisfeito, o bloqueio de linha X será feito para atualizar a linha. 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 de predicados é feita sem adquirir bloqueios, consultas simultâneas que modificam linhas diferentes não bloquearão umas às outras.

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 TRAN UPDATE t1 SET b=b+10 WHERE a=1;
	BEGIN TRAN UPDATE t1 SET b=b+10 WHERE a=2;
COMMIT TRAN
	COMMIT TRAN

O comportamento de bloqueio muda com o bloqueio otimizado do exemplo anterior. Sem o bloqueio otimizado, a Sessão 2 será bloqueada.

Porém, com o bloqueio otimizado, a Sessão 2 não será bloqueada, pois a versão confirmada mais recente da linha 1 contém a=1, o que não satisfaz o predicado da Sessão 2.

Se o predicado for satisfeito, aguardamos a conclusão das transações ativas da linha. Se precisássemos esperar pelo bloqueio de TID S, a linha poderia ter mudado, assim como a última versão confirmada. Nesse caso, em vez de anular a transação por causa de um conflito de atualização, o mecanismo de banco de dados repetirá a avaliação de predicado na mesma linha. Se o predicado se qualificar na nova tentativa, a linha será atualizada.

Veja o exemplo a seguir quando a alteração de predicado é repetida automaticamente:

CREATE TABLE t2
(a int not null
,b int null);

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

Sessão 1	Sessão 2
BEGIN TRAN UPDATE t2 SET b=b+10 WHERE a=1;
	BEGIN TRAN UPDATE t2 SET b=b+10 WHERE a=1;
COMMIT TRAN
	COMMIT TRAN

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

Sistemas simultâneos no nível de RCSI (isolamento do instantâneo de leitura confirmada) com cargas de trabalho que dependem de ordem de execução estrita de transações, podem apresentar comportamento de consulta diferente quando o bloqueio otimizado estiver habilitado.

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

CREATE TABLE t1 (a int not null, b int null);

INSERT INTO t1 VALUES (1,1);
GO

Sessão 1	Sessão 2
BEGIN TRAN T1 UPDATE t1 SET b=2 WHERE a=1;
	BEGIN TRAN T2 UPDATE t1 SET b=3 WHERE b=2;
COMMIT TRAN
	COMMIT TRAN

Vamos avaliar o resultado do cenário acima com e sem LAQ (bloqueio após a qualificação), parte integrante do bloqueio otimizado.

Sem LAQ

Sem o LAQ, a transação T2 será bloqueada e aguardará a transação T1 ser concluída.

Após a confirmação das duas transações, a tabela t1 conterá estas linhas:

 a | b
 1 | 3

Com LAQ

Com LAQ, a transação T2 usará a última versão confirmada da linha b (b=1 no repositório de versões) para avaliar o predicado (b=2). Esta linha não se qualifica, então é ignorada, e a T2 se move para a próxima linha 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 t1 conterá estas linhas:

 a | b
 1 | 2

Importante

Mesmo sem LAQ, os aplicativos não deverão pressupor que o SQL Server (sob níveis de isolamento de controle de versão) garantirá uma ordenação rigorosa, sem usar dicas de bloqueio. Nossa recomendação geral para clientes de sistemas simultâneos no RCSI com cargas de trabalho que dependem de ordem de execução rigorosa de transações (como visto no exercício anterior), é usar níveis de isolamento mais rigorosos.

Adições de diagnóstico para bloqueio otimizado

Para oferecer suporte ao monitoramento e à solução de problemas de bloqueio e bloqueio otimizado, procure as seguintes adições:

• Tipos de espera para bloqueio otimizado
  • Tipos de espera XACT e descrições de recursos em sys.dm_os_wait_stats (Transact-SQL):
    • LCK_M_S_XACT_READ - Ocorre quando uma tarefa está aguardando um bloqueio compartilhado em um tipo de wait_resource do XACT, com a intenção de ler.
    • LCK_M_S_XACT_MODIFY - Ocorre quando uma tarefa está aguardando um bloqueio compartilhado em um tipo de wait_resource do XACT, com a intenção de modificar.
    • LCK_M_S_XACT: ocorre quando uma tarefa está aguardando um bloqueio compartilhado em um tipo de wait_resource do XACT, quando a intenção não pode ser inferida. Raro.
• 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.

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 de leitura confirmada como o nível de isolamento padrão. Se o RCSI não estiver habilitado, habilite-o usando este exemplo:

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

No banco de dados SQL do Azure, o RCSI é habilitado por padrão, e a leitura confirmada é o nível de isolamento padrão. Com o RCSI habilitado, ao usar o nível de isolamento de leitura confirmada, os leitores não bloqueiam gravadores, e vice-versa. Os leitores leem uma versão da linha pelo instantâneo feito no início da consulta. Com LAQ, os gravadores qualificarão as linhas de acordo com o predicado, com base na versão confirmada mais recente da linha sem adquirir bloqueios em U. Com LAQ, a consulta aguardará somente se a linha se qualificar e se 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 será 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 expiram no fim da transação. Ao usar níveis de isolamento mais rígidos, como leitura repetível ou serializável, o mecanismo de banco de dados é forçado a reter bloqueios de linha e de página até o final da transação, tanto para leitores como para gravadores, resultando em mais bloqueio e bloqueio de memória.

Evite dicas de bloqueio

As tabelas e dicas de consulta são respeitadas, mas reduzem o benefício do bloqueio otimizado. Dicas de bloqueio, como UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK etc., nas consultas reduzem todos os benefícios do bloqueio otimizado. Usar essas dicas de bloqueio nas consultas força o mecanismo de banco de dados a fazer bloqueios de linha/página e retê-los até o final da transação, para respeitar a intenção das dicas 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 select com UPDLOCK e atualizá-la depois. Recomendamos usar dicas de bloqueio apenas quando for necessário.

Com o bloqueio otimizado, não há restrições a consultas existentes, e as consultas não precisam ser reescritas. As consultas que não usarem dicas aproveitarão melhor o bloqueio otimizado.

Uma dica de tabela em uma tabela de uma consulta não desabilitará o bloqueio otimizado em outras tabelas da 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 UPDATE. Por exemplo:

CREATE TABLE t3
(a int not null
, b int not null);

CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;

No exemplo de consulta anterior, apenas a tabela t4 será afetada pela dica de bloqueio, enquanto t3 ainda poderá se beneficiar do bloqueio otimizado.

UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;

No exemplo de consulta anterior, somente a tabela t3 usará o nível de isolamento de leitura repetível e reterá bloqueios até o fim da transação. Outras atualizações para t3 ainda podem se beneficiar do bloqueio otimizado. Também se aplica à dica HOLDLOCK.

Perguntas frequentes

Onde o bloqueio otimizado está disponível?

Atualmente, o bloqueio otimizado está disponível no banco de dados SQL do Azure.

O bloqueio otimizado está disponível nestas camadas de serviço:

• todas as camadas de serviço DTU
• todas as camadas de serviço vCore, inclusive provisionadas e sem servidor

O bloqueio otimizado atualmente não está disponível em:

• Instância Gerenciada do Azure SQL
• SQL Server 2022 (16.x)

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 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.

Posso desabilitar o bloqueio otimizado?

Hoje os clientes podem criar uma solicitação de suporte para desabilitar o bloqueio otimizado.

Siga estas etapas para criar uma solicitação de suporte ao banco de dados SQL do Azure no portal do Azure.

1. Primeiro, verifique se o bloqueio otimizado está habilitado no banco de dados.

2. No menu do portal do Azure, selecione Ajuda + suporte.

   

3. Em Ajuda + suporte, selecione Criar uma solicitação de suporte.

   

4. Em Tipo de problema, selecione Técnico.

5. Em Assinatura, Serviço e Recurso, escolha o banco de dados SQL desejado.

6. Em Resumo, digite “Disable optimized locking”.

7. Em Tipo de problema, escolha Desempenho e execução de consultas.

8. Em Subtipo de problema, escolha Bloqueios e deadlocks.

9. Em Detalhes adicionais, forneça o máximo de informações possível sobre o motivo para desabilitar o bloqueio otimizado. Temos interesse em analisar com você os motivos e casos de uso para desabilitar o bloqueio otimizado.

Conteúdo relacionado

• Guia de controle de versão de linha e bloqueio de transações
• RCSI (isolamento de instantâneo confirmado por leitura)
• sys.dm_tran_locks (Transact-SQL)
• Recuperação Acelerada de Banco de Dados no SQL do Azure
• Recuperação acelerada de banco de dados