Guia de controle de versão de linha e bloqueio de transações

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

Em um banco de dados, o gerenciamento incorreto de transações normalmente leva a problemas de contenção e de desempenho em sistemas com muitos usuários. À medida que o número de usuários que acessam os dados aumenta, torna-se importante ter aplicativos que utilizem as transações de maneira eficaz. Este guia descreve os mecanismos de bloqueio e controle de versão de linha que o Mecanismo de Banco de Dados do SQL Server usa para garantir a integridade física de cada transação e fornece informações sobre como os aplicativos podem controlar as transações de forma eficiente.

Observação

O bloqueio otimizado é um recurso do Mecanismo de Banco de Dados introduzido em 2023 que reduz drasticamente a memória de bloqueio e o número de bloqueios necessários para gravações simultâneas. Este artigo foi atualizado para descrever o Mecanismo de Banco de Dados do SQL Server com e sem bloqueio otimizado. Atualmente, o bloqueio otimizado está disponível apenas no Banco de Dados SQL do Azure.

• Para obter mais informações e saber onde o bloqueio otimizado está disponível, confira Bloqueio otimizado.
• Para determinar se o bloqueio otimizado está habilitado no seu banco de dados, consulte O bloqueio otimizado está habilitado?

O bloqueio otimizado atualizou significativamente algumas seções deste artigo, incluindo:

• Bloqueio no Mecanismo de Banco de Dados
• Operação de exclusão
• Operação de Inserção
• Escalonamento de bloqueios
• Reduzir o bloqueio e o escalonamento
• Comportamento durante a modificação de dados
• Comportamento no resumo
• Dicas de bloqueio

Noções básicas de transações

Uma transação é uma sequência de operações executadas como uma única unidade lógica de trabalho. Uma unidade lógica de trabalho deve mostrar quatro propriedades, designadas pelas iniciais ACID (atomicidade, consistência, isolamento e durabilidade), para que seja qualificada como uma transação.

Atomicidade
Uma transação deve ser uma unidade atômica de trabalho; ou todas as suas modificações de dados são executadas ou nenhuma delas é executada.

Consistência
Quando concluída, uma transação deve deixar todos os dados em um estado consistente. Em um banco de dados relacional, todas as regras devem ser aplicadas às modificações da transação para manter toda a integridade dos dados. Todas as estruturas de dados internas, tais como índices em árvore B ou listas duplamente vinculadas, devem estar corretas ao término da transação.

Observação

A documentação do SQL Server usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o SQL Server implementa uma árvore B+. Isso não se aplica a índices columnstore ou armazenamentos de dados na memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.

Isolamento
Modificações feitas por transações simultâneas devem ser isoladas das modificações feitas por qualquer outra transação simultânea. Uma transação reconhece os dados no estado em que estavam antes de outra transação simultânea tê-los modificado ou reconhece os dados depois que a segunda transação tiver sido concluída, mas não reconhece um estado intermediário. Isso é chamado serializabilidade porque resulta na capacidade de recarregar os dados iniciais e reexecutar uma série de transações de modo que os dados obtidos estejam no mesmo estado em que estavam depois que as transações originais foram executadas.

Durabilidade
Depois que uma transação totalmente durável tiver sido concluída, seus efeitos ficam permanentemente no sistema. As modificações persistem até mesmo no caso de uma queda do sistema. O SQL Server 2014 (12.x) e versões posteriores permitem transações duradouras atrasadas. As transações duráveis atrasadas são confirmadas antes do registro de log de transação ser persistente no disco. Para obter mais informações sobre durabilidade de transações atrasadas, confira o artigo Durabilidade da transação de controle.

Os programadores SQL são responsáveis por iniciar e terminar transações em pontos que imponham a consistência lógica dos dados. O programador deve definir a sequência de modificações de dados que deixem os dados em um estado consistente em relação às regras comerciais da organização. O programador inclui essas instruções de modificação em uma única transação para que o Mecanismo de Banco de Dados do SQL Server possa impor a integridade física da transação.

É responsabilidade de um sistema de banco de dados corporativo, como uma instância do Mecanismo de Banco de Dados do SQL Server, fornecer mecanismos que garantam a integridade física de cada transação. O Mecanismo de Banco de Dados do SQL Server fornece:

• Recursos de bloqueio que preservam o isolamento da transação.

• Recursos de log garantem a durabilidade da transação. Para transações completamente duráveis, o registro de log é protegido no disco antes da confirmação das transações. Assim, mesmo que o hardware do servidor, o sistema operacional ou a instância do próprio Mecanismo de Banco de Dados do SQL Server falhe, a instância usará os logs de transação na reinicialização para reverter automaticamente todas as transações incompletas até o ponto da falha do sistema. As transações duráveis atrasadas confirmar antes que o registro de log de transação é protegido no disco. Essas transações podem ser perdidas se houver uma falha do sistema antes de o registro de log ser protegido no disco. Para obter mais informações sobre durabilidade de transações atrasadas, confira o artigo Durabilidade da transação de controle.

• Recursos de administração de transação que impõem a atomicidade e a consistência da transação. Após o início de uma transação, ela deve ser concluída com êxito (receber commit), ou o Mecanismo de Banco de Dados do SQL Server reverterá todas as modificações de dados feitas desde o início da transação. Essa operação é denominada de reversão da transação porque retorna os dados ao estado anterior a essas alterações.

Controlar transações

Os aplicativos controlam transações principalmente ao especificar quando uma transação começa e termina. Isso pode ser especificado por meio de instruções Transact-SQL ou de funções da interface de programação de aplicativo (API) do banco de dados. O sistema também deve ser capaz de processar corretamente os erros que encerram uma transação antes de sua conclusão. Para obter mais informações, confira Transações, Como executar transações no ODBC e Transações no SQL Server Native Client.

Por padrão, as transações são gerenciadas no nível de conexão. Quando uma transação é iniciada em uma conexão, todas as instruções Transact-SQL executadas nessa conexão fazem parte da transação até que ela termine. No entanto, em uma sessão de conjuntos de resultados ativos múltiplos (MARS), uma transação Transact-SQL explícita ou implícita se torna uma transação com escopo de lote que é gerenciada no nível do lote. Quando o lote for concluído, se a transação com escopo de lote não for confirmada ou revertida, ela será automaticamente revertida pelo SQL Server. Para obter mais informações, consulte Usando MARS (vários conjuntos de resultados ativos).

Iniciar transações

Usando funções da API e instruções Transact-SQL, é possível iniciar transações em uma instância do Mecanismo de Banco de Dados do SQL Server como transações explícitas, de confirmação automática ou implícitas.

Transações explícitas
Uma transação explícita é aquela em que você define explicitamente o início e o fim da transação por meio de uma função de API ou emitindo as instruções Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION ou ROLLBACK WORK. Quando a transação terminar, a conexão volta ao modo de transação em que estava antes de a transação explícita ser iniciada, seja o modo implícito ou de confirmação automática.

É possível usar todas as instruções Transact-SQL em uma transação explícita, exceto as instruções a seguir:

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX …
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• Procedimentos armazenados do sistema de texto completo
• sp_dboption para definir as opções do banco de dados ou qualquer procedimento do sistema que modifique o banco de dados master dentro de transações explícitas ou implícitas.

Observação

UPDATE STATISTICS pode ser usada dentro de uma transação explícita. Mas, ela será confirmada independentemente da transação envolvida e não poderá ser revertida.

Transações de Confirmação Automática
O modo de confirmação automática é o modo padrão de gerenciamento de transações do mecanismo de banco de dados do SQL Server. Toda instrução Transact-SQL é confirmada ou revertida quando concluída. Se uma instrução for concluída com sucesso, será confirmada; se encontrar qualquer erro, será revertida. Uma conexão com uma instância do Mecanismo de Banco de Dados do SQL Server vai operar no modo de confirmação automática sempre que esse modo padrão não tiver sido substituído por transações explícitas ou implícitas. O modo de confirmação automática também é o modo padrão para ADO, OLE DB, ODBC e DB-Library.

Transações Implícitas
Quando uma conexão está operando no modo de transação implícita, a instância do Mecanismo de Banco de Dados do SQL Server inicia automaticamente uma nova transação depois que a transação atual é confirmada ou revertida. Você não faz nada para determinar o início de uma transação; apenas confirma ou reverte cada uma das transações. O modo de transação implícita gera uma cadeia contínua de transações. Defina o modo de transação implícita por meio de uma função de API ou da instrução Transact-SQL SET IMPLICIT_TRANSACTIONS ON. Esse modo também é conhecido como Autocommit OFF, confira Método setAutoCommit (SQLServerConnection).

Depois que o modo de transação implícita tiver sido ativado para uma conexão, a instância do Mecanismo de Banco de Dados do SQL Server iniciará automaticamente uma transação quando executar qualquer uma dessas instruções pela primeira vez:

• ALTER TABLE

• CREATE

• DELETE

• DROP

• FETCH

• GRANT

• INSERT

• OPEN

• REVOKE

• SELECT

• TRUNCATE TABLE

• UPDATE

• Transações de escopo de lote Aplicáveis apenas a MARS (conjuntos de resultados ativos múltiplos), uma transação Transact-SQL explícita ou implícita iniciada em uma sessão MARS se torna uma transação de escopo de lote. Uma transação de escopo de lote que não é confirmada ou revertida quando um lote é concluído é revertida automaticamente pelo SQL Server.

• Transações distribuídas Transações distribuídas abrangem dois ou mais servidores conhecidos como gerenciadores de recursos. O gerenciamento da transação deve ser coordenado entre os gerenciadores de recursos por um componente de servidor chamado de gerenciador de transações. Cada instância do Mecanismo de Banco de Dados do SQL Server pode operar como um gerenciador de recursos em transações distribuídas coordenadas por gerenciadores de transações, como o Coordenador de Transações Distribuídas da Microsoft (MS DTC), ou outros gerenciadores de transações que dão suporte à especificação XA do Open Group para processamento de transações distribuídas. Para obter mais informações, consulte a documentação do MS DTC.

  Uma transação em uma única instância do Mecanismo de Banco de Dados do SQL Server que abrange dois ou mais bancos de dados é, na verdade, uma transação distribuída. A instância gerencia a transação distribuída internamente. Para o usuário, ela opera como uma transação local.

  No aplicativo, uma transação distribuída é gerenciada da mesma forma como uma transação local. No final da transação, o aplicativo solicita que a transação seja confirmada ou revertida. Uma confirmação distribuída deve ser gerenciada de forma diferenciada pelo gerenciador de transações para minimizar o risco de que uma falha de rede possa resultar em alguns gerenciadores de recurso que confirmam com êxito enquanto outros revertem a transação. Isso é obtido pelo gerenciamento do processo de confirmação em duas fases (a fase de preparação e a fase de confirmação), o que é conhecido como um protocolo 2PC.

  • Fase de preparação Quando o gerenciador de transações recebe uma solicitação de confirmação, ele envia um comando de preparação a todos os gerenciadores de recursos envolvidos na transação. Cada gerenciador executa todas as ações necessárias para tornar a transação durável, e todos os buffers que mantêm imagens de log da transação são liberados no disco. À medida que cada gerenciador de recursos conclui a fase de preparação, ele retorna informações de êxito ou de falha ao gerenciador de transações. O SQL Server 2014 (12.x) introduziu a durabilidade da transação atrasada. As transações duráveis atrasadas são confirmadas antes de imagens de log da transação serem liberadas para o disco. Para obter mais informações sobre durabilidade de transações atrasadas, confira o artigo Durabilidade da transação de controle.

  • Fase de confirmação Se o gerenciador de transações receber preparações com êxito de todos os gerenciadores de recursos, ele enviará comandos de confirmação para cada gerenciador de recursos. Em seguida, os gerenciadores de recursos podem concluir a confirmação. Se todos os gerenciadores de recursos relatarem uma confirmação bem-sucedida, o gerenciador de transações enviará uma notificação de êxito ao aplicativo. Se um gerenciador de recursos informar uma falha na preparação, o gerenciador de transações enviará um comando de reversão a cada gerenciador de recursos e indicará a falha da confirmação ao aplicativo.

    Os aplicativos do Mecanismo de Banco de Dados do SQL Server podem gerenciar transações distribuídas por meio do Transact-SQL ou da API do banco de dados. Para obter mais informações, confira BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Encerrar transações

Você pode finalizar transações com uma instrução COMMIT ou ROLLBACK ou com uma função de API correspondente.

• COMMIT Se uma transação for concluída com êxito, faça commit nela. Uma instrução COMMIT garante que todas as modificações na transação fazem parte permanente do banco de dados. Um COMMIT também libera recursos, como bloqueios, usados pela transação.

• ROLLBACK Se ocorrer um erro em uma transação ou se o usuário decidir cancelá-la, reverta-a. Uma instrução ROLLBACK desfaz todas as modificações feitas na transação retornando os dados ao estado anterior ao início da transação. Um ROLLBACK também libera recursos usados pela transação.

Observação

Em conexões habilitadas para oferecer suporte a vários conjuntos de resultados ativos (MARS), uma transação explícita iniciada por uma função de API não pode ser confirmada enquanto houver solicitações pendentes para execução. Qualquer tentativa de confirmação desse tipo de transação enquanto houver operações pendentes sendo executadas resultará em um erro.

Erros durante o processamento de transações

Se um erro impedir a conclusão bem-sucedida de uma transação, o SQL Server reverterá automaticamente a transação e liberará todos os recursos mantidos por ela. Se a conexão de rede do cliente com uma instância do Mecanismo de Banco de Dados do SQL Server for interrompida, todas as transações pendentes para essa conexão serão revertidas quando a rede notificar a instância sobre a interrupção. Se o aplicativo cliente falhar ou se o computador cliente ficar inoperante ou for reiniciado, isso também interromperá a conexão, e a instância do Mecanismo de Banco de Dados do SQL Server reverterá todas as conexões pendentes quando a rede notificar sobre a interrupção. Se o cliente sair do aplicativo, todas as transações pendentes serão revertidas.

Se ocorrer um erro de instrução em tempo de execução (como uma violação de restrição) em um lote, o comportamento padrão no Mecanismo de Banco de Dados do SQL Server será reverter apenas a instrução que gerou o erro. Você pode alterar esse comportamento usando a instrução SET XACT_ABORT. Depois que SET XACT_ABORT ON for executada, qualquer erro de instrução em tempo de execução fará com que a transação atual seja revertida. Erros de compilação, como erros de sintaxe, não são afetados por SET XACT_ABORT. Para obter mais informações, veja SET XACT_ABORT (Transact-SQL).

Quando ocorrerem erros, a ação corretiva (COMMIT ou ROLLBACK) deve ser incluída em um código de aplicativo. Uma ferramenta eficaz para tratar erros, inclusive os de transações, é o constructo Transact-SQL TRY...CATCH. Para obter mais informações com exemplos que incluem transações, veja TRY...CATCH (Transact-SQL). A partir do SQL Server 2012 (11.x), você pode usar a instrução THROW para levantar uma exceção e transferir a execução para um bloco CATCH de um constructo TRY...CATCH. Para obter mais informações, veja THROW (Transact-SQL).

Erros em tempo de execução e de compilação no modo de confirmação automática

No modo de confirmação automática, às vezes parece que uma instância do Mecanismo de Banco de Dados do SQL Server reverteu um lote inteiro em vez de apenas uma instrução SQL. Isto acontece se o erro encontrado for um erro de compilação, não um erro em tempo de execução. Um erro de compilação impede que o Mecanismo de Banco de Dados do SQL Server crie um plano de execução, portanto, nada no lote é executado. Embora pareça que todas as instruções antes daquela que gerou o erro tenham sido revertidas, o erro impediu que tudo no lote fosse executado. No exemplo a seguir, nenhuma das instruções INSERT no terceiro lote foram executadas por causa de um erro de compilação. Parece que as primeiras duas instruções INSERT foram revertidas, mas elas nunca foram executadas.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

No exemplo a seguir, a terceira instrução INSERT gera um erro de duplicação de chave primária em tempo de execução. As primeiras duas instruções INSERT têm êxito e são confirmadas; portanto, elas permanecem depois do erro em tempo de execução.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

O Mecanismo de Banco de Dados do SQL Server usa a resolução de nomes diferida, na qual os nomes de objetos não são resolvidos até o momento da execução. No exemplo a seguir, as primeiras duas instruções INSERT são executadas e confirmadas, e essas duas linhas permanecem na tabela TestBatch depois que a terceira instrução INSERT gera um erro em tempo de execução, referindo-se a uma tabela que não existe.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Noções básicas sobre bloqueio e controle de versão de linha

O Mecanismo de Banco de Dados do SQL Server usa os seguintes mecanismos para garantir a integridade das transações e manter a consistência dos bancos de dados quando vários usuários acessam os dados ao mesmo tempo:

• Bloqueio

  Cada transação solicita bloqueios de tipos diferentes nos recursos, como linhas, páginas ou tabelas, dos quais depende a transação. Os bloqueios não permitem que outras transações modifiquem os recursos de uma maneira que causaria problemas para a transação que solicita o bloqueio. Cada transação libera seus bloqueios quando não depende mais dos recursos bloqueados.

• Controle de versão de linha

  Quando um nível de isolamento baseado em controle de versão de linha é habilitado, o Mecanismo de Banco de Dados do SQL Server mantém versões de cada linha modificada. Os aplicativos podem especificar que uma transação use as versões da linha para exibir dados da forma como eram no início da transação ou consulta ao invés de proteger todas as leituras com bloqueios. Usando o controle de versão de linha, a possibilidade de uma operação de leitura bloquear outras transações é muito reduzida.

O bloqueio e o controle de versão de linha impedem que os usuários leiam dados não confirmados e impedem que vários usuários tentem alterar os mesmos dados ao mesmo tempo. Sem o bloqueio ou o controle de versão de linha, as consultas executadas em relação a esses dados podem produzir resultados inesperados retornando dados que ainda não foram confirmados no banco de dados.

Os aplicativos podem escolher níveis de isolamento da transação que definem o nível de proteção da transação contra efeitos de modificações feitas por outras transações. As dicas em nível de tabela podem ser especificadas para instruções Transact-SQL individuais para adaptar ainda mais o comportamento aos requisitos do aplicativo.

Gerenciar o acesso simultâneo aos dados

Os usuários que acessam um recurso ao mesmo tempo estão acessando o recurso simultaneamente. O acesso simultâneo a dados exige mecanismos para impedir efeitos adversos quando vários usuários tentam modificar recursos que outros usuários estão utilizando.

Efeitos de simultaneidade

Os usuários que modificam dados podem afetar outros usuários que estejam lendo ou modificando os mesmos dados ao mesmo tempo. Dizemos que esses usuários estão acessando os dados simultaneamente. Se um sistema de armazenamento de dados não tiver nenhum controle de simultaneidade, os usuários verão os seguintes efeitos colaterais:

• Atualizações perdidas

  As atualizações perdidas acontecem quando duas ou mais transações selecionam a mesma linha e então atualizam a linha com base no valor selecionado originalmente. Cada transação não tem conhecimento das outras transações. A última atualização substitui atualizações feitas pelas outras transações, o que resulta em dados perdidos.

  Por exemplo, dois editores fazem uma cópia eletrônica do mesmo documento. Cada editor altera a cópia de maneira independentemente e salva a cópia alterada, substituindo, portanto, o documento original. O editor que salva a cópia alterada por último substitui as alterações feitas pelo outro editor. Esse problema poderia ser evitado se um editor não pudesse acessar o arquivo até que o outro editor tivesse terminado e confirmado a transação.

• Dependência não confirmada (leitura suja)

  A dependência não confirmada acontece quando uma segunda transação seleciona uma linha que está sendo atualizada por outra transação. A segunda transação está lendo dados que não foram confirmados ainda e podem ser alterados pela transação que atualiza a linha.

  Por exemplo, um editor está fazendo mudanças em um documento eletrônico. Durante as mudanças, um segundo editor pega uma cópia do documento que inclui todas as mudanças feitas até o momento e distribui o documento para a audiência destinada. O primeiro editor decide então que as mudanças feitas até o momento estão erradas, remove as edições e salva o documento. O documento distribuído contém edições que já não existem e que deveriam ser tratadas como se nunca tivessem existido. Esse problema poderia ser evitado se ninguém pudesse ler o documento alterado até que o primeiro editor salvasse a versão final com as modificações e confirmasse a transação.

