Bloqueio otimizado

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

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

O que é o bloqueio otimizado?

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

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

• Um ID de transação (TID) é um identificador exclusivo de uma transação. Cada linha é rotulada com a última TID que a modificou. Em vez de potencialmente muitos bloqueios de chave ou identificador de linha, um único bloqueio no TID é usado. Para obter mais informações, consulte bloqueio de ID de transação (TID).
• Bloqueio após qualificação (LAQ) é uma otimização que avalia predicados de consulta usando a versão confirmada mais recente da linha sem adquirir um bloqueio, melhorando assim a simultaneidade. Para obter mais informações, consulte Bloqueio após qualificação (LAQ).

Por exemplo:

• Sem bloqueio otimizado, a atualização de 1.000 linhas em uma tabela pode exigir 1.000 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 cada linha é 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 bloqueio é muito menos provável de ocorrer, melhorando a concorrência da carga de trabalho.

Observação

A habilitação do bloqueio otimizado reduz ou elimina os bloqueios de linha e página adquiridos pelas instruções DML (Data Modification Language), como INSERT, UPDATE, DELETEMERGE. Não tem efeito sobre outros tipos de bloqueios de banco de dados e objeto, como os 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 atualmente na Instância Gerenciada SQL do Azure ou no SQL Server.

O bloqueio otimizado está ativado?

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');

Resultado	Descrição
0	O bloqueio otimizado está desativado.
1	O bloqueio otimizado está ativado.
NULL	O bloqueio otimizado não está disponível.

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

• O bloqueio otimizado requer de recuperação acelerada do banco de dados (ADR) seja habilitado no banco de dados.
• Para obter o máximo benefício do bloqueio otimizado, de isolamento de instantâneo confirmado de leitura confirmada (RCSI) deve ser habilitada para o banco de dados. O componente LAQ do bloqueio otimizado só estará em vigor se o RCSI estiver ativado.

O 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 para seu banco de dados atual, conecte-se ao banco de dados e execute a seguinte 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 breve resumo do comportamento quando o bloqueio otimizado não está ativado. Para obter mais informações, consulte o guia de bloqueio de transações e versionamento de linhas.

No mecanismo de banco de dados, o bloqueio é um mecanismo que impede que várias transações atualizem os mesmos dados simultaneamente, a fim de garantir o ACID propriedades das transações.

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

Quando várias transações tentam 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 pelos quais o motor pode fornecer a semântica para os níveis de isolamento da transação ANSI SQL . Embora o bloqueio em bases de dados seja essencial, a redução da concorrência, deadlocks, complexidade e sobrecarga de bloqueio podem afetar o desempenho e a escalabilidade.

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

Quando controle de versão de linha níveis de isolamento baseados estão em uso ou quando o ADR está habilitado, cada linha no banco de dados contém internamente uma ID de transação (TID). Esta TID é armazenada no disco. Cada transação que modifica uma linha carimba essa linha com sua TID.

Com o bloqueio TID, em vez de ser feito sobre a chave da linha, o bloqueio é realizado sobre o TID da linha. A transação de modificação mantém um bloqueio de X sobre o seu TID. Outras transações adquirem um bloqueio S no TID para esperar até que a primeira transação seja concluída. Com o bloqueio TID, os bloqueios de página e linha continuam a ser feitos para modificações, mas cada bloqueio de página e linha é liberado assim que cada linha é modificada. O único bloqueio mantido até ao final da transação é o bloqueio único X no recurso TID, substituindo vários bloqueios de página e de linha (chave).

Considere o exemplo seguinte que mostra bloqueios para a sessão atual enquanto uma transação de escrita 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 de X no recurso XACT (transação).

Se o bloqueio otimizado não estiver ativado, a mesma solicitação manterá quatro bloqueios - três bloqueios de chave X em cada linha e um bloqueio IX (de intenção exclusiva) na página que contém as linhas.

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

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

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

Sem bloqueio otimizado, os predicados de consulta são verificados linha por linha durante uma análise, começando com a obtenção de um bloqueio de linha de atualização (U). Se o predicado for satisfeito, um bloqueio de linha exclusivo (X) é feito antes de atualizar a linha e mantido até o final da transação.

