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.

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

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

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

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

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

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