• Análise inconsistente (leitura não repetível)

  Ocorre análise inconsistente quando uma segunda transação acessa a mesma linha várias vezes e lê dados diferentes a cada vez. A análise inconsistente é semelhante à dependência não confirmada, no sentido em que outra transação está alterando os dados que uma segunda transação está lendo. No entanto, na análise inconsistente os dados lidos pela segunda transação foram confirmados pela transação que fez as alterações. Além disso, a análise inconsistente envolve leituras múltiplas (duas ou mais) da mesma fila, e a cada vez as informações são alterada por outra transação; daí a denominação leitura não repetível.

  Por exemplo, um editor lê o mesmo documento duas vezes, mas entre cada leitura o escritor reescreve o documento. Quando o editor lê o documento pela segunda vez, este já foi alterado. A leitura original não era repetível. Esse problema poderia ser evitado se o gravador não pudesse alterar o documento até que o editor tivesse terminado de lê-lo pela última vez.

• Leituras fantasmas

  A leitura fantasma é uma situação que ocorre quando são executadas duas consultas idênticas e a coleção de linhas retornada pela segunda consulta é diferente. O exemplo abaixo mostra como isso pode ocorrer. Suponha que as duas transações abaixo sejam executadas ao mesmo tempo. As duas instruções SELECT na primeira transação podem retornar resultados diferentes, pois a instrução INSERT na segunda transação altera os dados usados por ambas.

  --Transaction 1
  BEGIN TRAN;
  SELECT ID FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  --The INSERT statement from the second transaction occurs here.
  SELECT ID FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee
    (Id, Name) VALUES(6 ,'New');
  COMMIT;
  

• Leituras ausentes e duplicadas provocadas por atualizações de linha

  • Ignorar ou ver uma linha atualizada várias vezes

    Transações que estejam sendo executadas no nível READ UNCOMMITTED não emitem bloqueios compartilhados para impedir que outras transações modifiquem os dados lidos pela transação atual. Transações que estejam sendo executadas no nível READ COMMITTED emitem bloqueios compartilhados, mas os bloqueios de linha ou de página são liberados depois que a linha é lida. Em qualquer dos dois casos, quando você estiver percorrendo um índice, se outro usuário alterar a coluna de chave de índice da linha durante sua leitura, a linha poderia aparecer novamente se a alteração de chave movesse a linha para uma posição à frente de sua leitura. De maneira semelhante, a linha poderia não aparecer se a alteração de chave movesse a linha para uma posição no índice que você já tinha lido. Para evitar isso, use a dica SERIALIZABLE ou HOLDLOCK ou o controle de versão de linha. Para obter mais informações, confira Dicas de tabela (Transact-SQL).

  • Perdendo uma ou mais linhas que não eram o destino da atualização

    Quando você estiver usando READ UNCOMMITTED, se sua consulta ler linhas que usem uma varredura de ordem de alocação (usando páginas IAM), você poderá perder linhas se outra transação estiver provocando uma divisão de página. Isso não pode acontecer quando você está usando leitura confirmada porque um bloqueio de tabela é mantido durante uma divisão de página e não ocorre se a tabela não tem um índice clusterizado, pois as atualizações não provocam divisões de página.

Tipos de simultaneidade

Quando muitas pessoas tentam modificar dados em um banco de dados ao mesmo tempo, um sistema de controles deve ser implementado de forma que as modificações feitas por uma pessoa não afetem adversamente as de outra pessoa. Isso é chamado controle de simultaneidade.

A teoria do controle de simultaneidade tem duas classificações para os métodos de instituição do controle de simultaneidade:

• Controle de simultaneidade pessimista

  Um sistema de bloqueios impede que os usuários modifiquem os dados de uma forma que afete outros usuários. Depois que um usuário executa uma ação que aplica um bloqueio, outros usuários não podem executar ações que estariam em conflito com o bloqueio até o proprietário liberar o bloqueio. Isso é chamado de controle pessimista porque é principalmente usado em ambientes em que existe contenção elevada dos dados, e que o custo da proteção dos dados com bloqueios é inferior ao custo de reversão de transações, caso ocorram conflitos de simultaneidade.

• Controle de simultaneidade otimista

  No controle de simultaneidade otimista, os usuários não bloqueiam os dados quando os leem. Quando um usuário atualiza os dados, o sistema verifica se outro usuário alterou os dados depois de lidos. Se outro usuário tiver atualizado os dados, um erro é ativado. Normalmente, o usuário que recebe o erro reverte a transação e inicia novamente. Isso é chamado de controle otimista porque é usado principalmente em ambientes em que existe contenção reduzida dos dados, e que o custo de reversão ocasional de uma transação é inferior ao custo de bloqueio dos dados quando os mesmos são lidos.

O SQL Server oferece suporte a uma série de controles de simultaneidade. Os usuários especificam o tipo de controle de simultaneidade selecionando níveis de isolamento da transação para conexões ou opções de simultaneidade em cursores. Esses atributos podem ser definidos usando instruções Transact-SQL ou por meio das propriedades e atributos das interfaces de programação de aplicativos (APIs) de banco de dados, como ADO, ADO.NET, OLE DB e ODBC.

Níveis de isolamento no Mecanismo de Banco de Dados do SQL Server

As transações especificam um nível de isolamento que define o grau em que uma transação deve ser isolada contra modificações de recursos ou de dados feitas por outras transações. Os níveis de isolamento são descritos em termos de quais efeitos colaterais de simultaneidade são permitidos, como leituras sujas ou leituras fantasma.

Níveis de isolamento da transação controlam:

• Se os bloqueios são adquiridos quando os dados são lidos e que tipo de bloqueios são solicitados.
• Por quanto tempo os bloqueios de leitura são mantidos.
• Se uma operação de leitura faz referência a linhas modificadas por outra transação:
  • Bloqueia até que o bloqueio exclusivo na linha seja liberado.
  • Recupera a versão confirmada da linha existente no momento em que a instrução ou transação foi iniciada.
  • Lê a modificação de dados não confirmados.

Importante

Escolhendo um nível de isolamento da transação não afeta os bloqueios obtidos para proteger as modificações de dados. Uma transação sempre obtém um bloqueio exclusivo em quaisquer dados que modifica e mantém tal bloqueio até que a transação seja concluída, sem considerar o conjunto de níveis de isolamento para a transação em questão. Para operações de leitura, níveis de isolamento da transação definem principalmente o nível de proteção dos efeitos das modificações feitas por outras transações.

Um nível de isolamento inferior aumenta a capacidade de muitos usuários acessarem dados ao mesmo tempo, mas aumenta o número de efeitos de simultaneidade (como leituras sujas ou atualizações perdidas) que os usuários podem encontrar. Inversamente, um nível de isolamento mais alto reduz os tipos de efeito de simultaneidade que os usuários podem encontrar, mas requer mais recursos do sistema e aumenta as chances de uma transação bloquear outra. Escolher o nível de isolamento apropriado depende de equilibrar os requisitos de integridade de dados do aplicativo em relação à sobrecarga de cada nível de isolamento. O nível de isolamento mais alto, serializável, garante que uma transação recuperará exatamente os mesmos dados toda vez que repetir uma operação de leitura, mas faz isto executando um nível de bloqueio que provavelmente causará impacto em outros usuários em sistemas multiusuários. O mais baixo nível de isolamento, leitura de dados não confirmados, pode recuperar dados que foram modificados mas não foram confirmados por outras transações. Todos os efeitos colaterais de simultaneidade podem acontecer em leitura não confirmada, mas não há nenhum bloqueio de leitura ou controle de versão, assim a sobrecarga é minimizada.

Níveis de isolamento do Mecanismo de Banco de Dados

O padrão ISO define os seguintes níveis de isolamento, todos eles compatíveis com o Mecanismo de Banco de Dados do SQL Server:

Nível de Isolamento	Definição
Leitura não confirmada	O nível de isolamento mais baixo, no qual as transações só estão isoladas o suficiente para assegurar que dados corrompidos fisicamente não sejam lidos. Nesse nível, são permitidas leituras sujas, para que uma transação tenha acesso às alterações ainda não confirmadas de outras transações.
Leitura confirmada	Permite que uma transação leia dados lidos anteriormente (não modificados) por outra transação, sem esperar pela conclusão da primeira transação. O Mecanismo de Banco de Dados do SQL Server mantém os bloqueios de gravação (adquiridos nos dados selecionados) até o final da transação, mas os bloqueios de leitura são liberados assim que a operação SELECT é executada. Esse é o nível padrão do Mecanismo de Banco de Dados do SQL Server.
Leitura repetível	O Mecanismo de Banco de Dados do SQL Server mantém os bloqueios de leitura e gravação que são adquiridos nos dados selecionados até o final da transação. Contudo, poderão ocorrer leituras fantasmas, pois os bloqueios de intervalo não são gerenciados.
Serializável	O nível mais alto, no qual as transações estão completamente isoladas umas das outras. O Mecanismo de Banco de Dados do SQL Server mantém bloqueios de leitura e gravação adquiridos em dados selecionados para serem liberados no final da transação. Os bloqueios de intervalo são adquiridos quando uma operação SELECT usa uma cláusula WHERE em intervalo, sobretudo para evitar leituras fantasmas. Observação: pode haver falha em operações e transações DDL em tabelas replicadas quando o nível de isolamento serializável é solicitado. Isso ocorre porque as consultas de replicação usam dicas que podem ser incompatíveis com o nível de isolamento serializável.

O SQL Server também oferece suporte a dois níveis adicionais de isolamento de transação que usam controle de versão de linha. Um é uma implementação do isolamento READ COMMITTED e o outro é um nível de isolamento de transação, instantâneo.

Nível de isolamento do controle de versão de linha	Definição
Instantâneo de leitura confirmada (RCSI)	Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT é definida como ON, o isolamento READ COMMITTED usa o controle de versão de linha para fornecer consistência de leitura em nível de instrução. Operações de leitura só requerem bloqueios de nível de tabela SCH-S e nenhum bloqueio de página ou linha. Ou seja, o Mecanismo de Banco de Dados do SQL Server usa o controle de versão de linha para apresentar a cada instrução um instantâneo consistente com a transação dos dados, conforme existiam no início da instrução. Não são usados bloqueios para proteger os dados contra atualizações efetuadas por outras transações. Uma função definida pelo usuário pode retornar dados confirmados depois do horário de início da instrução que contém que o UDF. Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT está definida como OFF, que é a configuração padrão, o isolamento READ COMMITTED usa bloqueios compartilhados para impedir que outras transações modifiquem linhas enquanto a transação atual estiver executando uma operação de leitura. Os bloqueios compartilhados também bloqueiam a instrução de ler linhas modificadas por outras transações até que a outra transação seja concluída. Ambas as implementações atendem à definição ISO de isolamento READ COMMITTED.
Instantâneo	O nível de isolamento do instantâneo usa controle de versão de linha para fornecer consistência de leitura em nível de transação. Operações de leitura não requerem bloqueios de página ou linha; apenas bloqueios de tabela SCH-S são necessários. Ao ler linhas modificadas por outra transação, elas recuperam a versão da linha que existia na inicialização da transação. Você apenas pode usar o isolamento de instantâneo em relação a um banco de dados quando a opção ALLOW_SNAPSHOT_ISOLATION de banco de dados estiver definida como ON. Por padrão, essa opção é definida como OFF para bancos de dados de usuários. Observação: o SQL Server não é compatível com o controle de versão de metadados. Por isso, há restrições nas operações de DDL que podem ser executadas em uma transação explícita que está sendo executada sob isolamento do instantâneo. As instruções de DDL a seguir não são permitidas sob isolamento do instantâneo depois de uma instrução BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME ou qualquer instrução CRL (Common Language Runtime) DDL. Essas instruções serão permitidas quando você estiver usando o isolamento de instantâneo em transações implícitas. Uma transação implícita, por definição, é uma instrução única que torna possível impor semânticas de isolamento do instantâneo, até mesmo com instruções DDL. Violações desse princípio podem causar o erro 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

A tabela a seguir mostra os efeitos colaterais de simultaneidade habilitados por níveis de isolamento diferentes.

Nível de isolamento	Leitura suja	Leitura não repetível	Fantasma
Leitura não confirmada	Sim	Sim	Sim
Leitura confirmada	Não	Sim	Sim
Leitura repetível	Não	No	Sim
Instantâneo	Não	No	Não
Serializável	Não	No	Não

Para obter mais informações sobre os tipos específicos de controle de versão de linha ou bloqueio controlado pelos níveis de isolamento de transação, veja SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Os níveis de isolamento da transação podem ser definidos usando o Transact-SQL ou por meio de uma API do banco de dados.

Transact-SQL
Os scripts Transact-SQL usam a instrução SET TRANSACTION ISOLATION LEVEL.

ADO
Os aplicativos ADO definem a propriedade IsolationLevel do objeto Connection como adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, ou adXactReadSerializable.

ADO.NET
Os aplicativos ADO.NET que usam o namespace gerenciado System.Data.SqlClient podem chamar o método SqlConnection.BeginTransaction e definir a opção IsolationLevel como Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, ou Snapshot.

OLE DB
Ao iniciar uma transação, os aplicativos que usam OLE DB chamam ITransactionLocal::StartTransaction com isoLevel definido como ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT ou ISOLATIONLEVEL_SERIALIZABLE.

Ao especificar o nível de isolamento da transação no modo de confirmação automática, os aplicativos OLE DB podem definir a propriedade DBPROPSET_SESSION como DBPROP_SESS_AUTOCOMMITISOLEVELSDBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED ou DBPROPVAL_TI_SNAPSHOT.

ODBC
Os aplicativos ODBC chamam SQLSetConnectAttr com Attribute definido como SQL_ATTR_TXN_ISOLATION e ValuePtr definido como SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ ou SQL_TXN_SERIALIZABLE.

Para transações de instantâneos, os aplicativos chamam SQLSetConnectAttr com Atributo definido como SQL_COPT_SS_TXN_ISOLATION e ValuePtr definido como SQL_TXN_SS_SNAPSHOT. Uma transação de instantâneo pode ser recuperada usando SQL_COPT_SS_TXN_ISOLATION ou SQL_ATTR_TXN_ISOLATION.

Bloqueio no Mecanismo de Banco de Dados

O bloqueio é um mecanismo usado pelo Mecanismo de Banco de Dados do SQL Server para sincronizar o acesso de vários usuários à mesma parte dos dados ao mesmo tempo.

Antes que uma transação adquira uma dependência de uma parte dos dados no estado atual, como por ler ou modificar os dados, ela deve se proteger contra os efeitos de outra transação que modifique os mesmos dados. A transação faz isso, solicitando um bloqueio na parte dos dados. Os bloqueios têm modos diferentes, como compartilhado ou exclusivo. O modo de bloqueio define o nível de dependência que a transação tem nos dados. Nenhuma transação pode receber um bloqueio que conflite com o modo de um bloqueio já atribuído àqueles dados por outra transação. Se uma transação solicitar um modo de bloqueio que entra em conflito com um bloqueio já concedido nos mesmos dados, a instância do Mecanismo de Banco de Dados do SQL Server pausará a transação solicitante até que o primeiro bloqueio seja liberado.

Quando uma transação modifica uma parte dos dados, ela mantém determinados bloqueios que protegem a modificação até o final da transação. O tempo que uma transação mantém os bloqueios adquiridos para proteger as operações de leitura depende da configuração do nível de isolamento da transação e se o bloqueio otimizado está habilitado.

• Quando o bloqueio otimizado não está habilitado, os bloqueios de linha e de página necessários para gravações são mantidos até o final da transação.

• Quando o bloqueio otimizado está habilitado, somente um bloqueio de ID de transação (TID) é mantido durante a duração da transação. No nível de isolamento padrão, as transações não manterão os bloqueios de linha e de página necessários para gravações até o final da transação. Isso reduz a memória de bloqueio necessária e reduz a necessidade de escalonamento de bloqueios. Além disso, quando o bloqueio otimizado está habilitado, a otimização do bloqueio após a qualificação (LAQ) avalia os predicados de uma consulta na última versão com commit da linha sem adquirir um bloqueio, melhorando a simultaneidade.

Todos os bloqueios mantidos por uma transação são liberados quando a transação for concluída (confirma ou reverte).

Os aplicativos normalmente não solicitam bloqueios diretamente. Bloqueios são gerenciados internamente por uma parte do Mecanismo de Banco de Dados do SQL Server chamada de gerenciador de bloqueios. Quando uma instância do Mecanismo de Banco de Dados do SQL Server processa uma instrução Transact-SQL, o processador de consultas do Mecanismo de Banco de Dados do SQL Server determina quais recursos devem ser acessados. O processador de consulta determina que tipos de bloqueio são necessários para proteger cada recurso baseado no tipo de acesso e no nível configurado de isolamento da transação. O processador de consulta solicita os bloqueios apropriados ao gerenciador de bloqueio. O gerenciador de bloqueio concede os bloqueios, se não houver bloqueios conflitantes mantidos por outras transações.

Bloqueio de granularidade e hierarquias

O Mecanismo de Banco de Dados do SQL Server tem bloqueio multigranular que permite que diferentes tipos de recursos sejam bloqueados por uma transação. Para minimizar o custo do bloqueio, o Mecanismo de Banco de Dados do SQL Server bloqueia os recursos automaticamente em um nível apropriado para a tarefa. Bloquear em uma granularidade menor como, por exemplo linhas, aumenta a concorrência mas tem uma sobrecarga maior, devido à exigência de mais bloqueios mantidos, se muitas linhas forem bloqueadas. Bloqueando em uma granularidade maior, como tabelas, é dispendioso em termos de simultaneidade, porque bloquear a tabela inteira restringe o acesso a qualquer parte da tabela por outras transações. No entanto, tem uma sobrecarga menor, porque menos bloqueios estão sendo mantidos.

O Mecanismo de Banco de Dados do SQL Server geralmente precisa adquirir bloqueios em vários níveis de granularidade para proteger totalmente um recurso. Esse grupo de bloqueios em vários níveis de granularidade é chamado de uma hierarquia de bloqueio. Por exemplo, para proteger totalmente a leitura de um índice, uma instância do Mecanismo de Banco de Dados do SQL Server pode ter que adquirir bloqueios de compartilhamento em linhas e bloqueios de tentativa compartilhada nas páginas e na tabela.

A tabela a seguir mostra os recursos que o Mecanismo de Banco de Dados do SQL Server pode bloquear.

Recurso	Descrição
RID	Um identificador de linha usado para bloquear uma única linha dentro de um heap.
CHAVE	Um bloqueio de linha dentro de um índice usado para proteger um intervalo de chaves em transações serializáveis.
PAGE	Uma página de 8 quilobytes (KB) em um banco de dados, como dados ou páginas de índice.
EXTENT	Um grupo contíguo de oito páginas, como dados ou páginas de índice.
HoBT 1	Um heap ou árvore-B. Um bloqueio que protege uma árvore B (índice) ou as páginas de dados de heap em uma tabela que não tem um índice clusterizado.
TABELA 1	A tabela inteira, inclusive todos os dados e índices.
FILE	Um arquivo do banco de dados.
APPLICATION	Um recurso de aplicativo especificado.
METADATA	Bloqueios de metadados.
ALLOCATION_UNIT	Uma unidade de alocação.
DATABASE	O banco de dados inteiro.
XACT 2	Bloqueio de ID de transação (TID) usado no Bloqueio otimizado. Consulte Bloqueio de ID de transação (TID).

¹ HoBT e bloqueios de TABLE podem ser afetados pela opção de LOCK_ESCALATION de ALTER TABLE.

² Recursos de bloqueio adicionais estão disponíveis para recursos de bloqueio XACT, confira Adições de diagnóstico para bloqueio otimizado.

Modos de bloqueio

O Mecanismo de Banco de Dados do SQL Server bloqueia os recursos usando diferentes modos de bloqueio que determinam como os recursos podem ser acessados por transações simultâneas.

A tabela a seguir mostra os modos de bloqueio de recursos que o Mecanismo de Banco de Dados do SQL Server utiliza.

Modo de bloqueio	Descrição
Compartilhado (S)	Usado para operações de leitura que não alteram ou atualizam dados, como uma instrução SELECT.
Atualização (U)	Usado em recursos que podem ser atualizados. Evita uma forma comum de deadlock que ocorre quando várias sessões estão lendo, bloqueando e potencialmente atualizando recursos mais tarde.
Exclusivo (X)	Usado para operações da modificação de dados, como INSERT, UPDATE ou DELETE. Assegura que várias atualizações não sejam realizadas no mesmo recurso ao mesmo tempo.
Intenção	Usado para estabelecer uma hierarquia de bloqueio. Os tipos de bloqueios intencionais são: intencional compartilhado (IS), intencional exclusivo (IX) e compartilhado com intenção exclusiva (SIX).
Esquema	Usado quando uma operação dependente do esquema de uma tabela está em execução. Os tipos de bloqueios de esquema são: modificação de esquema (Sch-M) e estabilidade de esquema (Sch-S).
BU (atualização em massa)	Usado quando para copiar dados em massa em uma tabela e a dica TABLOCK está especificada.
Intervalo de chave	Protege o intervalo de leitura de linhas lido por uma consulta ao usar o nível de isolamento da transação serializável. Assegura que outras transações não possam inserir linhas que se qualifiquem para consultas da transação serializável se as consultas forem executadas novamente.