Com o bloqueio otimizado e quando o nível de isolamento de instantâneo de READ COMMITTED (RCSI) está ativado, os predicados são verificados na versão confirmada mais recente da linha sem precisar de bloqueios. Se o predicado não satisfizer, a consulta será movida para a próxima linha da verificação. Se o predicado for satisfeito, um bloqueio de linha X será usado para atualizar a linha. O bloqueio de linha X é liberado assim que a atualização da linha é concluída, antes do final da transação.

Como a avaliação de predicados é realizada sem adquirir bloqueios, consultas simultâneas modificando 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 bloqueamento otimizado, a sessão 2 é bloqueada porque a sessão 1 mantém um bloqueio de U na linha que a sessão 2 precisa atualizar. No entanto, com o bloqueio otimizado, a sessão 2 não é bloqueada porque não são aplicados bloqueios de U e porque, na versão mais recente confirmada da linha 1, a coluna a é igual a 1, o que não atende o predicado da sessão 2.

Como bloqueios não são usados com o LAQ U, uma transação simultânea pode modificar a linha após o predicado ter sido avaliado. Se o predicado estiver satisfeito e não houver nenhuma outra transação ativa na linha (sem bloqueio TID X), a linha será modificada. Se houver uma transação ativa, o mecanismo de banco de dados aguardará sua conclusão e reavaliará o predicado novamente no momento da modificação, pois a outra transação pode ter modificado a linha. Se o predicado ainda estiver satisfeito, a linha será modificada.

Considere o exemplo a seguir em que a avaliação de predicados é repetida automaticamente 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;

O comportamento da consulta muda com bloqueio otimizado e RCSI

Cargas de trabalho simultâneas sob RCSI (Read Committed snapshot isolation) que dependem de uma ordem de execução estrita de transações podem apresentar diferenças no comportamento da consulta quando o bloqueio otimizado está habilitado.

Considere 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 LAQ, a declaração UPDATE na transação T2 é bloqueada, à espera da transação T1. Quando T1 é concluído, T2 atualiza a coluna de configuração de linha b para 3 porque seu predicado está satisfeito.

Após a confirmação de ambas as transações, a tabela t4 contém as seguintes linhas:

 a | b
 1 | 3

com LAQ

Com o LAQ, a transação T2 usa a última versão confirmada da linha onde a coluna b é igual a 1 para avaliar seu predicado (b = 2). A linha de dados não se qualifica; dessa forma, é ignorada e a instrução é concluída sem ter sido bloqueada pela transação T1. Neste exemplo, o LAQ remove o bloqueio, mas leva a resultados diferentes.

Após a confirmação de ambas as transações, a tabela t4 contém as seguintes linhas:

 a | b
 1 | 2

Importante

Mesmo sem LAQ, os aplicativos não devem assumir que o mecanismo de banco de dados garante uma ordenação rigorosa 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 trabalho simultâneas sob RCSI que dependem de ordem de execução estrita de transações (como mostrado no exemplo anterior) é usar níveis de isolamento mais rígidos como REPEATABLE READ e SERIALIZABLE.

Melhorias de diagnóstico para um bloqueio otimizado

As melhorias a seguir ajudam a monitorizar e resolver problemas de bloqueios e interbloqueios quando o bloqueio otimizado está ativado:

• Tipos de espera para bloqueio otimizado
  • XACT tipos de espera para o bloqueio de S no TID e descrições de recursos em sys.dm_os_wait_stats (Transact-SQL):
    • LCK_M_S_XACT_READ - Ocorre quando uma tarefa está à espera de um bloqueio partilhado num tipo XACTwait_resource, com a intenção de ler.
    • LCK_M_S_XACT_MODIFY - Ocorre quando uma tarefa está à espera de um bloqueio compartilhado em um tipo de XACTwait_resource, com a intenção de modificação.
    • LCK_M_S_XACT - Ocorre quando uma tarefa está aguardando um bloqueio partilhado em tipo XACTwait_resource e a intenção não pode ser inferida. Este cenário não é comum.
• Bloqueando a visibilidade dos recursos
  • XACT bloquear recursos. Para obter mais informações, consulte resource_description em sys.dm_tran_locks (Transact-SQL).
