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

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

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

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

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:

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

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

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

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

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

• 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