Bloqueios compartilhados

Bloqueios compartilhados (S) permitem que transações simultâneas leiam um recurso (SELECT) sob controle de simultaneidade pessimista. Nenhuma outra transação pode modificar os dados enquanto bloqueios compartilhados (S) existirem no recurso. Bloqueios compartilhados (S) em um recurso são liberados quando a operação de leitura termina, exceto se o nível de isolamento da transação for configurado para leitura repetida ou maior ou uma dica de bloqueio for usada para reter os bloqueios compartilhados (S) pela duração da transação.

Bloqueios de atualização

O Mecanismo de Banco de Dados aplica bloqueios de atualização (U) enquanto se prepara para executar uma atualização. Os bloqueios U são compatíveis com os bloqueios S, mas somente uma transação pode manter um bloqueio U por vez em um determinado recurso. Isso é fundamental - muitas transações simultâneas podem manter bloqueios S, mas somente uma transação pode manter um bloqueio U em um recurso. Os bloqueios de atualização (U) são eventualmente atualizados para bloqueios exclusivos (X) para atualizar uma linha.

Bloqueios de atualização (U) também podem ser obtidos por consultas que não executam um UPDATE, quando a dica de tabela UPDLOCK é especificada na consulta. É comum que os aplicativos usem um padrão de "selecionar uma linha e, em seguida, atualizar a linha", em que a leitura e a gravação são explicitamente separadas na transação. Nesse caso, se o nível de isolamento for de leitura repetível ou serializável, as atualizações simultâneas poderão causar um deadlock. Em vez disso, os aplicativos poderiam seguir um padrão de "selecionar uma linha com a dica UPDLOCK e, em seguida, atualizar a linha".

• Em uma transação de leitura repetível ou serializável, a transação lê os dados, adquirindo um bloqueio compartilhado (S) no recurso e, em seguida, modifica os dados, o que exige a conversão do bloqueio em um bloqueio exclusivo (X). Se duas transações adquirirem bloqueios compartilhados (S) em um recurso e, em seguida, tentarem atualizar os dados simultaneamente, uma transação tentará a conversão do bloqueio em um bloqueio exclusivo (X). A conversão de bloqueio compartilhado exclusivo deve esperar, porque o bloqueio exclusivo de uma transação não é compatível com o bloqueio compartilhado (S) da outra transação; ocorre uma espera de bloqueio. A segunda transação tenta adquirir um bloqueio exclusivo (X) para sua atualização. Como ambas as transações estão se convertendo em bloqueios exclusivos (X) e cada uma delas está esperando que a outra transação libere seu bloqueio compartilhado (S), ocorre um deadlock.

• No nível de isolamento padrão de leitura comprometida, os bloqueios S têm curta duração e são liberados assim que são usados. É improvável que bloqueios de curta duração levem a deadlocks.

• Se a dica UPDLOCK for usada em uma gravação, a transação deverá ter acesso à última versão da linha. Se a última versão não estiver mais visível, espera-se que seja possível receber Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict quando o isolamento SNAPSHOT estiver em uso. Para obter um exemplo, confira Trabalhar com isolamento de instantâneos.

Bloqueios exclusivos

Bloqueios exclusivos (X) evitam o acesso a um recurso através de transações simultâneas. Com um bloqueio exclusivo (X), nenhuma outra transação pode modificar os dados; operações de leitura podem ser realizadas apenas com o uso da dica NOLOCK ou nível de isolamento de leitura não confirmada.

Instruções de modificação de dados como INSERT, UPDATE e DELETE combinam operações de modificação e de leitura. A instrução primeiro executa as operações de leitura para adquirir dados, antes de executar as operações de modificação necessárias. Assim, as instruções de modificação de dados normalmente solicitam bloqueios compartilhados e bloqueios exclusivos. Por exemplo, uma instrução UPDATE poderia modificar linhas em uma tabela com base em uma junção com outra tabela. Nesse caso, a instrução UPDATE solicita bloqueios compartilhados nas linhas lidas na junção de tabela, além de solicitar bloqueios exclusivos nas linhas atualizadas.

Bloqueios intencionais

O Mecanismo de Banco de Dados do SQL Server usa bloqueios intencionais para proteger a aplicação de um bloqueio compartilhado (S) ou exclusivo (X) em um recurso inferior na hierarquia de bloqueios. Os bloqueios intencionais recebem esse nome porque são adquiridos antes de um bloqueio no nível inferior e, portanto, sinalizam a intenção de aplicar bloqueios em um nível inferior.

Os bloqueios intencionais têm duas finalidades:

• Para evitar que outras transações modifiquem recursos de alto nível de uma forma que inviabilizaria o bloqueio em um nível inferior.
• Para melhorar a eficiência do Mecanismo de Banco de Dados do SQL Server na detecção de conflitos de bloqueio em um nível mais alto de granularidade.

Por exemplo, um bloqueio intencional compartilhado é solicitado no nível de tabela antes que os bloqueios compartilhados (S) sejam solicitados em páginas ou linhas dentro dessa tabela. Configurar um bloqueio intencional no nível de tabela evita que outra transação subsequentemente adquira um bloqueio exclusivo (X) em uma tabela contendo essa página. Os bloqueios intencionais melhoram o desempenho porque o Mecanismo de Banco de Dados do SQL Server os examina somente no nível da tabela para determinar se uma transação pode adquirir com segurança um bloqueio nessa tabela. Isso remove a necessidade de examinar cada bloqueio de linha ou página na tabela para determinar se a transação pode bloquear a tabela inteira.

Os bloqueios intencionais incluem IS (tentativa compartilhada), IX (tentativa exclusiva) e SIX (compartilhados com tentativa exclusiva).

Modo de bloqueio	Descrição
Tentativa compartilhada (IS)	Protege bloqueios solicitados ou bloqueios compartilhados adquiridos em alguns (mas não todos) recursos mais baixos na hierarquia.
IX (intensão exclusiva)	Protege os bloqueios solicitados ou bloqueios exclusivos adquiridos em alguns (mas não todos) recursos mais baixos na hierarquia. IX é um superconjunto de IS, e também protege solicitando bloqueios compartilhados em recursos de nível mais baixo.
SIX (compartilhado com intenção exclusiva)	Protege bloqueios compartilhados solicitados ou adquiridos em todos os recursos inferiores na hierarquia e bloqueios intencionais exclusivos em alguns (mas não todos) recursos de nível mais baixo. São permitido bloqueios IS simultâneos no recurso de nível mais alto. Por exemplo, adquirir um bloqueio SIX, em uma tabela, também adquire bloqueios intencionais exclusivos nas páginas que estão sendo modificadas e bloqueios exclusivos nas linhas modificadas. Só pode haver um bloqueio SIX por recurso de cada vez, evitando atualizações feitas no recurso por outras transações, embora outras transações possam ler recursos, em uma hierarquia mais baixa, obtendo bloqueios IS no nível de tabela.
IU (atualização intencional)	Protege os bloqueios de atualização solicitados ou adquiridos, em todos os recursos dos níveis mais baixos da hierarquia. Bloqueios de IU só são usados em recursos de página. Bloqueios IU são convertidos a bloqueios IX se houver uma operação de atualização.
SIU (atualização intencional compartilhada)	Uma combinação de bloqueios S e IU, como resultado de aquisição desses bloqueios, separadamente e simultaneamente, mantendo ambos os bloqueios. Por exemplo, uma transação executa uma consulta com a dica PAGLOCK e, então, executa uma operação de atualização. A consulta com a dica PAGLOCK adquire o bloqueio de S e a operação de atualização adquire o bloqueio IU.
UIX (atualização intencional exclusiva)	Uma combinação de bloqueios U e IX, como resultado de aquisição desses bloqueios, separadamente e simultaneamente, mantendo ambos os bloqueios.

Bloqueios de esquema

O Mecanismo de Banco de Dados do SQL Server usa bloqueios de modificação de esquema (Sch-M) durante uma operação de linguagem de definição de dados (DDL) de tabela, como adicionar uma coluna ou remover uma tabela. Durante o tempo em ele é mantido, o bloqueio Sch-M evita o acesso simultâneo à tabela. Isso significa que o bloqueio Sch-M bloqueia todos as operações externas até que o bloqueio seja liberado.

Algumas operações DML (Data Manipulation Language), tais como truncamento da tabela, usam o bloqueio Sch-M para impedir o acesso à tabelas afetadas por operações simultâneas.

O Mecanismo de Banco de Dados do SQL Server usa bloqueios de estabilidade de esquema (Sch-S) ao compilar e executar consultas. Os bloqueios de Sch-S não bloqueiam quaisquer bloqueios transacionais, inclusive bloqueios exclusivos (X). Assim sendo, outras transações, incluindo aquelas com bloqueios X em uma tabela, continuam executando enquanto a consulta é compilada. Porém, operações simultâneas DDL e operações simultâneas DML que adquirem bloqueios Sch-M, não podem ser executadas na tabela.

Bloqueios de atualização em massa

Os bloqueios de atualização em massa (BU) permitem a vários threads carregarem simultaneamente dados em massa para a mesma tabela, evitando que outros processos, que não são carregamentos de dados em massa acessem a tabela. O Mecanismo de Banco de Dados do SQL Server usa bloqueios de atualização em massa (BU) quando ambas as condições a seguir são verdadeiras.

• Você usa a instrução Transact-SQL BULK INSERT, ou a função OPENROWSET(BULK), ou um dos comandos de API de inserção em massa, como .NET SqlBulkCopy, OLEDB Fast Load APIs, ou as APIs de cópia em massa do ODBC para copiar dados em massa para uma tabela.
• A dica TABLOCK é especificada ou a opção de tabela bloqueio da tabela em carregamento em massa é configurada usando sp_tableoption.

Dica

Diferentemente da instrução BULK INSERT, que contém um bloqueio de atualização em massa (BU) menos restritivo, INSERT INTO...SELECT com a dica TABLOCK contém um bloqueio exclusivo da tentativa (IX) na tabela. Isso significa que você não pode inserir linhas usando operações de inserção paralelas.

Bloqueios de intervalo de chaves

Os bloqueios de intervalo de chaves protegem um intervalo de linhas implicitamente incluído em um conjunto de registros que está sendo lido por uma instrução Transact-SQL ao usar o nível de isolamento de transação serializável. O bloqueio de intervalo de chave impede leituras fantasmas. Ao proteger os intervalos de chaves entre as linhas, ele também evita inserções fantasmas ou exclusões em um conjunto de registros acessado por uma transação.

Compatibilidade de bloqueios

A compatibilidade de bloqueios controla se várias transações adquirem bloqueios, no mesmo recurso ao mesmo tempo. Se um recurso já estiver bloqueado por outra transação, um novo pedido de bloqueio apenas pode ser feito se o modo do bloqueio solicitado for compatível com o modo do bloqueio existente. Se o modo de pedido de bloqueio não for compatível com o bloqueio existente, a transação que solicita um novo bloqueio esperará a liberação do bloqueio existente ou que o intervalo de tempo limite de bloqueio se esgote. Por exemplo, nenhum modo de bloqueio é compatível com bloqueios exclusivos. Enquanto um bloqueio exclusivo (X) for mantido, nenhuma outra transação pode adquirir um bloqueio de nenhum tipo (compartilhado, atualizado ou exclusivo) naquele recurso até que o bloqueio exclusivo (X) seja liberado. Opcionalmente, se um bloqueio compartilhado (S) tiver sido aplicado a um recurso, outras transações também poderão adquirir um bloqueio compartilhado (U) nesse item, mesmo que a primeira transação não esteja concluída. No entanto, outras transações não podem adquirir um bloqueio exclusivo até que o bloqueio compartilhado tenha sido liberado.

A tabela a seguir mostra a compatibilidade dos modos de bloqueio mais comumente encontrados.

Modo concedido existente	IS	S	U	IX	SIX	X
Modo solicitado
Tentativa compartilhada (IS)	Sim	Sim	Sim	Sim	Sim	Não
Compartilhado (S)	Sim	Sim	Sim	Não	No	Não
Atualização (U)	Sim	Sim	Não	No	No	Não
IX (intensão exclusiva)	Sim	Não	No	Sim	Não	Não
SIX (compartilhado com intenção exclusiva)	Sim	Não	No	No	No	Não
Exclusivo (X)	Não	No	No	No	No	Número

Observação

Um bloqueio intencional exclusivo (IX) é compatível com um modo de bloqueio IX porque IX significa que a intenção é atualizar somente algumas linhas em vez de todas. Outras transações que tentarem ler ou atualizar algumas das linhas também são permitidas, exceto se não forem as mesmas linhas que estejam sendo atualizadas por outras transações. Além disso, se duas transações tentarem atualizar a mesma linha, ambas receberão um bloqueio IX no nível de tabela e página. No entanto, uma transação receberá um bloqueio X no nível de linha. A outra transação deve aguardar até que o bloqueio de nível de linha seja removido.

Use a tabela a seguir para determinar a compatibilidade de todos os modos de bloqueio disponíveis no SQL Server.

Bloqueio de intervalo de chave

Os bloqueios de intervalo de chaves protegem um intervalo de linhas implicitamente incluído em um conjunto de registros que está sendo lido por uma instrução Transact-SQL ao usar o nível de isolamento de transação serializável. O nível de isolamento serializável requer que qualquer consulta executada durante uma transação deva obter o mesmo conjunto de linhas, toda vez que seja executada durante a transação. Um bloqueio de intervalo de chave protege esse requisito, impedindo que outras transações insiram novas linhas cujas chaves falhariam no intervalo de chaves lido pela transação serializável.

O bloqueio de intervalo de chave impede leituras fantasmas. Ao proteger os intervalos de chaves entre as linhas, ele também evita inserções fantasmas em um conjunto de registros acessado por uma transação.

Um bloqueio de intervalo de chave é colocado em um índice, especificando um valor de chave inicial e final. Esse bloqueio impede quaisquer tentativas de inserção, atualização ou exclusão de qualquer linha de um valor de chave que falhe no intervalo, pois essas operações primeiro teriam que obter um bloqueio no índice. Por exemplo, uma transação serializável pode emitir uma instrução SELECT que leia todas as linhas cujos valores de chave correspondam à condição BETWEEN 'AAA' AND 'CZZ'. Um bloqueio no intervalo de chave sobre o valor da chave de 'AAA' até 'CZZ' evita que outras transações insiram linhas com valores de chave em qualquer posição daquele intervalo, tais como 'ADG', 'BBD', ou 'CAL'.

Modos de bloqueio do intervalo de chaves

O bloqueio de intervalo de chave inclui um intervalo e um componente de linha especificados no formato intervalo-linha:

• O intervalo representa o modo de bloqueio que protege o intervalo entre duas entradas consecutivas de índice.
• A fila representa o modo de bloqueio que protege a entrada de índice.
• O modo representa o modo de bloqueio combinado em uso. Os modos de bloqueio de intervalo de chave consistem de duas partes. A primeira representa o tipo de bloqueio utilizado para bloquear o intervalo de índice (RangeT), e a segunda representa o tipo de bloqueio utilizado para bloquear uma chave específica (K). As duas partes são conectadas por um hífen (-), como IntervaloT-K.

Intervalo	Linha	Mode	Descrição
RangeS	S	RangeS-S	Intervalo compartilhado, bloqueio de recurso compartilhado; exame de intervalo serializável.
RangeS	U	RangeS-U	Intervalo compartilhado, bloqueio de recurso compartilhado; exame de atualização serializável.
RangeI	Nulo	RangeI-N	Insere o intervalo, anula o bloqueio de recurso; usado para testar intervalos antes de inserir uma nova chave em um índice.
RangeX	X	RangeX-X	Intervalo exclusivo, bloqueio de recurso exclusivo; usado ao atualizar uma chave em um intervalo.

Observação

O modo de bloqueio interno Nulo é compatível com todos os outros modos de bloqueio.

Os modos de bloqueio de intervalo de chave têm uma matriz de compatibilidade que mostra quais bloqueios são compatíveis com outros bloqueios obtidos em chaves e intervalos sobrepostos.

Modo concedido existente	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Modo solicitado
Compartilhado (S)	Sim	Sim	Não	Sim	Sim	Sim	Não
Atualização (U)	Sim	Não	No	Sim	Não	Sim	Não
Exclusivo (X)	Não	No	No	No	No	Sim	Não
RangeS-S	Sim	Sim	Não	Sim	Sim	Não	Não
RangeS-U	Sim	Não	No	Sim	Não	No	Não
RangeI-N	Sim	Sim	Sim	Não	No	Sim	Não
RangeX-X	Não	No	No	No	No	No	Não

Bloqueios de conversão

Os bloqueios de conversão são criados quando um bloqueio de intervalo de chave se sobrepuser a outro bloqueio.

Bloqueio 1	Bloqueio 2	Bloqueio de Conversão
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

Os bloqueios de conversão podem ser observados por um curto período de tempo sob diferentes circunstâncias complexas, às vezes enquanto executando processos simultâneos.

Varredura de intervalo serializável, busca de singleton, exclusão e inserção

O bloqueio de intervalo de chave garante que as seguintes operações sejam serializáveis:

• Consulta de varredura de intervalo
• Busca de singleton em linha inexistente
• Operação de exclusão
• Operação de Inserção

Antes que o bloqueio de intervalo de chave possa ocorrer, as seguintes condições devem ser satisfeitas:

• O nível de isolamento da transação deve ser definido como SERIALIZABLE.
• O processador de consulta deve usar um índice para implementar o predicado de filtro do intervalo. Por exemplo, a cláusula WHERE em uma instrução SELECT poderia estabelecer uma condição de intervalo com este predicado: ColumnX BETWEEN N**'AAA'** AND N**'CZZ'**. Um bloqueio de intervalo de chave só poderá ser adquirido se a ColumnX estiver coberta por uma chave de índice.

Exemplos

A seguinte tabela e índice são usados como base para os exemplos de intervalo de chave que seguem.

Consulta de varredura de intervalo

Para garantir que uma consulta de varredura de intervalo seja serializável, a mesma consulta deve retornar os mesmos resultados a cada vez que seja executada dentro de uma mesma transação. Novas linhas não devem ser inseridas dentro da consulta de varredura de intervalo por outras transações; caso contrário, elas se tornam inserções fantasmas. Por exemplo, a consulta seguinte usa a tabela e índice da ilustração anterior:

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

Os bloqueios de intervalo de chaves são colocados nas entradas do índice correspondentes ao intervalo das linhas de dados, em que o nome está entre os valores Adam e Dale, evitando a adição ou a remoção de novas linhas que se qualificaram na consulta anterior. Embora o nome desse intervalo seja Adam, o bloqueio de intervalo de chaves do modo RangeS-S dessa entrada de índice garante que nenhum nome novo que começa com a letra A possa ser adicionado antes de Adam, como Abigail. De modo semelhante, o bloqueio de intervalo de chaves RangeS-S na entrada do índice Dale garante que nenhum nome novo que começa com a letra C possa ser adicionado após Carlos, como Clive.

Observação

O número de bloqueios que RangeS-S contém é n+1, em que n é o número de linhas que satisfazem a consulta.

Busca singleton de dados inexistentes

Se uma consulta dentro de uma transação tenta selecionar uma linha que não existe, a emissão da consulta em um momento posterior dentro da mesma transação terá de retornar o mesmo resultado. Nenhuma outra transação terá permissão de inserir essa linha inexistente. Por exemplo, nessa consulta:

SELECT name
FROM mytable
WHERE name = 'Bill';

Um bloqueio de intervalo de chave é posicionado na entrada de índice correspondendo ao intervalo de nome de Ben a Bing pois o nome Bill seria inserido entre essas duas entradas adjacentes. O bloqueio de intervalo de chave do modo RangeS-S é posicionado na entrada de índice Bing. Isso impede que qualquer outra transação insira valores, como Bill, entre as entradas de índice Ben e Bing.

Operação de exclusão, sem bloqueio otimizado

Ao excluir um valor dentro de uma transação, o intervalo no qual o valor se encaixa não tem que ser bloqueado durante a transação que está executando a operação de exclusão. Bloquear o valor de chave excluso até o fim da transação é suficiente para manter a serialização. Por exemplo, na seguinte instrução DELETE:

DELETE mytable
WHERE name = 'Bob';