• Aguarde a visibilidade do recurso
  • XACT aguardar recursos. Para obter mais informações, consulte wait_resource em sys.dm_exec_requests (Transact-SQL).
• Gráfico de impasse
  • Em cada recurso no relatório de deadlock <resource-list>, cada elemento <xactlock> detalha os recursos subjacentes e apresenta informações específicas sobre os bloqueios de cada membro de um deadlock. Para obter mais informações e um exemplo, consulte Bloqueio otimizado e impasses.

Práticas recomendadas com bloqueio otimizado

Habilitar o isolamento de instantâneo de leitura confirmada (RCSI)

Para maximizar os benefícios do bloqueio otimizado, é recomendável habilitar o isolamento de instantâneo confirmado de leitura (RCSI) no banco de dados e usar 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;

Na Base de Dados SQL do Azure, o RCSI está ativado por predefinição e READ COMMITTED é o nível de isolamento predefinido. Com o RCSI ativado 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 o LAQ, os utilizadores avaliam as linhas com base no predicado, considerando a última versão confirmada das linhas e sem adquirir bloqueios U. Com o LAQ, uma consulta aguarda somente se a linha for qualificada e houver uma transação de gravação ativa nessa linha. A qualificação com base na versão confirmada mais recente, juntamente com o bloqueio apenas das linhas qualificadas, reduz o bloqueio e aumenta a simultaneidade.

Além do bloqueio reduzido, a memória de bloqueio necessária é reduzida. Isso ocorre porque os leitores não aceitam nenhum bloqueio, e os escritores usam apenas bloqueios de curta duração, em vez de bloqueios que são mantidos até o final da transação. Quando utiliza níveis de isolamento mais rígidos, como REPEATABLE READ ou SERIALIZABLE, o mecanismo de base de dados mantém bloqueios de linha e página até ao final da transação, mesmo com o bloqueio otimizado ativado, tanto para leitores como para escritores, resultando em maior bloqueio e uso de memória de bloqueio.

Evite bloquear dicas

Embora dicas de tabela e consulta como UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK, etc. sejam consideradas quando o bloqueio otimizado é ativado, elas reduzem o benefício do bloqueio otimizado. As sugestões de bloqueio forçam o mecanismo de base de dados a adotar bloqueios de linha ou página e mantê-los até ao final da transação, para respeitar a intenção das sugestões de bloqueio. Alguns aplicativos têm lógica onde as dicas de bloqueio são necessárias, por exemplo, ao ler uma linha com a dica UPDLOCK e atualizá-la mais tarde. Recomendamos usar dicas de bloqueio apenas quando necessário.

Com o bloqueio otimizado, não há restrições para consultas existentes e as consultas não precisam ser reescritas. As consultas que não estão usando sugestões beneficiam-se mais do bloqueio otimizado.

Uma indicação de tabela numa tabela numa consulta não desativa o bloqueio otimizado para outras tabelas na mesma consulta. Além disso, o bloqueio otimizado afeta apenas o comportamento de bloqueio de tabelas que estão sendo atualizadas por uma instrução DML, como INSERT, UPDATE, DELETEou 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 pode 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 mantém bloqueios até o final da transação. Outras atualizações para t5 ainda podem beneficiar do bloqueio otimizado. O mesmo se aplica à dica HOLDLOCK.

Perguntas frequentes (FAQ)

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

No Banco de Dados SQL do Azure, sim.

Como posso detetar se o bloqueio otimizado está ativado?

Verifique O bloqueio otimizado está habilitado?.

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

Se o ADR estiver desativado, o bloqueio otimizado também será desativado automaticamente.

E se eu quiser forçar o bloqueio de consultas apesar do bloqueio otimizado?

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

O bloqueio otimizado é utilizado em réplicas secundárias apenas para leitura?

Não, porque 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 feitos.

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

Neste momento, não.

Conteúdo relacionado

• Guia de bloqueio de transações e controle de versão de linhas
• Ler isolamento de instantâneo confirmado (RCSI)
• sys.dm_tran_locks (Transact-SQL)
• Recuperação acelerada do banco de dados