Compartilhar via

Bloqueio otimizado

  • Artigo

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 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 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 o bloqueio otimizado, atualizar mil linhas em uma tabela pode exigir mil bloqueios de linha exclusivos (X), que são retidos até o final da transação.
  • Com o bloqueio otimizado, atualizar mil linhas em uma tabela pode exigir mil bloqueios de linha X, mas cada bloqueio é liberado assim que a linha é atualizada, e apenas um bloqueio TID é retido 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 somente no Banco de Dados SQL do Azure, 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 de SQL do Azure ou 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:

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

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 garantir as propriedades de transações ACID.

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 múltiplas transações tentam 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 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)

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

Uma captura de tela do conjunto de resultados de uma consulta em sys.dm_tran_locks para uma única sessão mostra apenas um bloqueio quando o bloqueio otimizado está habilitado.

Se o bloqueio otimizado não estiver habilitado, a mesma solicitação conterá quatro bloqueios - três bloqueios de chave X em cada linha e um bloqueio IX (intencional exclusivo) na página que contém as linhas:

Uma captura de tela do conjunto de resultados de uma consulta em sys.dm_tran_locks para uma única sessão mostra três bloqueios quando o bloqueio otimizado não está habilitado.

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.

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

Com base na infraestrutura TID, o bloqueio otimizado altera a forma como as instruções DML, como INSERT, UPDATE, DELETE e MERGE, 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 RCSI (isolamento do instantâneo READ COMMITTED) está habilitado, os predicados são verificação na versão confirmada mais recente da linha sem usar bloqueios. 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 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, pois os bloqueios U não são usados e porque a versão confirmada mais recente da linha 1, coluna a é igual a 1, o que não satisfaz o predicado da sessão 2.

Como com os bloqueios de LAQ U não são obtidos, uma transação simultânea pode modificar a linha depois que o predicado for avaliado. Se o predicado for 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á a 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 for satisfeito, a linha será modificada.

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

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 acima com e sem LAQ (bloqueio após a qualificação).

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, então é ignorada, e a transaçã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 deverão pressupor que o mecanismo de banco de dados garantirá uma ordenação rigorosa sem usar dicas de bloqueio quando níveis de isolamento baseados 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 wait_resource do XACT, com umaa intenção de ler.
      • LCK_M_S_XACT_MODIFY - Ocorre quando uma tarefa está aguardando um bloqueio compartilhado em um tipo wait_resource do XACT, com uma intenção de modificar.
      • LCK_M_S_XACT: ocorre quando uma tarefa está aguardando um bloqueio compartilhado em um tipo wait_resource do XACT, quando a intenção não pode ser inferida. Isso não é comum.
  • Bloqueando a visibilidade dos recursos
  • Aguardar visibilidade do recurso
  • 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 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 LAQ, a consulta aguarda 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 é 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. Ao usar níveis de isolamento mais rígidos, como REPEATABLE READ ou SERIALIZABLE, o mecanismo de banco de dados é forçado a reter bloqueios de linha e de página até o final da transação, mesmo com o bloqueio otimizado habilitado, tanto para leitores como para gravadores, resultando em mais bloqueio e uso de bloqueio de memória.

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 dicas de bloqueio forçam o mecanismo de banco de dados a fazer bloqueios de linha ou 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 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 a consultas existentes, e as consultas não precisam ser reescritas. As consultas que não usarem dicas aproveitam melhor o bloqueio otimizado.

Uma dica de tabela em uma tabela de uma consulta não desabilita 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 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 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, 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 usados.

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

Não no momento.

Conteúdo relacionado