Um bloqueio (X) exclusivo é posicionado na entrada de índice correspondente ao nome Bob. Outras transações podem inserir ou excluir valores entre ou após o valor excluído Bob. Entretanto, qualquer transação que tente ler, inserir, ou excluir o valor Bob será bloqueada até que a transação de exclusão seja confirmada ou revertida. (A opção de banco de dados READ_COMMITTED_SNAPSHOT e o nível de isolamento SNAPSHOT também permitem leituras de uma versão de linha do estado previamente confirmado.)

A exclusão de intervalo pode ser executada utilizando três modos básicos de bloqueio: de linha, de página, ou de tabela. A estratégia de bloqueio de linha, de página ou de tabela é decidida pelo otimizador de consulta ou pode ser especificada pelo usuário por meio das dicas do otimizador de consulta como ROWLOCK, PAGLOCK ou TABLOCK. Quando PAGLOCK ou TABLOCK é usado, o Mecanismo de Banco de Dados do SQL Server desalocará imediatamente uma página de índice se todas as linhas forem excluídas dessa página. Por outro lado, quando ROWLOCK é usada, todas as linhas exclusas são marcadas apenas como exclusas; elas são depois removidas da página de índice usando-se uma tarefa em segundo plano.

Operação de exclusão, com bloqueio otimizado

Ao excluir um valor em uma transação, os bloqueios de linha e de página são adquiridos e liberados de forma incremental e não são mantidos durante toda a transação. Por exemplo, na seguinte instrução DELETE:

DELETE mytable
WHERE name = 'Bob';

Um bloqueio de TID é aplicado em todas as linhas modificadas pela duração da transação. Um bloqueio é adquirido no TID das entradas de índice correspondentes ao nome Bob. Com o bloqueio otimizado, os bloqueios de página e de linha continuam a ser adquiridos para atualizações, mas cada bloqueio de página e de linha é liberado assim que cada linha é atualizada. O bloqueio de TID impede que as linhas sejam atualizadas até que a transação seja concluída. Qualquer transação que tentar ler, inserir, ou excluir o valor Bob será bloqueada até que a transação de exclusão seja confirmada ou revertida. (A opção de banco de dados READ_COMMITTED_SNAPSHOT e o nível de isolamento SNAPSHOT também permitem leituras de uma versão de linha do estado previamente confirmado.)

Caso contrário, a mecânica de bloqueio de uma operação de exclusão é a mesma que aquela sem bloqueio otimizado.

Operação de inserção sem bloqueio otimizado

Ao inserir um valor em uma transação, o intervalo no qual o valor se encaixa não tem que ser bloqueado durante a transação que está executando a operação de inserção. Bloquear o valor da chave inserida até o término da transação é suficiente para manter a serialização. Por exemplo, na seguinte instrução INSERT:

INSERT mytable VALUES ('Dan');

O bloqueio de intervalo de chaves do modo RangeI-N é aplicado na entrada do índice correspondente ao nome David para testar o intervalo. Se o bloqueio é concedido, Dan é inserido, e um bloqueio (X) exclusivo é posicionado no valor Dan. O bloqueio de intervalo de chaves no modo RangeI-N só é necessário para testar o intervalo e não é mantido durante a transação que executa a operação de inserção. Outras transações podem inserir ou excluir valores antes ou após o valor inserido Dan. Entretanto, qualquer transação que tente ler, inserir, ou excluir o valor Dan será bloqueada até que a transação de inserção seja confirmada ou revertida.

Operação de inserção com bloqueio otimizado

Ao inserir um valor em uma transação, o intervalo no qual o valor se encaixa não tem que ser bloqueado durante a transação que está executando a operação de inserção. Bloqueios de linha e de página raramente são adquiridos, somente quando há uma reconstrução de índice online em andamento ou quando há transações serializáveis na instância. Se bloqueios de linha e de página forem adquiridos, eles serão liberados rapidamente e não serão mantidos durante a transação. A aplicação de um bloqueio de TID exclusivo no valor da chave inserida até o final da transação é suficiente para manter a serialização. Por exemplo, na seguinte instrução INSERT:

INSERT mytable VALUES ('Dan');

Com o bloqueio otimizado, um bloqueio RangeI-N apenas será adquirido se houver pelo menos uma transação que esteja usando o nível de isolamento SERIALIZABLE na instância. O bloqueio de intervalo de chaves do modo RangeI-N é aplicado na entrada do índice correspondente ao nome David para testar o intervalo. Se o bloqueio é concedido, Dan é inserido, e um bloqueio (X) exclusivo é posicionado no valor Dan. O bloqueio de intervalo de chaves no modo RangeI-N só é necessário para testar o intervalo e não é mantido durante a transação que executa a operação de inserção. Outras transações podem inserir ou excluir valores antes ou após o valor inserido Dan. Entretanto, qualquer transação que tente ler, inserir, ou excluir o valor Dan será bloqueada até que a transação de inserção seja confirmada ou revertida.

Escalonamento de bloqueios

Escalonamento de bloqueios é o processo de conversão de muitos bloqueios refinados em menos bloqueios de alta granularidade, reduzindo a sobrecarga do sistema e aumentando a probabilidade de contenção de simultaneidade.

O escalonamento de bloqueios se comporta de forma diferente, dependendo de o bloqueio otimizado estar habilitado ou não.

Escalonamento de bloqueios sem bloqueio otimizado

À medida que o Mecanismo de Banco de Dados do SQL Server adquire bloqueios de baixo nível, ele também aplica bloqueios intencionais nos objetos que contêm os objetos de nível inferior:

• Ao bloquear linhas ou intervalos de chaves de índice, o Mecanismo de Banco de Dados aplica um bloqueio intencional nas páginas que contêm as linhas ou chaves.
• Ao bloquear páginas, o Mecanismo de Banco de Dados aplica um bloqueio intencional nos objetos de nível superior que contêm as páginas. Além do bloqueio intencional no objeto, os bloqueios intencionais de página são solicitados nos seguintes objetos:
  • Páginas em nível de folha de índices não clusterizados
  • Páginas de dados de índices clusterizados
  • Páginas de dados de heap

O Mecanismo de Banco de Dados pode fazer o bloqueio de linha e de página para a mesma instrução a fim de minimizar o número de bloqueios e reduzir a probabilidade de que o escalonamento de bloqueios seja necessário. Por exemplo, o Mecanismo de Banco de Dados pode colocar bloqueios de página em um índice não clusterizado (se um número suficiente de chaves contíguas no nó do índice for selecionado para atender à consulta) e bloqueios de linha nos dados.

Para escalonar bloqueios, o Mecanismo de Banco de Dados tenta alterar o bloqueio intencional na tabela para o bloqueio completo correspondente, por exemplo, alterando um bloqueio intencional exclusivo (IX) para um bloqueio exclusivo (X) ou um bloqueio intencional compartilhado (IS) para um bloqueio compartilhado (S). Se a tentativa de escalonamento de bloqueios for bem-sucedida e o bloqueio total da tabela for adquirido, todos os bloqueios de heap ou árvore B, página (PAGE) ou nível de linha (RID) mantidos pela transação no heap ou no índice serão liberados. Se o bloqueio completo não for adquirido, não acontecerá nenhum escalonamento de bloqueios nesse momento e o Mecanismo de Banco de Dados continuará adquirindo bloqueios de linha, de chave ou de página.

O Mecanismo de Banco de Dados não escalona bloqueios de linha ou de intervalo de chaves para bloqueios de página, mas os escalona diretamente para bloqueios de tabela. Da mesma forma, bloqueios de página são sempre escalonados para bloqueios de tabela. O bloqueio de tabelas particionadas pode ser escalonado para o nível HoBT para a partição associada em vez do bloqueio de tabela. Um bloqueio em nível de HoBT não necessariamente bloqueia os HoBTs alinhados para a partição.

Observação

Bloqueios em nível de HoBT geralmente aumentam a simultaneidade, mas apresentam a possibilidade de deadlocks quando as transações que estão bloqueando partições diferentes querem expandir seus bloqueios exclusivos para as outras partições. Em casos raros, a granularidade de bloqueios TABLE pode ter um desempenho melhor.

Se uma tentativa de escalonamento de bloqueios falhar devido a bloqueios conflitantes mantidos por transações simultâneas, o Mecanismo de Banco de Dados tentará novamente o escalonamento de bloqueios para cada 1.250 bloqueios adicionais adquiridos pela transação.

Cada evento de escalonamento opera principalmente no nível de uma única instrução Transact-SQL. Quando o evento é iniciado, o Mecanismo de Banco de Dados tenta escalonar todos os bloqueios pertencentes à transação atual em qualquer uma das tabelas referenciadas pela instrução ativa, desde que atenda aos requisitos de limite de escalonamento. Se o evento de escalonamento começar antes que a instrução tenha acessado uma tabela, não será feita nenhuma tentativa de escalonar os bloqueios nessa tabela. Se o escalonamento de bloqueios for bem-sucedido, todos os bloqueios adquiridos pela transação em uma instrução anterior e ainda mantidos no momento em que o evento for iniciado serão escalonados se a tabela for referenciada pela instrução atual e estiver incluída no evento de escalonamento.

Por exemplo, suponha que uma sessão realize estas operações:

• Começa uma transação.
• Atualiza TableA. Isso gera bloqueios de linha exclusivos em TableA que são mantidos até a conclusão da transação.
• Atualiza TableB. Isso gera bloqueios de linha exclusivos em TableB que são mantidos até a conclusão da transação.
• Executa uma instrução SELECT que une TableA com TableC. O plano de execução de consulta chama as linhas a serem recuperadas da TableA antes que as linhas sejam recuperadas da TableC.
• A instrução SELECT dispara o escalonamento de bloqueios enquanto recupera as linhas da TableA e antes de acessar a TableC.

Se o escalonamento de bloqueios tiver êxito, somente os bloqueios mantidos pela sessão na TableA serão escalonados. Isso inclui bloqueios compartilhados da instrução SELECT e bloqueios exclusivos da instrução UPDATE anterior. Embora somente os bloqueios que a sessão adquiriu na TableA para a instrução SELECT sejam contados para determinar se o escalonamento deve ser realizado, depois que o escalonamento tiver êxito, todos os bloqueios mantidos pela sessão na TableA serão escalonados para um bloqueio exclusivo na tabela e todos os outros bloqueios de granularidade inferior na TableA, incluindo os bloqueios intencionais, serão liberados.

Não foram realizadas tentativas para escalonar os bloqueios na TableB porque não havia referência ativa à TableB na instrução SELECT. Do mesmo modo, não foram realizadas tentativas para escalonar os bloqueios na TableC, que não foi escalonada porque ela ainda não havia sido acessada quando o escalonamento ocorreu.

Escalonamento de bloqueios com bloqueio otimizado

O bloqueio otimizado ajuda a reduzir a memória de bloqueio, pois poucos bloqueios são mantidos durante a transação. À medida que o Mecanismo de Banco de Dados do SQL Server adquire bloqueios de linha e de página, o escalonamento de bloqueios pode ocorrer de forma semelhante, mas com muito menos frequência. Normalmente, o bloqueio otimizado consegue evitar o escalonamento de bloqueios, reduzindo o número de bloqueios e a quantidade de memória de bloqueio necessária.

Quando o bloqueio otimizado está ativado e no nível de isolamento padrão READ COMMITTED, o Mecanismo de Banco de Dados libera os bloqueios de linha e de página assim que a gravação é concluída. Nenhum bloqueio de linha e página é mantido durante a transação, exceto por um único bloqueio de ID de transação (TID). Isso reduz a probabilidade de escalonamento de bloqueios.

Limites de escalonamento de bloqueios

O escalonamento de bloqueios é disparado quando não está desabilitado na tabela durante o uso da opção ALTER TABLE SET LOCK_ESCALATION e quando uma das seguintes condições existem:

• Uma única instrução Transact-SQL adquire pelo menos 5.000 bloqueios em uma única tabela ou índice não particionado.
• Uma única instrução Transact-SQL adquire pelo menos 5.000 bloqueios em uma única partição de uma tabela particionada, e a opção ALTER TABLE SET LOCK_ESCALATION está definida como AUTO.
• O número de bloqueios em uma instância do Mecanismo de Banco de Dados excede os limites de memória ou de configuração.

Se bloqueios não puderem ser escalonados devido a conflitos de bloqueios, o Mecanismo de Banco de Dados acionará periodicamente o escalonamento de bloqueios a cada 1.250 novos bloqueios adquiridos.

Limite de escalonamento para uma instrução Transact-SQL

Quando o Mecanismo de Banco de Dados verifica possíveis escalonamentos a cada 1.250 bloqueios recém-adquiridos, um escalonamento de bloqueios ocorrerá se, e somente se, uma instrução Transact-SQL tiver adquirido pelo menos 5.000 bloqueios em uma única referência de uma tabela. O escalonamento de bloqueios é acionado quando uma instrução Transact-SQL adquire pelo menos 5.000 bloqueios em uma única referência de uma tabela. Por exemplo, o escalonamento de bloqueios não será acionado se uma instrução adquirir três mil bloqueios em um índice e três mil bloqueios em outro índice da mesma tabela. Da mesma forma, o escalonamento de bloqueios não será acionado se uma instrução tiver uma autojunção em uma tabela, e cada referência à tabela adquirir apenas três mil bloqueios na tabela.

O escalonamento de bloqueios ocorrerá somente para tabelas que forem acessadas no momento em que o escalonamento é acionado. Suponha que uma só instrução SELECT seja uma junção que acessa três tabelas nesta sequência: TableA, TableB e TableC. A instrução adquire três mil bloqueios de linha em um índice clusterizado para a TableA e pelo menos cinco mil bloqueios de linha no índice clusterizado para a TableB, mas ainda não acessou a TableC. Quando o Mecanismo de Banco de Dados detectar que a instrução adquiriu pelo menos 5.000 bloqueios de linha em TableB, ele tentará escalar todos os bloqueios mantidos pela transação atual em TableB. Ele também tenta escalonar todos os bloqueios mantidos pela transação atual na TableA, mas como o número de bloqueios na TableA é inferior a 5 mil, o escalonamento não terá êxito. Não houve nenhuma tentativa de escalonamento de bloqueios para a TableC porque ela ainda não havia sido acessada quando o escalonamento ocorreu.

Limite de escalonamento para uma instância do Mecanismo de Banco de Dados

Sempre que o número de bloqueios for maior que o limite de memória para o escalonamento de bloqueios, o Mecanismo de Banco de Dados acionará o escalonamento de bloqueios. O limite de memória depende da configuração da opção de configuração de bloqueios:

• Se a opção bloqueios estiver definida como a configuração padrão 0, o limite de escalonamento de bloqueios será atingido quando a memória usada pelos objetos de bloqueio for 24% da memória usada pelo Mecanismo de Banco de Dados, excluindo a memória AWE. A estrutura de dados usada para representar um bloqueio tem aproximadamente 100 bytes de comprimento. Esse limite é dinâmico porque o Mecanismo de Banco de Dados adquire e libera a memória dinamicamente para se ajustar às cargas de trabalho variáveis.

• Se a opção bloqueios for um valor diferente de 0, o limite de escalonamento de bloqueios será 40% (ou menos, se houver uma demanda de memória) do valor da opção bloqueios.

O Mecanismo de Banco de Dados pode escolher qualquer instrução ativa de qualquer sessão para escalonamento e, para cada 1.250 novos bloqueios, escolherá instruções para escalonamento, desde que a memória de bloqueio usada na instância permaneça acima do limite.

Escalonar tipos de bloqueio mistos

Quando ocorre o escalonamento de bloqueios, o bloqueio selecionado para o heap ou índice é suficientemente forte para atender aos requisitos do bloqueio de nível inferior mais restritivo.

Por exemplo, suponha uma sessão:

• Começa uma transação.
• Atualiza uma tabela que contém um índice clusterizado.
• Emite um comando SELECT que faz referência à mesma tabela.

A instrução UPDATE adquire estes bloqueios:

• Bloqueios exclusivos (X) nas linhas de dados atualizadas.
• Bloqueios exclusivos intencionais (IX) nas páginas de índice agrupadas que contêm essas linhas.
• Um bloqueio IX no índice clusterizado e outro na tabela.

A instrução SELECT adquire estes bloqueios:

• Bloqueios compartilhado (S) em todas as linhas de dados lidas, a menos que a linha já esteja protegida por um bloqueio X da instrução UPDATE.
• Bloqueios de tentativa compartilhada em todas as páginas de índice agrupadas que contêm essas linhas, a menos que a página já esteja protegida por um bloqueio IX.
• Nenhum bloqueio na tabela ou no índice clusterizado porque eles já estão protegidos por bloqueios IX.

Se a instrução SELECT adquirir bloqueios suficientes para acionar o escalonamento de bloqueios e o escalonamento for bem-sucedido, o bloqueio IX na tabela será convertido em um bloqueio X, e todos os bloqueios de linha, página e índice serão liberados. Tanto as atualizações quanto as leituras são protegidas pelo bloqueio X na tabela.

Reduzir o bloqueio e o escalonamento

Na maioria dos casos, o Mecanismo de Banco de Dados oferece o melhor desempenho quando opera com suas configurações padrão de bloqueio e escalonamento de bloqueios.

• Aproveite as vantagens do bloqueio otimizado.

  • O bloqueio otimizado oferece um mecanismo aprimorado de bloqueio de transações que reduz o consumo de memória de bloqueio e o bloqueio de transações simultâneas. É muito menos provável que ocorra um escalonamento de bloqueios quando o bloqueio otimizado está habilitado.
  • Evite usar dicas de tabela com bloqueio otimizado. Dicas de tabelas podem reduzir a eficácia do bloqueio otimizado.
  • Habilite READ_COMMITTED_SNAPSHOT no banco de dados para obter o máximo de benefícios do bloqueio otimizado. Esse é o nível de isolamento padrão no Banco de Dados SQL do Azure.
  • O bloqueio otimizado exige que a recuperação acelerada de banco de dados (ADR) esteja ativada no banco de dados.

Se uma instância do Mecanismo de banco de dados gerar muitos bloqueios e estiver observando frequentes escalonamentos de bloqueios, considere reduzir a quantidade de bloqueios com as seguintes estratégias:

• Use um nível de isolamento que não gere bloqueios compartilhados para operações de leitura:

  • Nível de isolamento READ COMMITTED quando a opção de banco de dados READ_COMMITTED_SNAPSHOT é ON.

  • Nível de isolamento SNAPSHOT.

  • Nível de isolamento READ UNCOMMITTED. Isso pode ser usado apenas para sistemas que podem operar com leituras sujas.

    Observação

    A alteração do nível de isolamento afeta todas as tabelas da instância do Mecanismo de Banco de Dados.

• Use as dicas de tabela PAGLOCK ou TABLOCK para que o Mecanismo de Banco de Dados use bloqueios de página, heap ou índice em vez de bloqueios de baixo nível. O uso dessa opção, no entanto, aumenta os problemas de usuários que bloqueiam outros usuários que tentam acessar os mesmos dados, e ela não deve ser usada em sistemas com mais de alguns usuários simultâneos.

• Quando o bloqueio otimizado não estiver habilitado, para tabelas particionadas, use a opção LOCK_ESCALATION de ALTER TABLE para escalonar os bloqueios para o nível HoBT em vez de para a tabela ou para desabilitar o escalonamento de bloqueios.

• Divida operações em lotes grandes em várias operações menores. Por exemplo, suponha que você tenha executado a seguinte consulta para remover centenas de milhares de registros antigos de uma tabela de auditoria e tenha descoberto que isso causou um escalonamento de bloqueios que bloqueou outros usuários:

  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  

  Ao remover esses registros em lotes de centenas de registros de cada vez, você pode reduzir significativamente o número de bloqueios acumulados por transação e evitar o escalonamento de bloqueios. Por exemplo:

  SET ROWCOUNT 500
  delete_more:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  IF @@ROWCOUNT > 0 GOTO delete_more
  SET ROWCOUNT 0
  

• Reduza o volume de bloqueio de uma consulta tornando a consulta a mais eficiente possível. Verificações grandes ou um número alto de pesquisas de indicadores podem aumentar a chance de escalonamento de bloqueios; além disso, isso aumenta a chance de deadlocks e, no geral, prejudica a simultaneidade e o desempenho. Depois de encontrar a consulta que causa o escalonamento de bloqueios, busque oportunidades para criar índices ou para adicionar colunas a um índice existente a fim de remover verificações de índice ou de tabela e maximizar a eficiência das buscas de índice. Considere a possibilidade de usar o Orientador de Otimização do Mecanismo de Banco de Dados para executar uma análise automática de índice na consulta. Para obter mais informações, consulte Tutorial: Orientador de Otimização do Mecanismo de Banco de Dados. Uma das metas dessa otimização é fazer com que as buscas de índice retornem o mínimo de linhas possível para minimizar o custo das pesquisas de indicador (maximize a seletividade do índice para a consulta específica). Se o Mecanismo de Banco de Dados estimar que um operador lógico de Pesquisa de Indicador poderá retornar muitas linhas, ele poderá usar um PREFETCH para realizar a pesquisa de indicador. Se o Mecanismo de Banco de Dados usar PREFETCH para uma pesquisa de indicador, ele deverá aumentar o nível de isolamento da transação de uma parte da consulta para leitura repetível para uma parte da consulta. Isso significa que aquilo que possa parecer semelhante a uma instrução SELECT em um nível de isolamento de leitura confirmada pode adquirir muitos milhares de bloqueios de chave (no índice clusterizado e em um índice não clusterizado), o que pode fazer com que essa consulta exceda os limites de escalonamento de bloqueios. Isso será especialmente importante se você descobrir que o bloqueio escalonado é um bloqueio de tabela compartilhada, que, no entanto, não é normalmente visto no nível de isolamento de leitura confirmada padrão.

  Se uma pesquisa de indicador com a cláusula WITH PREFETCH estiver causando o escalonamento, considere a possibilidade de adicionar mais colunas ao índice não clusterizado que é exibido no operador lógico da Busca de Índice ou da Verificação de Índice abaixo do operador lógico da Pesquisa de Indicador no plano de consulta. Talvez seja possível criar um índice de cobertura (um índice que inclua todas as colunas de uma tabela que foram usadas na consulta) ou, pelo menos, um índice que abranja as colunas usadas para critérios de junção ou na cláusula WHERE se a inclusão de tudo na lista de seleção de coluna for impraticável. Uma junção de Loops Aninhados também pode usar PREFETCH, e isso causa o mesmo comportamento de bloqueio.

• O escalonamento de bloqueios não poderá ocorrer se, atualmente, outro SPID estiver mantendo um bloqueio de tabela incompatível. O escalonamento de bloqueios sempre é escalonado para um bloqueio de tabela e nunca para bloqueios de página. Além disso, se uma tentativa de escalonamento de bloqueios falhar porque outro SPID mantém um bloqueio TAB incompatível, a consulta que tentou escalonamento não será bloqueada enquanto aguarda um bloqueio TAB. Em vez disso, ela continuará adquirindo bloqueios no nível original e mais granular (linha, chave ou página), fazendo periodicamente tentativas de escalonamento adicionais. Portanto, um método usado para evitar o escalonamento de bloqueios em uma tabela específica é adquirir e manter um bloqueio em uma conexão diferente que não seja compatível com o tipo de bloqueio escalonado. Um bloqueio IX (exclusivo de tentativa) no nível da tabela não bloqueia linhas nem páginas, mas ainda não é compatível com um bloqueio TAB S (compartilhado) ou X (exclusivo) escalonado. Por exemplo, suponha que você precise executar um trabalho em lotes que modifique um grande número de linhas na tabela mytable e que tenha causado o bloqueio que ocorre devido ao escalonamento de bloqueios. Se esse trabalho sempre for concluído em menos de uma hora, você poderá criar um trabalho Transact-SQL que contenha o seguinte código e programar o novo trabalho para iniciar vários minutos antes do horário de início do trabalho em lote:

  BEGIN TRAN
  SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1=0
  WAITFOR DELAY '1:00:00'
  COMMIT TRAN
  

  Essa consulta adquire e mantém um bloqueio IX em mytable por uma hora, o que impede o escalonamento de bloqueios na tabela durante esse período. Esse lote não modifica nenhum dado nem bloqueia outras consultas (a menos que a outra consulta force um bloqueio de tabela com a dica TABLOCK ou se um administrador desabilita os bloqueios de página ou de linha usando um procedimento armazenado sp_indexoption).

• Você também pode usar os sinalizadores de rastreamento 1211 e 1224 para desabilitar todos ou alguns escalonamentos de bloqueio. No entanto, esses sinalizadores de rastreamento desabilitam todo o escalonamento de bloqueio globalmente para todo o Mecanismo de Banco de Dados. O escalonamento de bloqueios tem uma finalidade muito útil no Mecanismo de Banco de Dados, pois maximiza a eficiência das consultas que, de outra forma, ficariam mais lentas devido à sobrecarga de aquisição e liberação de vários milhares de bloqueios. O escalonamento de bloqueios também ajuda a minimizar a memória necessária para controlar os bloqueios. A memória que o Mecanismo de Banco de Dados pode alocar dinamicamente para estruturas de bloqueio é finita. Portanto, se você desabilitar o escalonamento de bloqueios e a memória de bloqueios crescer o suficiente, as tentativas de alocar bloqueios adicionais para qualquer consulta poderão falhar e ocorrerá o seguinte erro: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  Observação

  Quando o erro MSSQLSERVER_1204 ocorre, ele interrompe o processamento da instrução atual e causa uma reversão da transação ativa. A reversão em si pode bloquear os usuários ou resultar em um longo tempo de recuperação do banco de dados, caso você reinicie o serviço de banco de dados.

  Observação

  O uso de uma dica de bloqueio como ROWLOCK só altera o plano de bloqueio inicial. As dicas de bloqueio não impedem o escalonamento de bloqueios.

Monitorar o escalonamento de bloqueios

Monitore o escalonamento de bloqueios usando o Evento estendido lock_escalation (xEvent), como no exemplo a seguir:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation(SET collect_database_name=(1),collect_statement=(1)
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.    username))
ADD TARGET package0.histogram(SET source=N'sqlserver.database_id')
GO

Importante

O Evento estendido lock_escalation (xEvent) deve ser usado em vez da classe de evento Lock:Escalation no Rastreamento do SQL ou no SQL Profiler.

Bloqueio dinâmico

Usar bloqueios de nível baixo, como bloqueios de linha, aumenta a simultaneidade diminuindo a probabilidade de duas transações solicitarem bloqueios, da mesma parte dos dados, ao mesmo tempo. Bloqueios de nível baixo também aumentam o número de bloqueios e os recursos necessários para administrá-los. Usar tabela de nível alto ou bloqueios de página diminui a sobrecarga, porém causando diminuição da simultaneidade.

O Mecanismo de Banco de Dados do SQL Server usa uma estratégia de bloqueio dinâmico para determinar os bloqueios mais econômicos. O Mecanismo de Banco de Dados do SQL Server determina automaticamente quais bloqueios são mais apropriados quando a consulta é executada, com base nas características do esquema e da consulta. Por exemplo, para reduzir a sobrecarga de bloqueios, o otimizador pode escolher bloqueios no nível de página em um índice, ao executar uma verificação do índice.

O bloqueio dinâmico tem as seguintes vantagens:

• Administração de banco de dados simplificada. Os administradores do banco de dados não precisam ajustar os limites de escalonamento de bloqueio.
• Desempenho melhorado. O Mecanismo de Banco de Dados do SQL Server minimiza a sobrecarga do sistema usando bloqueios apropriados para a tarefa.
• Os desenvolvedores de aplicativos podem se concentrar no desenvolvimento. O Mecanismo de Banco de Dados do SQL Server ajusta o bloqueio automaticamente.

A partir do SQL Server 2008 (10.0.x), o comportamento do escalonamento de bloqueio foi alterado com a introdução da opção LOCK_ESCALATION. Para obter mais informações, veja a opção LOCK_ESCALATION de ALTER TABLE.

Particionamento de bloqueios

Para os grandes sistemas de computador, bloqueios em objetos que são referenciados com frequência podem se tornar um gargalo de desempenho, uma vez que a aquisição e liberação de bloqueios produz contenções em recursos de bloqueios internos. O particionamento de bloqueio melhora o desempenho do bloqueio dividindo um único recurso de bloqueio em vários recursos de bloqueio. Esse recurso só está disponível para sistemas com 16 ou mais CPUs, é habilitado automaticamente e não pode ser desabilitado. Somente bloqueios de objeto podem ser particionados. Bloqueios de objetos que têm um subtipo não são particionados. Para obter mais informações, consulte sys.DM tran_locks (Transact-SQL).

Compreender o particionamento de bloqueios

As tarefas de bloqueio acessam vários recursos compartilhados, dois dos quais são otimizados através de particionamento de bloqueio:

• Spinlock. Controla o acesso a um recurso de bloqueio, como uma linha ou uma tabela.

  Sem particionamento de bloqueio, um spinlock gerencia todas as solicitações de bloqueio para um único recurso de bloqueio. Em sistemas que têm um grande volume de atividade, a contenção pode acontecer enquanto as solicitações de bloqueio esperam que o spinlock fique disponível. Nessa situação, adquirir bloqueios pode se converter em um gargalo e ter um impacto negativo no desempenho.

  Para reduzir a contenção em um único recurso de bloqueio, o particionamento de bloqueio divide um único recurso de bloqueio em vários recursos de bloqueio para distribuir a carga através de vários spinlocks.

• Memory. É usada para armazenar as estruturas de recurso de bloqueio.

  Depois que o spinlock é adquirido, são armazenadas estruturas de bloqueio na memória, que então são acessadas e eventualmente modificadas. Distribuir acesso de bloqueio através de vários recursos ajuda eliminar a necessidade de transferir blocos de memória entre CPUs, que ajudará melhorar o desempenho.

Implementar e monitorar o particionamento de bloqueios

O particionamento de bloqueio é ativado por padrão para sistemas com 16 ou mais CPUs. Quando o particionamento de bloqueios está habilitado, uma mensagem informativa é registrada no log de erros do SQL Server.

Ao adquirir bloqueios em um recurso particionado:

• Só os modos de bloqueio NL, SCH-S, IS, IU e IX modos são adquiridos em uma única partição.

• Bloqueios S (Shared), X (Exclusive) e outros bloqueios em modos diferentes de NL, SCH-S, IS, IU e IX em todas as partições iniciando com partição ID 0 e seguindo na partição a ordem de ID. Esses bloqueios em um recurso particionado usarão mais memória que os bloqueios, no mesmo modo, em um recurso não particionado, desde que cada partição seja efetivamente um bloqueio separado. O aumento de memória é determinado pelo número de partições. Os contadores de bloqueio do SQL Server no Monitor de Desempenho do Windows exibirão informações sobre a memória usada por bloqueios particionados e não particionados.

Uma transação é atribuída a uma partição quando a transação inicia. Para a transação, todas as solicitações de bloqueio que podem ser particionadas usam a partição atribuída a essa transação. Por esse método, o acesso para bloquear recursos do mesmo objeto através de transações diferentes é distribuído através de diferentes partições.

A coluna resource_lock_partition na Exibição de Gerenciamento Dinâmico sys.dm_tran_locks fornece a ID da partição de bloqueio para um recurso de bloqueio particionado. Para obter mais informações, consulte sys.DM tran_locks (Transact-SQL).

Trabalhar com o particionamento de bloqueios

Os exemplos de código a seguir ilustram o particionamento de bloqueio. Nos exemplos, são executadas duas transações em duas sessões diferentes para mostrar o comportamento de particionamento de bloqueio em um sistema de computador com 16 CPUs.

Essas instruções Transact-SQL criam objetos de teste que são usados nos exemplos a seguir.

-- Create a test table.
CREATE TABLE TestTable  (col1 int);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable
    ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable VALUES (1);
GO

Exemplo A

Sessão 1:

Uma instrução SELECT é executada em uma transação. Devido à dica de bloqueio HOLDLOCK, essa instrução adquirirá e reterá um bloqueio IS (Intencional compartilhado) na tabela (para efeitos de ilustração, são ignorados os bloqueios de linha e de página). O bloqueio IS só será adquirido na partição atribuída à transação. Para este exemplo, supõe-se que o bloqueio IS é adquirido na ID 7 da partição.

-- Start a transaction.
BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
    FROM TestTable
    WITH (HOLDLOCK);

Sessão 2:

Uma transação é iniciada e a instrução SELECT que executa sob essa transação adquirirá e reterá um bloqueio S (compartilhado) na tabela. O bloqueio S será adquirido em todas as partições, que resultem em vários bloqueios de tabela, um para cada partição. Por exemplo, em um sistema com 16 CPUs, 16 bloqueios S serão emitidos através de os IDs de partição 0-15. Como o bloqueio S é compatível com o bloqueio de IS, que é mantido na ID 7 da partição pela transação na sessão 1, não há nenhum bloqueio entre as transações.

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCK, HOLDLOCK);

Sessão 1:

A instrução SELECT a seguir é executada na transação que ainda estiver ativa na sessão 1. Devido a dica de bloqueio de tabela exclusivo, a transação tentará adquirir um bloqueio X na tabela . Entretanto, o bloqueio S que está sendo mantido pela transação na sessão 2 bloqueará o bloqueio X na ID 0 da partição.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Exemplo B

Sessão 1:

Uma instrução SELECT é executada em uma transação. Devido à dica de bloqueio HOLDLOCK, essa instrução adquirirá e reterá um bloqueio IS (Intencional compartilhado) na tabela (para efeitos de ilustração, são ignorados os bloqueios de linha e de página). O bloqueio IS só será adquirido na partição atribuída à transação. Para este exemplo, supõe-se que o bloqueio IS é adquirido na ID 6 da partição.

-- Start a transaction.
BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
    FROM TestTable
    WITH (HOLDLOCK);

Sessão 2:

Uma instrução SELECT é executada em uma transação. Devido a dica de bloqueio TABLOCKX, a transação tenta adquirir um bloqueio X (exclusivo) na tabela. Lembre-se que o bloqueio X deve ser adquirido em todas as partições começando com o ID de partição 0. Entretanto, o bloqueio X será adquirido em todos os IDs de partição 0-5 pelo bloqueio IS que é adquirido no ID de partição 6.

Nos IDs de partição 7-15 que o bloqueio X ainda não atingiu, outras transações podem continuar adquirindo bloqueios.

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCKX, HOLDLOCK);

Níveis de isolamento baseados em controle de versão de linha no Mecanismo de Banco de Dados do SQL Server

A partir do SQL Server 2005 (9.x), o Mecanismo de Banco de Dados do SQL Server oferece uma implementação de um nível de isolamento de transação existente com commit de leitura que fornece um instantâneo em nível de instrução usando controle de versão de linha. O mecanismo de banco de dados do SQL Server também oferece um nível de isolamento da transação, o instantâneo, que fornece um instantâneo de nível de transação e que também usa o controle de versão de linha.

O controle de versão de linha é uma estrutura geral no SQL Server que invoca um mecanismo de cópia na gravação quando uma linha é modificada ou excluída. Enquanto a transação está sendo executada, ele requer que a versão anterior da linha esteja disponível para transações que exigem um estado transacional consistente anterior. O controle de versão de linha é usado para:

• Compilar as tabelas inseridas e excluídas em gatilhos. Qualquer linha modificada pelo gatilho tem controle de versão. Isso inclui as linhas modificadas pela instrução que iniciou o gatilho, bem como quaisquer modificações de dados feitas pelo gatilho.
• Oferecer suporte a vários conjuntos de resultados ativos (MARS) Se uma sessão MARS emitir uma instrução de modificação de dados (como INSERT, UPDATE ou DELETE) cada vez que houver um conjunto de resultados ativos, as linhas afetadas pela instrução de modificação terão controle de versão.
• Oferecer suporte a operações de índice que especificam a opção ONLINE.
• Suporte a níveis de isolamento de transações baseados em controle de versão de linha:
  • Uma nova implementação do nível de isolamento READ COMMITTED que usa o controle de versão de linha para fornecer consistência de leitura em nível de instrução.
  • Um novo nível de isolamento, instantâneo, para fornecer a consistência de leitura em nível de transações.

O banco de dados tempdb deve ter espaço suficiente para o armazenamento de versão. Quando tempdb está cheio, as operações de atualização param de gerar versões e continuam a ter êxito, mas as operações de leitura podem falhar porque uma versão de linha específica necessária não existe mais. Isto afeta operações como gatilhos, MARS e indexação online.

O uso de controle de versão de linha para transações de leitura confirmada e de instantâneo é um processo em duas etapas:

1. Defina uma ou ambas as opções de banco de dados READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION como ON.

2. Defina o nível de isolamento da transação apropriado em um aplicativo:

   • Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT está habilitada, as transações que definem o nível de isolamento READ COMMITTED usam o controle de versão de linha.
   • Quando a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION estiver ON, as transações poderão definir o nível de isolamento do instantâneo.

Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION está habilitada, o Mecanismo de Banco de Dados do SQL Server atribui um número de sequência de transação (XSN) a cada transação que manipula dados usando o controle de versão de linha. As transações começam no momento em que uma instrução BEGIN TRANSACTION é executada. Porém, o número de sequência da transação inicia com a primeira operação de leitura ou gravação depois da instrução BEGIN TRANSACTION. O número de sequência da transação é incrementado um por vez sempre que é atribuído.

Quando qualquer uma das opções de banco de dados READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION está ON, cópias lógicas (versões) são mantidas para todas as modificações de dados executadas no banco de dados. Todas as vezes que uma linha é modificada por uma transação específica, a instância do Mecanismo de Banco de Dados do SQL Server armazena uma versão da imagem da linha confirmada anteriormente em tempdb. Cada versão é marcada com o número de sequência de transação da transação que fez a alteração. As versões das linhas modificadas são encadeadas usando uma lista de links. O valor da linha mais recente sempre é armazenado no banco de dados atual e encadeado às linhas com controle de versão armazenadas em tempdb.

Observação

Para a modificação de LOBs (objetos grandes), somente o fragmento alterado é copiado para o armazenamento de versão em tempdb.

As versões da linha são mantidas por tempo suficiente para atender os requisitos das transações executadas em níveis de isolamento com base em controle de versão de linha. O Mecanismo de Banco de Dados do SQL Server rastreia o número de sequência da transação mais antigo e útil e exclui periodicamente todas as versões de linha marcadas com números de sequência de transação inferiores ao número de sequência mais antigo e útil.

Quando ambas as opções do banco de dados estão definidas como OFF, somente as linhas modificadas por gatilhos ou sessões MARS, ou lidas por operações de índice ONLINE, têm controle de versão. Essas versões de linha são liberadas quando já não são necessárias. Um thread em segundo plano é periodicamente executado para remover versões obsoletas da linha.

Observação

Para transações de execução curta, uma versão de uma linha modificada pode ficar armazenada em cache no pool de buffers sem ser gravada nos arquivos de disco do banco de dados tempdb. Se a necessidade da linha com controle de versão for de curta duração, a linha será simplesmente retirada do pool de buffers sem necessariamente gerar uma sobrecarga de E/S.

Comportamento durante a leitura de dados

Quando as transações são executadas em dados de leitura de isolamento com base em controle de versão de linha, as operações de leitura não adquirem bloqueios compartilhados (S) nos dados que estão sendo lidos, portanto, não bloqueiam transações que estão modificando dados. A sobrecarga de recursos de bloqueio também é minimizada conforme o número de bloqueios adquirido é reduzido. O isolamento de leitura confirmada que usa controle de versão de linha e isolamento de instantâneo é desenvolvido para fornecer consistências de leitura em nível de transação e instrução de dados com controle de versão.

Todas as consultas, incluindo as transações executadas em níveis de isolamento com base em controle de versão de linha, adquirem bloqueios Sch-S (estabilidade do esquema) durante a compilação e a execução. Por causa disso, as consultas são bloqueadas quando uma transação simultânea mantém um bloqueio Sch-M (modificação de esquema) na tabela. Por exemplo, uma operação DDL (Linguagem de Definição de Dados) adquire um bloqueio Sch-M antes de modificar as informações do esquema da tabela. As transações de consulta, incluindo aquelas executadas em nível de isolamento com base em controle de versão de linha, são bloqueadas ao tentar adquirir um bloqueio Sch-S. Da mesma forma, uma consulta que mantém um bloqueio Sch-S bloqueará uma transação simultânea que tentar adquirir um bloqueio Sch-M.

Quando uma transação que usa o nível de isolamento de instantâneo é iniciada, a instância do Mecanismo de Banco de Dados do SQL Server registra todas as transações ativas no momento. Quando a transação de instantâneo lê uma linha que tem uma cadeia de versões, o Mecanismo de Banco de Dados do SQL Server segue a cadeia e recupera a linha onde está o número de sequência da transação:

• Está mais próxima, mas inferior ao número de sequência da transação de instantâneo que está lendo a linha.

• Não está na lista das transações ativas quando a transação de instantâneo é iniciada.

As operações de leitura executadas por uma transação de instantâneo recuperam a última versão de cada linha confirmada quando a transação de instantâneo foi iniciada. Isso fornece um instantâneo transacional consistente dos dados, da mesma forma como existiam no início da transação.

As transações de leitura confirmada que usam controle de versão de linha funcionam praticamente do mesmo modo. A diferença é que a transação de leitura confirmada não usa o próprio número de sequência da transação ao escolher versões de linha. Sempre que uma instrução é iniciada, a transação com commit de leitura lê o número de sequência da transação mais recente emitido para essa instância do Mecanismo de Banco de Dados do SQL Server. Esse é o número de sequência da transação usado para selecionar as versões de linha corretas para aquela instrução. Isso permite que as transações de leitura confirmada veja um instantâneo dos dados como ele era no início de cada instrução.

Observação

Embora as transações de leitura confirmada que usam controle de versão de linha forneçam uma exibição transacional consistente dos dados em um nível de instrução, as versões de linha geradas ou acessadas por esse tipo de transação são mantidas até a conclusão da transação.

Comportamento durante a modificação de dados

O comportamento das gravações de dados é significativamente diferente com e sem a presença do bloqueio otimizado.

Modificar dados sem bloqueio otimizado

Em uma transação com confirmação de leitura que usa controle de versão de linha, a seleção de linhas a serem atualizadas é feita por meio de uma varredura de bloqueio em que um bloqueio de atualização (U) é adquirido na linha de dados à medida que os valores de dados são lidos. Isso é igual à transação de leitura confirmada que não usa controle de versão de linha. Se a linha de dados não atender aos critérios de atualização, o bloqueio de atualização será liberado nessa linha, e a próxima linha será bloqueada e verificada.

As transações executadas em isolamento de instantâneo usam uma abordagem otimista para modificação de dados adquirindo bloqueios em dados antes de executar a modificação somente para impor restrições. Do contrário, os bloqueios não são adquiridos em dados até que os dados sejam modificados. Quando uma linha de dados atende aos critérios de atualização, a transação do instantâneo verifica se a linha de dados não foi modificada por uma transação simultânea que foi confirmada depois do início da transação do instantâneo. Se a linha de dados tiver sido modificada fora da transação de instantâneo, ocorrerá um conflito de atualização e a transação de instantâneo será finalizada. O conflito de atualização é tratado pelo Mecanismo de Banco de Dados do SQL Server e não há como desabilitar a detecção de conflito de atualização.

Observação

As operações de atualização executadas sob isolamento de instantâneo são executadas internamente sob isolamento READ COMMITTED quando a transação de instantâneo acessa qualquer um dos seguintes itens:

Uma tabela com uma restrição FOREIGN KEY.

Uma tabela referenciada na restrição FOREIGN KEY de outra tabela.

Uma exibição indexada referenciando mais de uma tabela.

Porém, mesmo de acordo com essas condições, a operação de atualização continuará verificando se os dados não foram modificados por outra transação. Se os dados tiverem sido modificados por outra transação, a transação de instantâneo encontrará um conflito de atualização e será finalizada. Os conflitos de atualização devem ser tratados e repetidos manualmente pelo aplicativo.

Modificar dados com bloqueio otimizado

Com o bloqueio otimizado habilitado e com a opção de banco de dados READ_COMMITTED_SNAPSHOT (RCSI) habilitada, e usando o nível de isolamento padrão READ COMMITTED, os leitores não adquirem nenhum bloqueio, e os gravadores adquirem bloqueios de baixo nível de curta duração, em vez de bloqueios que expiram no final da transação.

A habilitação do RCSI é recomendada para maior eficiência com bloqueio otimizado. 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 manter bloqueios de linha e de página até o final da transação, tanto para leitores quanto para gravadores, o que resulta em maior bloqueio e memória de bloqueio.

Com o RCSI habilitado e ao usar o nível de isolamento padrão READ COMMITTED, os gravadores qualificam as linhas de acordo com o predicado baseado na última versão confirmada da linha, sem adquirir bloqueios U. Uma consulta aguardará somente se a linha se qualificar e houver uma transação de gravação ativa nessa linha ou página. A qualificação com base na última versão confirmada e o bloqueio apenas das linhas qualificadas reduzem o bloqueio e aumentam a simultaneidade.

Se forem detectados conflitos de atualização com o RCSI e no nível de isolamento padrão READ COMMITTED, eles serão tratados e tentados novamente de forma automática, sem nenhum impacto sobre as cargas de trabalho do cliente.

Com o bloqueio otimizado habilitado, usando o nível de isolamento SNAPSHOT, o comportamento dos conflitos de atualização é o mesmo. Os conflitos de atualização devem ser tratados e repetidos manualmente pelo aplicativo.

Observação

Para obter mais informações sobre alterações de comportamento com o recurso de bloqueio após qualificação (LAQ) do bloqueio otimizado, consulte Alterações de comportamento de consulta com bloqueio otimizado e RCSI.

Comportamento no resumo

A tabela a seguir resume as diferenças entre o isolamento de snapshot e o isolamento de READ COMMITTED usando controle de versão de linha.

Propriedade	Nível de isolamento de leitura confirmada usando o controle de versão de linha	Nível de isolamento do instantâneo
A opção de banco de dados que deve ser definida como ON para habilitar o suporte exigido.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
Como uma sessão solicita o tipo específico de controle de versão de linha.	Use o nível padrão de isolamento de leitura confirmada ou execute a instrução SET TRANSACTION ISOLATION LEVEL para especificar o nível de isolamento READ COMMITTED. Isso pode ser feito depois do início da transação.	Exige a execução de SET TRANSACTION ISOLATION LEVEL para especificar o nível de isolamento SNAPSHOT antes do início da transação.
A versão dos dados lidos por instruções.	Todos os dados que foram confirmados antes do início de cada instrução.	Todos os dados que foram confirmados antes do início de cada transação.
Como as atualizações são controladas.	Sem bloqueio otimizado: reverte das versões de linha para os dados reais para selecionar as linhas a serem atualizadas e usa bloqueios de atualização nas linhas de dados selecionadas. Adquire bloqueios exclusivos em linhas de dados atuais a serem modificadas. Nenhuma detecção de conflito de atualização. Com bloqueio otimizado: as linhas são selecionadas com base na última versão confirmada sem que nenhum bloqueio seja adquirido. Se as linhas se qualificarem para a atualização, serão adquiridos bloqueios exclusivos de linha ou página. Se forem detectados conflitos de atualização, eles serão tratados e tentados novamente de forma automática.	Usa versões de linha para selecionar linhas a serem atualizadas. Tenta adquirir um bloqueio exclusivo na linha de dados reais a ser modificada e, se os dados tiverem sido modificados por outra transação, ocorrerá um conflito de atualização e a transação de instantâneo será finalizada.
Detecção de conflito de atualização	Sem bloqueio otimizado: nenhum. Com bloqueio otimizado: se forem detectados conflitos de atualização, eles serão tratados e tentados novamente de forma automática.	Suporte integrado. Não pode ser desabilitado.

Uso do recurso de controle de versão de linha

A estrutura de controle de versão de linha suporta os seguintes recursos disponíveis no SQL Server:

• Gatilhos
• MARS (vários conjuntos de resultados ativos)
• Indexação online

A estrutura de controle de versão de linha também dá suporte aos seguintes níveis de isolamento da transação baseada no controle de versão de linha que não estão habilitados por padrão:

• Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT estiver ON, as transações READ_COMMITTED fornecerão consistência usando o controle de versão de linha de leitura no nível da instrução.
• Quando a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION estiver ON, as transações SNAPSHOT fornecerão consistência usando o controle de versão de linha de leitura no nível da transação.

Os níveis de isolamento com base no controle de versão de linha reduzem o número de bloqueios adquiridos pela transação eliminando o uso de bloqueios compartilhados em operações de leitura. Isso aumenta o desempenho do sistema reduzindo os recursos usados para gerenciar bloqueios. O desempenho também é aumentado pela redução do número de vezes em que uma transação fica bloqueada por bloqueios adquiridos por outras transações.

Os níveis de isolamento com base no controle de versão de linha aumentam os recursos necessários pelas modificações de dados. Habilitar essas opções faz com que todas as modificações de dados no banco de dados sejam controladas por versão. Uma cópia dos dados antes da modificação é armazenada em tempdb mesmo quando não há transações habilitas usando o isolamento baseado em versão de linha. Os dados após a modificação incluem um ponteiro para os dados com versão armazenados em tempdb. Para objetos grandes, somente parte do objeto que foi alterado é copiada para tempdb.

Espaço usado no tempdb

Para cada instância do Mecanismo de Banco de Dados do SQL Server, o tempdb deve ter espaço suficiente para manter as versões de linha geradas para cada banco de dados na instância. O administrador do banco de dados deve garantir que o tempdb tenha espaço suficiente para suportar o armazenamento de versões. Existem dois repositórios de versões no tempdb:

• O repositório de versão de compilação de índices online é usado para compilações de índices online em todos os bancos de dados.
• O repositório da versão comum é usado para todas as outras operações de modificação de dados em todos os bancos de dados.

As versões de linha devem ser armazenadas enquanto uma transação ativa precisar acessá-las. A cada minuto, um thread em segundo plano remove as versões de linha que não são mais necessárias e libera o espaço da versão no tempdb. Uma transação de longa execução impedirá que o espaço do repositório de versão seja liberado, se as seguintes condições forem encontradas:

• Ela usa isolamento com base em controle de versão de linha.
• Ela usa gatilhos, MARS ou operações de compilação de índices online.
• Ela gera versões de linha.

Observação

Quando um gatilho é acionado em uma transação, são mantidas as versões de linha criadas pelo gatilho até o término da transação, mesmo quando as versões de linha não forem mais necessárias após o gatilho ser concluído. Isso também se aplica a transações de leitura confirmada que usam controle de versão de linha. Com esse tipo de transação, uma exibição consistente de maneira transacional do banco de dados é necessária apenas para cada instrução na transação. Isso significa que as versões de linha criadas para uma instrução na transação não são necessárias depois que a instrução é concluída. Porém, as versões de linha criadas por cada instrução na transação são mantidas até que a transação seja concluída.

Quando o tempdb fica sem espaço, o Mecanismo de Banco de Dados do SQL Server força a redução dos armazenamentos de versões. Durante o processo de redução, as transações mais longas que estiverem sendo executadas e que ainda não geraram versões de linha serão marcadas como vítimas. Uma mensagem 3967 é gerada no log de erros para cada transação vítima. Se uma transação for marcada como uma vítima, não poderá ler as versões de linha no armazenamento da versão. Ao tentar ler versões de linhas, a mensagem 3966 é gerada e a transação é revertida. Se o processo de redução for bem-sucedido, o espaço ficará disponível no tempdb. Caso contrário, o tempdb ficará sem espaço e ocorre o seguinte:

• As operações de gravação continuarão a ser executadas, mas não gerarão versões. Uma mensagem informativa (3959) será exibida no log de erros, mas a transação que grava dados não será afetada.

• As transações que tentam acessar versões de linha que não foram geradas por causa de uma reversão completa do tempdb terminam com o erro 3958.

Espaço usado em linhas de dados

Cada linha de banco de dados pode usar até 14 bytes ao término da linha para obter informações sobre o controle de versão de linha. As informações sobre o controle de versão de linha contêm o número de sequência da transação que confirmou a versão e o ponteiro da linha controlada por versão. Esses 14 bytes são adicionados na primeira vez em que a linha é modificada, ou quando uma nova linha é inserida, em qualquer uma destas condições:

• As opções READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION estão ON.
• A tabela tem um gatilho.
• Os MARS (conjuntos de resultados ativos múltiplos) estão sendo usados.
• As operações de compilação de índices online estão sendo executadas atualmente na tabela.

Esses 14 bytes são removidos da linha do banco de dados na primeira vez em que a linha é modificada nestas condições:

• As opções READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION estão OFF.
• O gatilho não existe mais na tabela.
• O MARS não está sendo usado.
• As operações de compilação de índices online não estão sendo executadas no momento.

Se você usar qualquer um dos recursos de controle de versão de linha, poderá ser necessário alocar espaço em disco adicional para o banco de dados para acomodar a linha de 14 bytes por banco de dados. A adição das informações de controle de versão de linha poderá causar divisões da página de índice ou a alocação de uma nova página de dados se não houver espaço disponível suficiente na página atual. Por exemplo, se o comprimento médio da linha for 100 bytes, os 14 bytes adicionais farão com que uma tabela existente cresça até 14%.

Reduzir o fator de preenchimento pode ajudar a impedir ou diminuir a fragmentação de páginas de índice. Para exibir informações de fragmentação para dados e índices de uma tabela ou exibição, você pode usar sys.dm_db_index_physical_stats.

Espaço usado em objetos grandes

O Mecanismo de Banco de Dados do SQL Server oferece suporte a seis tipos de dados que podem conter cadeias de caracteres grandes de até 2 gigabytes (GB): nvarchar(max), varchar(max), varbinary(max), ntext, text e image. Grandes cadeias de caracteres armazenadas que usam esses tipos de dados são armazenadas em uma série de fragmentos de dados vinculados à linha de dados. As informações de controle de versão de linha estão armazenadas em cada fragmento usado para armazenar grandes cadeias de caracteres. Os fragmentos de dados são uma coleção de páginas dedicadas a objetos grandes em uma tabela.

Conforme novos valores grandes forem adicionados a um banco de dados, eles serão alocados com o máximo de 8040 bytes de dados por fragmento. As versões anteriores do Mecanismo de Banco de Dados do SQL Server armazenavam até 8080 bytes de dados ntext, text ou image por fragmento.

Os dados LOB (objeto grande) existentes em ntext, text e image não são atualizados para dar espaço às informações de controle de versão de linha quando um banco de dados é atualizado para o SQL Server de uma versão anterior do SQL Server. Porém, a primeira vez em que os dados de LOB são modificados, eles são atualizados dinamicamente para habilitar o armazenamento de informações de controle de versão. Isso acontecerá até mesmo se não forem geradas versões de linha. Depois que os dados de LOB são atualizados, o número máximo de bytes armazenados por fragmento é reduzido de 8080 bytes para 8040 bytes. O processo de atualização é equivalente a excluir o valor LOB e reinserir o mesmo valor. Os dados LOB são atualizados mesmo que apenas 1 byte seja modificado. Essa é uma operação única para cada coluna ntext, text ou image, mas cada operação pode gerar uma grande quantidade de alocações de página e atividade de E/S dependendo do tamanho dos dados de LOB. Isso também poderá gerar uma grande quantidade de atividade de registro se a modificação for totalmente registrada. As operações WRITETEXT e UPDATETEXT serão registradas em log minimamente se o modelo de recuperação do banco de dados não for definido como FULL.

Os tipos de dados nvarchar(max), varchar(max) e varbinary(max) não estão disponíveis nas versões anteriores do SQL Server. Portanto, eles não têm nenhum problema de atualização.

Deve ser alocado espaço em disco suficiente para acomodar esse requisito.

Monitorar o controle de versão de linha e o repositório de versões

Para monitorar o controle de versão de linha, o repositório de versões e os processos de isolamento de instantâneos quanto a desempenho e problemas, o SQL Server fornece ferramentas na forma de DMVs (exibições de gerenciamento dinâmico) e contadores de desempenho no Monitor do Sistema do Windows.

DMVs

As DMVs a seguir fornecem informações sobre o estado atual do sistema tempdb e o armazenamento de versões, bem como sobre as transações que usam o controle de versão de linha.

• sys.dm_db_file_space_usage. Retorna informações de uso do espaço de cada arquivo no banco de dados. Para obter mais informações, veja sys.dm_db_file_space_usage (Transact-SQL).

• sys.dm_db_session_space_usage. Retorna a alocação de páginas e a atividade de desalocação por sessão do banco de dados. Para obter mais informações, veja sys.dm_db_session_space_usage (Transact-SQL).

• sys.dm_db_task_space_usage. Retorna a alocação de páginas e a atividade de desalocação por tarefa do banco de dados. Para obter mais informações, veja sys.dm_db_task_space_usage (Transact-SQL).

• sys.dm_tran_top_version_generators. Retorna uma tabela virtual para os objetos que produzem a maioria das versões no repositório de versão. Agrupa os 256 maiores registros agregados por database_id e rowset_id. Use essa função para localizar os maiores usuários do repositório de versão. Para obter mais informações, veja sys.DM tran_top_version_generators (Transact-SQL).

• sys.dm_tran_version_store. Retorna uma tabela virtual que exibe todos os registros de versão no repositório de versão comum. Para obter mais informações, veja sys.dm_tran_version_store (Transact-SQL).

• sys.dm_tran_version_store_space_usage. Retorna uma tabela virtual que exibe o espaço total no tempdb usado pelos registros do armazenamento de versões para cada banco de dados. Para obter mais informações, veja sys.dm_tran_version_store_space_usage (Transact-SQL).

  Observação

  Os objetos do sistema sys.dm_tran_top_version_generators e sys.dm_tran_version_store são funções potencialmente muito caras de serem executadas, pois ambos consultam todo o repositório de versões, que pode ser muito grande. O sys.dm_tran_version_store_space_usage é eficiente e não é caro de executar porque não navega pelos registros individuais do repositório de versões e, em vez disso, retorna o espaço agregado do repositório de versões consumido no tempdb por banco de dados.

• sys.dm_tran_active_snapshot_database_transactions. Retorna uma tabela virtual para todas as transações ativas em todos os bancos de dados da instância do SQL Server que usam controle de versão de linha. As transações de sistema não são exibidas nessa DMV. Para obter mais informações, veja sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

• sys.dm_tran_transactions_snapshot. Retorna uma tabela virtual que exibe instantâneos tirados por cada transação. O instantâneo contém o número de sequência das transações ativas que usam controle de versão de linha. Para obter mais informações, veja sys.dm_tran_transactions_snapshot (Transact-SQL).

• sys.dm_tran_current_transaction. Retorna uma única linha que exibe informações de estado relacionadas ao controle de versão de linha da transação na sessão atual. Para obter mais informações, veja sys.dm_tran_current_transaction (Transact-SQL).

• sys.dm_tran_current_snapshot. Retorna uma tabela virtual que exibe todas as transações ativas no momento em que a transação de isolamento do instantâneo atual é iniciada. Se a transação atual estiver usando um isolamento de instantâneo, essa função não retornará nenhuma linha. A DMV sys.dm_tran_current_snapshot é semelhante a sys.dm_tran_transactions_snapshot, exceto pelo fato de que retorna apenas as transações ativas do instantâneo atual. Para obter mais informações, veja sys.DM tran_current_snapshot (Transact-SQL).

Contadores de desempenho

Contadores de desempenho do SQL Server fornecem informações sobre o desempenho do sistema afetado pelos processos do SQL Server. Os seguintes contadores de desempenho monitoram tempdb e o armazenamento de versões, bem como as transações que usam controle de versão de linha. Os contadores de desempenho estão contidos no objeto de desempenho SQLServer:Transactions.

• Espaço livre em tempdb (KB). Monitora a quantidade, em quilobytes (KB), de espaço livre no banco de dados tempdb. Deve haver espaço livre suficiente em tempdb para lidar com o armazenamento de versões que suporta o isolamento de instantâneos.

  A fórmula a seguir fornece uma estimativa aproximada do tamanho do armazenamento de versão. Para transações de longa execução, pode ser útil monitorar a taxa de geração e limpeza para calcular o tamanho máximo de armazenamento de versão.

  [tamanho do repositório de versão comum] = 2 * [dados do repositório de versão gerados por minuto] * [tempo de execução mais longo (minutos) da transação]

  O tempo de execução mais longo das transações não deve incluir as compilações de índices online. Como essas operações podem demorar muito tempo em tabelas muito grandes, as compilações de índices online usam um armazenamento de versão separado. O tamanho aproximado do repositório de versão de compilação de índices online é igual à quantidade de dados modificados na tabela, incluindo todos os índices, enquanto a compilação de índices online estiver ativa.

• Tamanho do Repositório de Versão (KB). Monitora o tamanho em KB de todos os armazenamentos de versão. Essas informações ajudam a determinar a quantidade de espaço necessário no banco de dados tempdb para o repositório de versões. O monitoramento desse contador durante um período de tempo fornece uma estimativa útil do espaço adicional necessário para tempdb.

• Taxa de geração de versão (KB/s). Monitora a taxa de geração de versão, em KB por segundo, em todos os repositórios de versão.

• Taxa de limpeza de versão (KB/s). Monitora a taxa de limpeza de versão, em KB por segundo, em todos os repositórios de versão.

  Observação

  As informações da taxa de geração de versões (KB/s) e da taxa de limpeza de versões (KB/s) podem ser usadas para prever os requisitos de espaço do tempdb.

• Contagem de unidade de Repositório de Versão. Monitora a contagem de unidades do repositório de versão.

• Criação de unidade de Repositório de Versão. Monitora o número total de unidades de repositório de versão criadas para armazenar versões de linha depois que a instância tiver sido iniciada.

• Truncamento de unidade de Repositório de Versão. Monitora o número total de unidades de repositório de versão truncadas depois que a instância tiver sido iniciada. Uma unidade de armazenamento de versão é truncada quando o SQL Server determina que nenhuma das linhas de versão armazenadas na unidade de armazenamento de versão é necessária para executar transações ativas.

• Taxa de conflito de atualização. Monitora a proporção de transações de instantâneos de atualização que têm conflitos de atualização em relação ao número total de transações de instantâneos de atualização.

• Tempo de execução da transação mais longo. Monitora o tempo de execução mais longo em segundos de qualquer transação que usa controle de versão de linha. Pode ser usado para determinar se alguma transação está sendo executada por uma quantidade de tempo excessiva.

• Transações. Monitora o número total de transações ativas. Não inclui as transações do sistema.

• Transações de instantâneo. Monitora o número total de transações de instantâneo ativas.

• Transações de instantâneo de atualização. Monitora o número total de transações de instantâneo ativas que executam operações de atualização.

• Transações da versão de não instantâneo. Monitora o número total de transações de não instantâneo ativas que geram registros de versão.

  Observação

  A soma de Transações de Instantâneo de Atualização e Transações de Versão Não Instantâneo representa o número total de transações que participam da geração de versão. A diferença de Transações de Instantâneo e Transações de Instantâneo de Atualização informa o número de transações de instantâneo somente leitura.

Exemplo de nível de isolamento com base em controle de versão de linha

Os exemplos a seguir mostram as diferenças de comportamento entre transações de isolamento de instantâneo e transações de leitura confirmada que usam controle de versão de linha.

R. Trabalhar com isolamento de instantâneos

Neste exemplo, uma transação sendo executada sob um isolamento de instantâneo lê dados que são então modificados por outra transação. A transação do instantâneo não bloqueia a operação de atualização executada pela outra transação e continua lendo dados da linha com controle de versão, ao mesmo tempo em que ignora a modificação de dados. Porém, quando a transação de instantâneo tentar modificar os dados que já foram modificados pela outra transação, a transação de instantâneo gera um erro e é terminada.

Na sessão 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;
    -- This SELECT statement will return
    -- 48 vacation hours for the employee.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Sessão 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;
    -- Subtract a vacation day from employee 4.
    -- Update is not blocked by session 1 since
    -- under snapshot isolation shared locks are
    -- not requested.
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

    -- Verify that the employee now has 40 vacation hours.
    SELECT VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Na sessão 1:

    -- Reissue the SELECT statement - this shows
    -- the employee having 48 vacation hours. The
    -- snapshot transaction is still reading data from
    -- the versioned row.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Sessão 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

Na sessão 1:

    -- Reissue the SELECT statement - this still
    -- shows the employee having 48 vacation hours
    -- even after the other transaction has committed
    -- the data modification.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

    -- Because the data has been modified outside of the
    -- snapshot transaction, any further data changes to
    -- that data by the snapshot transaction will cause
    -- the snapshot transaction to fail. This statement
    -- will generate a 3960 error and the transaction will
    -- terminate.
    UPDATE HumanResources.Employee
        SET SickLeaveHours = SickLeaveHours - 8
        WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION
GO

B. Trabalhar com commit de leitura usando controle de versão de linha

Neste exemplo, uma transação de leitura confirmada que usa o controle de versão de linha é executada ao mesmo tempo que outra transação. A transação de leitura confirmada se comporta diferentemente de uma transação de instantâneo. Como uma transação de instantâneo, a transação de leitura confirmada lerá controles de versão de linhas, mesmo após a outra transação ter modificado os dados. Entretanto, diferentemente de uma transação de instantâneo, a transação de leitura confirmada:

• Lerá os dados modificados depois que a outra transação confirmar as mudanças de dados.
• Poderá atualizar os dados modificados pela outra transação onde a transação de instantâneo não pôde.

Na sessão 1:

USE AdventureWorks2022;  -- Or any earlier version of the AdventureWorks database.
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;
    -- This SELECT statement will return
    -- 48 vacation hours for the employee.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Sessão 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;
    -- Subtract a vacation day from employee 4.
    -- Update is not blocked by session 1 since
    -- under read-committed using row versioning shared locks are
    -- not requested.
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

    -- Verify that the employee now has 40 vacation hours.
    SELECT VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Na sessão 1:

    -- Reissue the SELECT statement - this still shows
    -- the employee having 48 vacation hours. The
    -- read-committed transaction is still reading data
    -- from the versioned row and the other transaction
    -- has not committed the data changes yet.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Sessão 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

Na sessão 1:

    -- Reissue the SELECT statement which now shows the
    -- employee having 40 vacation hours. Being
    -- read-committed, this transaction is reading the
    -- committed data. This is different from snapshot
    -- isolation which reads from the versioned row.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

    -- This statement, which caused the snapshot transaction
    -- to fail, will succeed with read-committed using row versioning.
    UPDATE HumanResources.Employee
        SET SickLeaveHours = SickLeaveHours - 8
        WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Habilitar níveis de isolamento baseados no controle de versão de linha

Os administradores de banco de dados controlam as configurações no nível do banco de dados para controle de versão de linha usando as opções de do banco de dados READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION na instrução ALTER DATABASE.

Quando a opção READ_COMMITTED_SNAPSHOT do banco de dados está definida como ON, os mecanismos usados para dar suporte à opção são ativados imediatamente. Ao configurar a opção READ_COMMITTED_SNAPSHOT, apenas a conexão que executa o comando ALTER DATABASE é permitida no banco de dados. Não deve haver nenhuma outra conexão aberta no banco de dados até que ALTER DATABASE esteja concluído. O banco de dados não precisa estar no modo de usuário único.

O seguinte comando Transact-SQL habilita READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

Quando a opção ALLOW_SNAPSHOT_ISOLATION de banco de dados está habilitada, a instância do Mecanismo de Banco de Dados do SQL Server não gera versões de linha para dados modificados até que todas as transações ativas que modificaram dados no banco de dados sejam concluídas. Se houver transações de modificação ativas, o SQL Server definirá o estado da opção como PENDING_ON. Depois que todas as modificações de transações estiverem concluídas, o estado da opção é alterado para ON. Os usuários não podem iniciar uma transação de instantâneo nesse banco de dados até que a opção esteja completamente ON. O banco de dados passa por um estado PENDING_OFF quando o administrador de banco de dados define a opção ALLOW_SNAPSHOT_ISOLATION como OFF.

A instrução Transact-SQL a seguir habilitará ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

A tabela a seguir relaciona e descreve os estados da opção ALLOW_SNAPSHOT_ISOLATION. Usar a opção ALTER DATABASE com a opção ALLOW_SNAPSHOT_ISOLATION não bloqueará os usuários que estiverem acessando os dados do banco de dados no momento.

Estado da estrutura de isolamento de instantâneo para banco de dados atual	Descrição
OFF	O suporte para as transações de isolamento de instantâneo não está ativado. Não é permitida nenhuma transação de isolamento de instantâneo.
PENDING_ON	O suporte para as transações de isolamento de instantâneo está em estado de transição (de OFF para ON). As transações abertas precisam ser concluídas. Não é permitida nenhuma transação de isolamento de instantâneo.
ATIVADO	O suporte para as transações de isolamento de instantâneo está ativado. São permitidas transações de instantâneo.
PENDING_OFF	O suporte para as transações de isolamento de instantâneo está em estado de transição (de ON para OFF). As transações de instantâneo iniciadas depois dessa hora não poderão acessar este banco de dados. Atualizar transações ainda paga o custo de controle de versão neste banco de dados. As transações de instantâneo existentes ainda podem acessar este banco de dados sem problemas. O estado PENDING_OFF não se torna desabilitado até que todas as transações de instantâneo, que estavam ativas quando o estado de isolamento de instantâneo do banco de dados era habilitado, sejam concluídas.

Use as exibições do catálogo sys.databases para determinar o estado das duas opções de controle de versão de linha do banco de dados.

Todas as atualizações das tabelas de usuários e de algumas tabelas do sistema armazenadas em master e msdb geram versões de linha.

A opção ALLOW_SNAPSHOT_ISOLATION é automaticamente definida como ON nos bancos de dados master e msdb e não pode ser desabilitada.

Os usuários não podem definir a opção READ_COMMITTED_SNAPSHOT como ON em master, tempdb ou msdb.

Usar níveis de isolamento baseados no controle de versão de linha

A estrutura de controle de versão de linha está sempre habilitada no SQL Server e é usada por vários recursos. Além de fornecer níveis de isolamento com base em controle de versão de linha, ela é usada para oferecer suporte a modificações feitas em gatilhos e em sessões MARS (Multiple Active Result Sets), e para oferecer suporte à leitura de dados de operações de índice ONLINE.

Os níveis de isolamento com base em controle de versão de linha são habilitados no banco de dados. Todos os aplicativos que acessam os objetos de bancos de dados habilitados podem executar consultas usando os seguintes níveis de isolamento:

• Leitura confirmada que usa controle de versão de linha pela definição da opção de banco de dados READ_COMMITTED_SNAPSHOT como ON, como mostrado no seguinte exemplo de código:

  ALTER DATABASE AdventureWorks2022
      SET READ_COMMITTED_SNAPSHOT ON;
  

  Quando o banco de dados está habilitado para READ_COMMITTED_SNAPSHOT, todas as consultas executadas no nível de isolamento READ COMMITTED usam o controle de versão de linha, o que significa que as operações de leitura não bloqueiam as operações de atualização.

• Isolamento do instantâneo através da definição da opção de banco de dados ALLOW_SNAPSHOT_ISOLATION como ON, como mostrado no exemplo de código seguinte:

  ALTER DATABASE AdventureWorks2022
      SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  Uma transação executada em isolamento de instantâneo pode acessar tabelas do banco de dados habilitadas para instantâneo. Para acessar tabelas que não foram habilitadas para instantâneo, é preciso alterar o nível de isolamento. Por exemplo, o exemplo de código a seguir mostra uma instrução SELECT que une duas tabelas durante a execução de uma transação de instantâneo. Uma das tabelas pertence a um banco de dados no qual o isolamento de instantâneo não está habilitado. Quando a instrução SELECT for executada no isolamento de instantâneo, não será executada com êxito.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  BEGIN TRAN
      SELECT t1.col5, t2.col5
          FROM Table1 as t1
          INNER JOIN SecondDB.dbo.Table2 as t2
              ON t1.col1 = t2.col2;
  

  O exemplo de código a seguir mostra a mesma instrução SELECT que foi modificada para alterar o nível de isolamento da transação para leitura confirmada. Em razão dessa mudança, a instrução SELECT será executada com êxito.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  BEGIN TRAN
      SELECT t1.col5, t2.col5
          FROM Table1 as t1
          WITH (READCOMMITTED)
          INNER JOIN SecondDB.dbo.Table2 as t2
              ON t1.col1 = t2.col2;
  

Limitações de transações usando níveis de isolamento com base em controle de versão de linha

Considere as limitações a seguir ao trabalhar com níveis de isolamento baseados em controle de versão de linha:

• READ_COMMITTED_SNAPSHOT não pode ser habilitado em tempdb, msdb ou master.

• As tabelas temporárias globais são armazenadas em tempdb. Ao acessar tabelas temporárias globais em uma transação de instantâneo, uma das seguintes ações deve ocorrer:

  • Defina a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION como ON em tempdb.
  • Use uma dica de isolamento para alterar o nível de isolamento da instrução.

• Transações de instantâneo falham quando:

  • O banco de dados é transformado em somente leitura após o início da transação de instantâneo, mas antes que a transação de instantâneo acesse o banco de dados.
  • Se objetos forem acessados em vários bancos de dados, um estado de banco de dados terá sido alterado de tal modo que a recuperação do banco de dados ocorrerá após o início da transação de instantâneo, mas antes que a transação de instantâneo acesse o banco de dados. Por exemplo: o banco de dados foi definido como OFFLINE e depois como ONLINE; o banco de dados fecha automaticamente e se abre ou o banco de dados é desanexado e anexado.

• Não há suporte para transações distribuídas, inclusive consultas em bancos de dados particionados distribuídos em isolamento de instantâneo.

• O SQL Server não mantém várias versões dos metadados do sistema. As instruções DDL (Linguagem de Definição de Dados) em tabelas e em outros objetos de banco de dados (índices, exibições, tipos de dados, procedimentos armazenados e funções CLR (Common Language Runtime)) alteram metadados. Se uma instrução DDL modificar um objeto, qualquer referência simultânea ao objeto em isolamento de instantâneo fará com que a transação de instantâneo falhe. Transações de leitura confirmada não têm essa limitação quando a opção de banco de dados READ_COMMITTED_SNAPSHOT é ON.

  Por exemplo, um administrador de banco de dados executa a instrução ALTER INDEX a seguir.

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID
      ON HumanResources.Employee REBUILD;
  GO
  

  Qualquer transação de instantâneo que esteja ativa quando a instrução ALTER INDEX for executada receberá um erro, caso ela tente fazer referência à tabela HumanResources.Employee após a execução da instrução ALTER INDEX. As transações de leitura confirmada que usam controle de versão de linha não são afetadas.

  Observação

  As operações BULK INSERT podem causar alterações a metadados da tabela de destino (por exemplo, ao desabilitar verificações de restrição). Quando isso ocorre, as transações de isolamento de instantâneo simultâneas que acessam tabelas inseridas em massa falham.

Personalizar bloqueios e o controle de versão de linhas

Personalizar o tempo limite de bloqueio

Quando uma instância do Mecanismo de Banco de Dados do Microsoft SQL Server não pode conceder um bloqueio a uma transação porque outra transação já possui um bloqueio conflitante no recurso, a primeira transação fica bloqueada aguardando a liberação do bloqueio existente. Por padrão, não existe período obrigatório de tempo limite e nenhuma forma para testar se um recurso está bloqueado antes de bloqueá-lo, exceto a tentativa de acessar os dados (e potencialmente ser bloqueado indefinidamente).

Observação

No SQL Server, use a exibição de gerenciamento dinâmico sys.dm_os_waiting_tasks para determinar se um processo está sendo bloqueado e quem o está bloqueando. Em versões anteriores do SQL Server, use o procedimento armazenado do sistema sp_who. Para obter mais informações e exemplos, consulte Entender e resolver problemas de bloqueio do SQL Server.

A configuração LOCK_TIMEOUT permite que um aplicativo defina um tempo máximo que uma instrução espera um recurso bloqueado. Quando uma instrução espera por mais tempo do que a configuração LOCK_TIMEOUT, a instrução bloqueada é cancelada automaticamente e a mensagem de erro 1222 (Lock request time-out period exceeded) é retornada ao aplicativo. Entretanto, nenhuma transação que contenha a instrução é revertida ou cancelada pelo SQL Server. Portanto, o aplicativo deve ter um identificador de erro que possa interceptar a mensagem de erro 1222. Se um aplicativo não interceptar o erro, o aplicativo poderá continuar sem reconhecer que uma instrução individual dentro de uma transação foi cancelada, e poderão ocorrer erros porque as instruções posteriores da transação podem depender da instrução que nunca foi executada.

A implementação de um identificador de erro que intercepte a mensagem de erro 1222 permite que um aplicativo possa lidar com a situação de tempo limite e execute uma ação para corrigir a situação, como: automaticamente enviar novamente a instrução que estava bloqueada ou reverter toda a transação.

Para determinar a configuração LOCK_TIMEOUT atual, execute a função @@LOCK_TIMEOUT:

SELECT @@lock_timeout;
GO

Personalizar o nível de isolamento da transação

READ COMMITTED é o nível de isolamento padrão para o Mecanismo de Banco de Dados do Microsoft SQL Server. Se um aplicativo precisar operar em um nível de isolamento diferente, poderá usar os seguintes métodos para definir o nível de isolamento:

• Executar a instrução SET TRANSACTION ISOLATION LEVEL.
• Os aplicativos ADO.NET que usam o namespace gerenciado System.Data.SqlClient podem especificar uma opção IsolationLevel usando o método SqlConnection.BeginTransaction.
• Aplicativos que usam ADO podem definir a propriedade Autocommit Isolation Levels.
• Ao iniciar uma transação, os aplicativos que usam OLE DB podem chamar ITransactionLocal::StartTransaction com isoLevel definido para o nível de isolamento de transação desejado. Ao especificar o nível de isolamento no modo de confirmação automática, os aplicativos que usam OLE DB podem definir a propriedade DBPROPSET_SESSIONDBPROP_SESS_AUTOCOMMITISOLEVELS como o nível de isolamento da transação desejada.
• Os aplicativos que usam ODBC podem definir o atributo SQL_COPT_SS_TXN_ISOLATION usando SQLSetConnectAttr.

Quando o nível de isolamento é especificado, o comportamento de bloqueio de todas as consultas e instruções de linguagem de manipulação de dados (DML) na sessão do SQL Server opera nesse nível de isolamento. O nível de isolamento permanece em vigor até o término da sessão ou até que o nível de isolamento seja definido como outro nível.

O exemplo seguinte define o nível de isolamento SERIALIZABLE:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID
    FROM HumanResources.Employee;
GO

O nível de isolamento pode ser substituído por uma consulta individual ou instruções DML, se necessário, especificando uma dica no nível de tabela. A especificação de uma dica no nível de tabela não afeta outras instruções na sessão. Recomendamos que as dicas no nível de tabela sejam usadas para alterar o comportamento padrão apenas quando absolutamente necessário.

O Mecanismo de Banco de Dados do SQL Server pode ter que adquirir bloqueios ao ler metadados, mesmo quando o nível de isolamento está definido para um nível em que os bloqueios de compartilhamento não são solicitados ao ler dados. Por exemplo, uma transação em execução no nível de isolamento de leitura não confirmada não adquire bloqueios de compartilhamento ao ler dados, mas pode solicitar bloqueios ao ler uma exibição de catálogo do sistema. Isso significa que é possível que uma transação de leitura não confirmada cause o bloqueio ao consultar uma tabela quando uma transação simultânea estiver modificando os metadados dessa tabela.

Para determinar o nível de isolamento da transação definido atualmente, use a instrução DBCC USEROPTIONS, como mostrado no exemplo a seguir. O conjunto de resultados pode ser diferente do conjunto de resultados em seu sistema.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Este é o conjunto de resultados.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Dicas de bloqueio

Dicas de bloqueio podem ser especificadas para referências de tabela individuais nas instruções SELECT, INSERT, UPDATE e DELETE. As dicas especificam o tipo de bloqueio ou controle de versão de linha que a instância do Mecanismo de Banco de Dados do SQL Server usa para os dados da tabela. Dicas de bloqueio de tabelas podem ser usadas quando é necessário um controle mais refinado dos tipos de bloqueios adquiridos em um objeto. Essas dicas de bloqueio substituem o nível de isolamento da transação atual na sessão.

Observação

Dicas de bloqueio não são recomendadas para uso quando o bloqueio otimizado está habilitado. Embora as dicas de tabela e consulta sejam respeitadas, elas reduzem o benefício do bloqueio otimizado. Para obter mais informações, consulte Evitar dicas de bloqueio com bloqueio otimizado.

Para obter mais informações sobre as dicas de bloqueio específicas e seus comportamentos, veja Dicas de tabela(Transact-SQL).

Observação

O Mecanismo de Banco de Dados do SQL Server quase sempre escolhe o nível de bloqueio correto. É recomendável que as dicas de bloqueio no nível de tabela sejam usadas para alterar o comportamento de bloqueio padrão apenas quando necessário. Não permitir um nível de bloqueio pode afetar adversamente a simultaneidade.

O Mecanismo de Banco de Dados do SQL Server pode ter que adquirir bloqueios ao ler metadados, mesmo ao processar uma seleção com uma dica de bloqueio que evita solicitações de bloqueios de compartilhamento ao ler dados. Por exemplo, uma SELECT que usa a dica NOLOCK não adquire bloqueios de compartilhamento ao ler dados, mas, às vezes, pode solicitar bloqueios ao ler uma exibição de catálogo do sistema. Isso significa que é possível que uma instrução SELECT usando NOLOCK seja bloqueada.

Conforme mostrado no exemplo a seguir, se o nível de isolamento da transação for definido como SERIALIZABLE e a dica de bloqueio em nível de tabela NOLOCK for usada com a instrução SELECT, os bloqueios de intervalo de chaves normalmente usados para manter transações serializáveis não serão adquiridos.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
    FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT
        resource_type,
        resource_subtype,
        request_mode
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@spid;

-- End the transaction.
ROLLBACK;
GO

O único bloqueio adquirido que faz referência a HumanResources.Employee é um bloqueio de estabilidade de esquema (Sch-S). Nesse caso, a seriabilidade não é mais garantida.

No SQL Server, a opção LOCK_ESCALATION de ALTER TABLE pode desfavorecer bloqueios de tabela e habilitar bloqueios HoBT em tabelas particionadas. Essa opção não é uma dica de bloqueio, mas pode ser usada para reduzir o escalonamento de bloqueios. Para obter mais informações, confira ALTER TABLE (Transact-SQL).

Personalizar bloqueios de um índice

O Mecanismo de Banco de Dados do SQL Server usa uma estratégia de bloqueio dinâmico que, na maioria dos casos, escolhe automaticamente a melhor granularidade de bloqueio para consultas. É recomendável substituir os níveis de bloqueio padrão que têm bloqueio de página e de linha ativado, a não ser que os padrões de acesso a tabela e ao índice sejam bem compreendidos e consistentes e haja um problema de contenção de recursos a ser resolvida. Substituir um nível de bloqueio pode impedir significativamente o acesso simultâneo a uma tabela ou índice. Por exemplo, a especificação de apenas bloqueios em nível de tabela em uma tabela grande que os usuários acessam excessivamente pode provocar gargalos, porque os usuários precisam esperar que o bloqueio em nível de tabela seja liberado para acessar a tabela.

Há alguns casos em que a desabilitação do bloqueio de página ou de linha poderá ser benéfico, se os padrões de acesso forem bem-entendidos e consistentes. Por exemplo, um aplicativo de banco de dados usa uma tabela de pesquisa que é atualizada semanalmente em um processo em lotes. Leitores simultâneos acessam a tabela com um bloqueio compartilhado (S) e a atualização em lotes semanal acessa a tabela com um bloqueio exclusivo (X). A desativação do bloqueio de página e de linha na tabela reduz a sobrecarga do bloqueio durante a semana permitindo que os leitores acessem a tabela simultaneamente por meio de bloqueios de tabelas compartilhado. Quando o trabalho em lotes é executado, ele pode concluir a atualização de maneira eficiente porque obtém um bloqueio de tabela exclusivo.

A desativação do bloqueio de página e de linha pode ou não ser aceitável porque a atualização semanal em lotes bloqueia o acesso dos leitores simultâneos à tabela enquanto a atualização está em execução. Se o trabalho em lotes só alterar algumas linhas ou páginas, você poderá alterar o nível de bloqueio para permitir bloqueio em nível de linha ou de página, o que permite que outras seções leiam a tabela sem bloqueio. Se o trabalho em lotes tiver um grande número de atualizações, obter um bloqueio exclusivo na tabela poderá ser a melhor maneira de garantir que o trabalho em lotes seja concluído de maneira eficiente.

Ocasionalmente um deadlock ocorre quando duas operações concorrentes adquirirem bloqueios de linha na mesma tabela e então bloqueiam a página porque ambas precisam bloquear a página. A desabilitação de bloqueios de linha força uma operação a esperar, evitando o deadlock. Para saber mais sobre deadlocks, consulte o Guia sobre deadlocks.

A granularidade do bloqueio usada em um índice pode ser definida usando as instruções CREATE INDEX e ALTER INDEX. As configurações de bloqueio se aplicam a páginas de índice e a páginas de tabela. Além disso, as instruções CREATE TABLE e ALTER TABLE podem ser usadas para definir a granularidade do bloqueio nas restrições PRIMARY KEY e UNIQUE. Para compatibilidade com versões anteriores, o procedimento armazenado do sistema de sp_indexoption também pode definir a granularidade. Para exibir a opção atual de bloqueio para um determinado índice, use a função INDEXPROPERTY. Podem não ser permitidos bloqueios no nível de página, no nível de linha ou uma combinação de bloqueios no nível de página e no nível de linha, para um determinado índice.

Bloqueios não permitidos	Índice acessado por
Nível da página	Bloqueios no nível de linha e no nível de tabela
Nível de linha	Bloqueios no nível de página e no nível de tabela
Nível de página e nível de linha	Bloqueio no nível de tabela

Informações avançadas sobre transações

Aninhar transações

Transações explícitas podem ser aninhadas. Isso serve basicamente para dar suporte a transações em procedimentos armazenados que possam ser chamados de um processo já presente em uma transação ou de processos que não tenham nenhuma transação ativa.

O exemplo a seguir mostra o uso planejado de transações aninhadas. O procedimento TransProc obriga sua transação independentemente do modo de transação de qualquer processo que a execute. Se TransProc for chamado quando uma transação estiver ativa, a transação aninhada em TransProc será amplamente ignorada e suas instruções INSERT serão confirmadas ou revertidas com base na ação final adquirida para a transação externa. Se TransProc for executado por um processo que não tenha uma transação pendente, a COMMIT TRANSACTION ao término do procedimento confirmará efetivamente as instruções INSERT.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
               Colb CHAR(3) NOT NULL);
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are
   still in the table. This indicates that the commit
   of the inner transaction from the first EXECUTE statement of
   TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;
GO

O commit de transações internas é ignorado pelo Mecanismo de Banco de Dados do SQL Server. A transação é confirmada ou revertida com base na ação adquirida no final da transação mais externa. Se a transação externa for confirmada, as transações aninhadas internas também serão confirmadas. Se a transação externa for revertida, então todas as transações internas também serão revertidas, sem considerar se as transações internas estavam individualmente confirmadas ou não.

Cada chamada para COMMIT TRANSACTION ou COMMIT WORK aplica-se ao último BEGIN TRANSACTION executado. Se as instruções BEGIN TRANSACTION estiverem aninhadas, então uma instrução COMMIT só se aplicará à última transação aninhada, que é a transação mais interna. Mesmo que uma instrução COMMIT TRANSACTION transaction_name em uma transação aninhada faça referência ao nome da transação externa, o commit se aplica somente à transação mais interna.

Não é permitido que o parâmetro transaction_name de uma instrução ROLLBACK TRANSACTION refira-se às transações internas de um conjunto de transações aninhadas nomeadas. transaction_name pode se referir apenas ao nome da transação mais externa. Se uma instrução de ROLLBACK TRANSACTION transaction_name que usa o nome da transação externa for executada em qualquer nível de um conjunto de transações aninhadas, todas as transações aninhadas serão revertidas. Se uma instrução ROLLBACK WORK ou ROLLBACK TRANSACTION sem um parâmetro transaction_name for executada em qualquer nível de um conjunto de transações aninhadas, todas as transações aninhadas serão revertidas, inclusive a transação externa.

A função @@TRANCOUNT registra o nível de aninhamento da atual da transação. Cada instrução BEGIN TRANSACTION incrementa @@TRANCOUNT em um. Cada instrução COMMIT TRANSACTION ou COMMIT WORK diminui @@TRANCOUNT em um. Uma instrução ROLLBACK WORK ou ROLLBACK TRANSACTION que não tem um nome de transação reverte todas as transações aninhadas e diminui @@TRANCOUNT para 0. Uma instrução ROLLBACK TRANSACTION que usa o nome de transação da transação externa em um conjunto de transações aninhadas reverte todas as transações aninhadas e diminui @@TRANCOUNT para 0. Quando não tiver certeza de que já está em uma transação, use SELECT @@TRANCOUNT para determinar se é um ou mais. Se @@TRANCOUNT for 0, você não estará em uma transação.

Usar sessões vinculadas

As sessões associadas facilitam a coordenação de ações em várias sessões no mesmo servidor. As sessões associadas permitem que duas ou mais sessões compartilhem a mesma transação e bloqueios e trabalhem nos mesmos dados sem conflitos de bloqueio. Essas seções podem ser criadas a partir de várias sessões dentro do mesmo aplicativo ou de vários aplicativos com sessões separadas.

Para participar de uma sessão vinculada, uma sessão chama sp_getbindtoken ou srv_getbindtoken (por meio do Open Data Services) para obter um token de associação. Um token de ligação é uma cadeia de caracteres que identifica exclusivamente cada transação associada. O token de ligação é enviado às outras sessões a serem associadas à sessão atual. As outras sessões se vinculam à transação chamando sp_bindsession, usando o token de associação recebido da primeira sessão.

Observação

Uma sessão deve ter uma transação de usuário ativa para sp_getbindtoken ou srv_getbindtoken ser bem-sucedido.

Os tokens de ligação devem ser transmitidos a partir do código do aplicativo que faz a primeira sessão para o código de aplicativo que associa subsequentemente suas sessões à primeira sessão. Não há nenhuma instrução Transact-SQL ou função de API que um aplicativo possa usar para obter o token de associação de uma transação iniciada por outro processo. Alguns dos métodos que podem ser usados para transmitir um token de ligação incluem o seguinte:

• Se todas as sessões forem iniciadas do mesmo processo de aplicativo, os tokens de ligação poderão ser armazenados na memória global ou passados em funções como um parâmetro.

• Se as sessões forem feitas a partir de processos de aplicativo separados, os tokens de ligação poderão ser transmitidos usando a comunicação entre processos (IPC), como uma chamada de procedimento remoto (RPC) ou troca dinâmica de dados (DDE).

• Os tokens de associação podem ser armazenados em uma tabela em uma instância do Mecanismo de Banco de Dados do SQL Server que pode ser lida por processos que desejam se associar à primeira sessão.

Somente uma sessão em um conjunto de sessões associadas pode estar ativa a qualquer momento. Se uma sessão estiver executando uma instrução na instância ou tiver resultados pendentes da instância, nenhuma outra sessão associada a ela poderá acessar a instância até que a sessão atual termine o processamento ou cancele a instrução atual. Se a instância estiver ocupada processando uma instrução de outras sessões associadas, ocorrerá um erro indicando que o espaço de transação está em uso e a sessão deverá tentar novamente posteriormente.

Quando você associa as sessões, cada sessão retém sua configuração de nível de isolamento. O uso de SET TRANSACTION ISOLATION LEVEL para alterar a configuração de nível de isolamento de uma sessão não afeta a configuração de nenhuma outra sessão associada a ela.

Tipos de sessões associadas

Os dois tipos de sessões associadas são local e distribuído.

• Sessão associada local Permite que sessões associadas compartilhem o espaço de transação de uma única transação em uma única instância do Mecanismo de Banco de Dados do SQL Server.

• Sessão associada distribuída Permite que as sessões associadas compartilhem a mesma transação em duas ou mais instâncias até que toda a transação seja confirmada ou revertida usando o MS DTC (Coordenador de Transações Distribuídas da Microsoft).

As sessões associadas distribuídas não são identificadas por um token de ligação de cadeia de caracteres; elas são identificadas por meio de números de identificação da transação distribuída. Se uma sessão associada estiver envolvida em uma transação local e executar uma RPC em um servidor remoto com SET REMOTE_PROC_TRANSACTIONS ON, a transação associada local será promovida automaticamente a uma transação associada distribuída por MS DTC e uma sessão MS DTC será iniciada.

Quando usar sessões associadas

Nas versões anteriores do SQL Server, as sessões associadas eram usadas principalmente no desenvolvimento de procedimentos armazenados estendidos que precisam executar instruções Transact-SQL em nome do processo que as chama. A passagem do processo de chamada por um token de ligação como um parâmetro do procedimento armazenado estendido permite que o procedimento seja associado ao espaço de transação do processo de chamada, integrando o procedimento armazenado estendido ao processo de chamada.

No Mecanismo de Banco de Dados do SQL Server, os procedimentos armazenados escritos usando CLR são mais seguros, escalonáveis e estáveis do que os procedimentos armazenados estendidos. Os procedimentos armazenados CLR usam o objeto SqlContext para unir o contexto da sessão de chamada, e não sp_bindsession.

As sessões associadas podem ser usadas para desenvolver aplicativos de três camadas nos quais a lógica empresarial está incorporada em programas separados que trabalham cooperativamente em uma única transação empresarial. Esses programas devem ser codificados para coordenar seu acesso cuidadosamente a um banco de dados. Como as duas sessões compartilham os mesmos bloqueios, os dois programas não devem tentar modificar os mesmos dados ao mesmo tempo. Em qualquer point-in-time, apenas uma sessão pode trabalhar como parte da transação; não pode haver execução paralela. A transação pode ser alternada apenas entre sessões em pontos bem definidos, como quando todas as instruções DML forem concluídas e seus resultados forem recuperados.

Transações eficientes em termos de código

É importante manter as transações tão curtas quanto possível. Quando uma transação é iniciada, um DBMS (Sistema de administração de banco de dados), deve manter muitos recursos, até o término da transação, para proteger as propriedades (ACID) de atomicidade, consistência, isolamento e durabilidade da transação. Se os dados forem modificados, as linhas modificadas devem ser protegidas com bloqueios exclusivos que evitem a leitura das linhas por qualquer outra transação, e os bloqueios exclusivos devem ser mantidos até que a transação seja confirmada ou revertida. Dependendo das configurações de nível de isolamento da transação, as instruções SELECT podem obter bloqueios que devem ser mantidos até a transação ser confirmada ou revertida. Especialmente em sistemas com muitos usuários, as transações devem ser mantidas tão curtas quanto possível para reduzir a contenção de bloqueios de recursos em conexões simultâneas. Transações longas e ineficazes podem não causar problemas com um número pequeno de usuários, mas são intoleráveis em um sistema com milhares de usuários. A partir do SQL Server 2014 (12.x), o SQL Server é compatível com transações duráveis atrasadas. As transações duráveis atrasadas não garantem a durabilidade. Para obter mais informações, veja Controlar a durabilidade da transação.

Diretrizes de código

Estas são diretrizes para codificar transações eficazes:

• Não solicite entradas de usuários durante a transação. Obtenha todas as entradas necessárias da parte dos usuários antes do início de uma transação. Sendo necessária uma entrada adicional de usuário durante uma transação, reverta a transação atual e reinicie a transação depois que a entrada do usuário for fornecida. Mesmo que os usuários respondam imediatamente, a reação humana é muito mais lenta que a velocidade do computador. Todos os recursos mantidos pela transação são retidos por um tempo extremamente longo, criando potencial para causar problemas de bloqueio. Se os usuários não responderem, a transação permanecerá ativa, bloqueando recursos críticos até que eles respondam, o que pode não acontecer por vários minutos ou até mesmo horas.

• Não abra uma transação enquanto estiver navegando pelos dados, se possível. As transações não devem ser iniciadas até que toda a análise preliminar de dados tenha terminado.

• Mantenha a transação tão curta quanto possível. Depois de saber quais são as modificações que precisam ser feitas, inicie uma transação, execute as instruções de modificação e, em seguida, confirme ou reverta imediatamente. Só abra a transação quando for necessário.

• Para reduzir o bloqueio, considere usar um nível de isolamento de linha baseado em controle de versão de linha para consultas somente leitura.

• Utilize bem os níveis de isolamento de transação inferiores. Muitos aplicativos podem ser prontamente codificados para usar um nível de isolamento de transação de leitura confirmada. Nem todas as transações requerem nível de isolamento de transação serializável.

• Utilize bem as opções inferiores de simultaneidade de cursor, como opções de simultaneidade otimista. Em um sistema com pouca probabilidade de atualizações simultâneas, a sobrecarga de lidar com um erro ocasional quando "alguém altera seus dados depois que você os leu" pode ser muito inferior à sobrecarga de sempre bloquear linhas à medida que são lidas.

• Acesse a menor quantidade de dados possível enquanto estiver em uma transação. Isso reduz o número de linhas bloqueadas, reduzindo, portanto, a contenção entre transações.

• Sempre que possível, evite dicas de bloqueio pessimistas como o holdlock sempre. Dicas como HOLDLOCK ou o nível de isolamento SERIALIZABLE podem fazer com que os processos tenham que aguardar mesmo em bloqueios compartilhados e reduz a simultaneidade

• Evite usar Transações implícitas quando possíveis Transações implícitas puderem apresentar um comportamento imprevisível devido à natureza delas. Confira Transações implícitas e problemas de simultaneidade

• Crie índices com um fator de preenchimento reduzido. Diminuir o fator de preenchimento pode ajudar você a impedir ou diminuir a fragmentação de páginas de índice e, portanto, reduzir os tempos de busca de índice, especialmente quando recuperados do disco. Para visualizar informações de fragmentação dos dados e índices de uma tabela ou exibição, você pode usar sys.dm_db_index_physical_stats.

Transações implícitas e como evitar problemas de simultaneidade e de recurso

Para impedir problemas de simultaneidade e de recurso, gerencie cuidadosamente as transações implícitas. Ao usar transações implícitas, a próxima instrução Transact-SQL após COMMIT ou ROLLBACK inicia automaticamente uma nova transação. Isso pode fazer com que uma nova transação seja aberta enquanto o aplicativo navega pelos dados, ou até mesmo quando solicita entradas da parte do usuário. Depois de completar a última transação necessária à proteção contra modificações de dados, desative as transações implícitas até que a transação seja novamente necessária para proteger as modificações de dados. Esse processo permite que o Mecanismo de Banco de Dados do SQL Server use o modo de confirmação automática enquanto o aplicativo estiver navegando nos dados e obtendo informações do usuário.

Além disso, quando o nível de isolamento do instantâneo estiver habilitado, embora uma nova transação não vá reter bloqueios, uma transação de execução longa impedirá a remoção de versões antigas de tempdb.

Gerenciar transações de longa duração

Uma transação de execução prolongada é uma transação ativa que não foi confirmada nem revertida em tempo hábil. Por exemplo, se o início e o término de uma transação forem controlados pelo usuário, uma causa típica de uma transação de longa execução será um usuário iniciar uma transação e sair enquanto a transação espera por uma resposta dele.

Uma transação de execução longa pode causar sérios problemas para um banco de dados, como:

• Se uma instância do servidor for desligada depois de uma transação ativa ter feito muitas modificações não confirmadas, a fase de recuperação do reinício subsequente poderá levar muito mais tempo do que o especificado pela opção de configuração do servidor intervalo de recuperação ou pela opção ALTER DATABASE ... SET TARGET_RECOVERY_TIME. Essas opções controlam a frequência de pontos de verificação ativos e indiretos, respectivamente. Para obter mais informações sobre os tipos de pontos de verificação, confira Pontos de verificação do banco de dados (SQL Server).

• É importante ressaltar que, embora uma transação em espera possa gerar um log muito pequeno, ela reterá o truncamento de log indefinidamente, fazendo com que o log da transação aumente bastante e seja completamente preenchido. Se o log de transações for completamente preenchido, o banco de dados não fará mais atualizações. Para obter mais informações, confira Guia de arquitetura e gerenciamento do log de transações do SQL Server, Solucionar problemas de um log de transações completo (erro 9002 do SQL Server) e O log de transações.

Importante

No Banco de Dados SQL do Azure, as transações ociosas (transações que não foram gravadas no log de transações por seis horas) são encerradas automaticamente para liberar recursos.

Descobrindo transações de execução prolongada

Para procurar transações demoradas, use um dos seguintes:

• sys.dm_tran_database_transactions

  Essa exibição de gerenciamento dinâmico retorna informações sobre as transações no banco de dados. Para uma transação de longa duração, as colunas de interesse especial incluem a hora do primeiro registro de log (database_transaction_begin_time), o estado atual da transação (database_transaction_state) e o número de sequência de log (LSN) do registro inicial no log de transações (database_transaction_begin_lsn).

  Para obter mais informações, consulte sys.dm_tran_database_transactions (Transact-SQL).

• DBCC OPENTRAN

  Essa instrução permite identificar a ID do proprietário da transação, de modo que seja possível localizar potencialmente a origem da transação para um término mais ordenado (confirmar, em vez de revertê-la). Para obter mais informações, consulte DBCC OPENTRAN (Transact-SQL).

Interromper uma transação

Talvez seja necessário usar a instrução KILL. Use essa instrução com muito cuidado, porém, especialmente quando houver processos importantes em processamento. Para obter mais informações, confira KILL (Transact-SQL).

Deadlocks

Deadlocks são um tópico complexo relacionado a bloqueios, mas diferente de bloqueios.

• Para obter mais informações sobre deadlocks, incluindo monitoramento, diagnóstico e amostras, confira o Guia de deadlocks.
• Para obter mais informações sobre deadlocks específicos do Banco de Dados SQL do Azure, consulte Analisar e evitar deadlocks no Banco de Dados SQL do Azure.

Conteúdo relacionado

• Sobrecarga do controle de versão de linha
• Visão geral de eventos estendidos
• sys.dm_tran_locks (Transact-SQL)
• Exibições de gerenciamento dinâmico do sistema
• Funções e exibições de gerenciamento dinâmico relacionadas à transação (Transact-SQL)
• Entender e resolver problemas de bloqueio do SQL Server
• Entender e resolver problemas de bloqueio do Banco de Dados SQL do Azure