Partilhar via

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

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerida do Azure SQLAzure Synapse AnalyticsAnalytics Platform System (PDW)Banco de Dados SQL no Microsoft Fabric

Em qualquer banco de dados, a má gestão de transações muitas vezes leva a problemas de contenção e desempenho em sistemas que têm muitos usuários. À medida que o número de usuários que acessam os dados aumenta, torna-se importante ter aplicativos que usem as transações de forma eficiente. Este guia descreve os mecanismos de bloqueio e controle de versão de linha que o Mecanismo de Banco de Dados usa para garantir a integridade de cada transação e fornece informações sobre como os aplicativos podem controlar as transações de forma eficiente.

Observação

Bloqueio otimizado é uma funcionalidade do motor de base de dados introduzida em 2023 que reduz drasticamente a memória de bloqueio e o número de bloqueios necessários para escritas simultâneas. Este artigo é atualizado para descrever o comportamento do Mecanismo de Banco de Dados com e sem bloqueio otimizado.

O bloqueio otimizado introduz alterações significativas em algumas seções deste artigo, incluindo:

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

Uma transação é uma sequência de operações realizadas como uma única unidade lógica de trabalho. Uma unidade lógica de trabalho deve apresentar quatro propriedades, chamadas de propriedades de atomicidade, consistência, isolamento e durabilidade (ACID), para se qualificar 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 realizadas, 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, como índices de árvore B ou listas duplamente vinculadas, devem estar corretas no final da transação.

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Nos índices de armazenamento de linha, o Mecanismo de Base de Dados implementa uma árvore B+. Isso não se aplica a índices columnstore ou índices em tabelas com otimização de memória. Para obter mais informações, consulte o SQL Server e o guia de design de arquitetura e design de índice SQL do Azure.

Isolamento
As modificações feitas por transações simultâneas devem ser isoladas das modificações feitas por quaisquer outras transações simultâneas. Uma transação reconhece os dados no estado em que estava antes de outra transação simultânea modificá-la ou reconhece os dados após a conclusão da segunda transação, mas não reconhece um estado intermediário. Isso é conhecido como serializabilidade porque resulta na capacidade de recarregar os dados iniciais e reproduzir uma série de transações para acabar com os dados no mesmo estado em que estavam depois que as transações originais foram executadas.

Durabilidade
Após a conclusão de uma transação totalmente duradoura, os seus efeitos estão permanentemente instalados no sistema. As modificações persistem mesmo em caso de falha do sistema. O SQL Server 2014 (12.x) e posterior permitem transações duráveis atrasadas. As transações duráveis com atraso são confirmadas antes que o registo do log de transações seja persistido no disco. Para obter mais informações sobre a durabilidade da transação atrasada, consulte o artigo Control Transaction Durability.

Os aplicativos são responsáveis por iniciar e terminar transações em pontos que impõem a consistência lógica dos dados. O aplicativo deve definir a sequência de modificações de dados que deixam os dados em um estado consistente em relação às regras de negócios da organização. O aplicativo executa essas modificações em uma única transação para que o Mecanismo de Banco de Dados possa impor a integridade da transação.

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

  • Instalações de bloqueio que preservam o isolamento da transação.

  • Mecanismos de registo para garantir a durabilidade das transações. Para transações totalmente duráveis, o registro de log é gravado permanentemente em disco antes que a transação seja confirmada. Assim, mesmo que o hardware do servidor, o sistema operacional ou a instância do próprio Mecanismo de Banco de Dados falhe, a instância usa os logs de transações na reinicialização para reverter automaticamente quaisquer transações incompletas até o ponto da falha do sistema. Transações duráveis atrasadas são confirmadas antes que o registro do log de transações seja gravado no disco. Essas transações podem ser perdidas se houver uma falha do sistema antes que o registro de log seja gravado no disco. Para obter mais informações sobre a durabilidade de transações adiadas, consulte o artigo Control Transaction Durability.

  • Recursos de gerenciamento de transações que impõem atomicidade e consistência de transações. Depois que uma transação for iniciada, ela deve ser concluída com êxito (confirmada) ou o Mecanismo de Banco de Dados desfaz todas as modificações de dados feitas pela transação desde o início da transação. Essa operação é conhecida como reverter uma transação porque retorna os dados ao estado em que estavam antes dessas alterações.

Controlo de transações

Os aplicativos controlam as transações principalmente especificando quando uma transação começa e termina. Isso pode ser especificado usando instruções Transact-SQL ou funções de interface de programação de aplicativo (API) de banco de dados. O sistema também deve ser capaz de lidar corretamente com erros que encerram uma transação antes que ela seja concluída. Para obter mais informações, consulte Transactions, Performing Transactions in ODBCe Transactions in SQL Server Native Client.

Por padrão, as transações são gerenciadas no nível da 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 a transação termine. No entanto, em uma sessão MARS (conjunto de resultados ativos múltiplos), uma transação explícita ou implícita Transact-SQL torna-se uma transação com escopo em lote que é gerenciada no nível do lote. Quando o lote for concluído, se a transação com escopo em lote não for confirmada ou revertida, ela será revertida automaticamente pelo Mecanismo de Banco de Dados. Para obter mais informações, consulte Usando vários conjuntos de resultados ativos (MARS).

Iniciar transações

Usando funções de API e instruções Transact-SQL, você pode iniciar transações 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 TRANSACTIONou ROLLBACK WORK Transact-SQL. Quando a transação termina, a conexão retorna ao modo de transação em que estava antes da transação explícita ser iniciada, que pode ser o modo implícito ou de confirmação automática.

Você pode usar todas as instruções Transact-SQL em uma transação explícita, exceto as seguintes instruções:

  • 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 definir opções de 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 usado dentro de uma transação explícita. No entanto, UPDATE STATISTICS compromete-se independentemente da transação anexa e não pode ser revertida.

de transações de confirmação automática

O modo de confirmação automática é o modo de gerenciamento de transações padrão do Mecanismo de Banco de Dados. Todas as declarações Transact-SQL são confirmadas ou revertidas quando concluem. Se uma declaração for concluída com êxito, ela será confirmada; se encontrar algum erro, ele será revertido. Uma conexão com uma instância do Mecanismo de Banco de Dados opera no modo de confirmação automática sempre que esse modo padrão não foi substituído por transações explícitas ou implícitas. O modo de confirmação automática também é o modo padrão para SqlClient, ADO, OLE DB e ODBC.

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 inicia automaticamente uma nova transação depois que a transação atual é confirmada ou revertida. Você não faz nada para delinear o início de uma transação; você só confirma ou reverte cada transação. 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, consulte Método setAutoCommit (SQLServerConnection).

Depois que o modo de transação implícita é habilitado para uma conexão, a instância do Mecanismo de Banco de Dados inicia automaticamente uma transação quando executa qualquer uma destas instruções pela primeira vez:

  • ALTER TABLE
  • CREATE
  • DELETE
  • DENY
  • DROP
  • FETCH
  • GRANT
  • INSERT
  • OPEN
  • REVOKE
  • SELECT
  • TRUNCATE
  • UPDATE

Transações com escopo em lote

Aplicável apenas a vários conjuntos de resultados ativos (MARS), uma transação explícita ou implícita Transact-SQL que começa em uma sessão MARS torna-se uma transação com escopo em lote. Uma transação com escopo em lote que não é confirmada ou revertida quando um lote é concluído é revertida automaticamente pelo Mecanismo de Banco de Dados.

Transações distribuídas

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

Uma transação dentro de uma única instância do Mecanismo de Banco de Dados que abrange dois ou mais bancos de dados é uma transação distribuída. A instância gerencia a transação distribuída internamente; para o usuário, ele opera como uma transação local.

No aplicativo, uma transação distribuída é gerenciada da mesma forma que 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 gerida de forma diferente pelo gestor de transações para minimizar o risco de que uma falha de rede possa resultar em alguns gestores de recursos efetuarem o comprometimento com sucesso, enquanto outros revertem a transação. Isto é conseguido através da gestão do processo de compromisso em duas fases (a fase de preparação e a fase de confirmação), que é conhecida como uma confirmação em duas fases.

  • Preparar a fase

    Quando o gerenciador de transações recebe uma solicitação de confirmação, ele envia um comando prepare para todos os gerentes de recursos envolvidos na transação. Em seguida, cada gestor de recursos faz tudo o que é necessário para tornar a transação durável, e todos os buffers de log de transações são gravados para o disco. À medida que cada gerente de recursos conclui a fase de preparação, ele retorna o sucesso ou falha da fase para o gerenciador de transações. O SQL Server 2014 (12.x) introduziu a durabilidade de transações atrasadas. Transações duráveis atrasadas são confirmadas antes que os buffers de log de transações em cada gerenciador de recursos sejam liberados para o disco. Para obter mais informações sobre a durabilidade de transações atrasadas, consulte o artigo Control Transaction Durability.

  • Fase de confirmação

    Se o gestor de transações receber preparações bem-sucedidas de todos os gestores de recursos, enviará comandos de confirmação para cada gestor de recursos. Os gerentes de recursos podem então concluir a confirmação. Se todos os gerentes de recursos relatarem uma confirmação bem-sucedida, o gerente de transações enviará uma notificação de êxito para o aplicativo. Se algum gerente de recursos relatar uma falha na preparação, o gerenciador de transações enviará um comando de reversão para cada gerente de recursos e indicará a falha da confirmação para o aplicativo.

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

Transações finais

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

  • Commit

    Se uma transação for bem-sucedida, execute-a. Uma declaração de COMMIT garante que todas as modificações da transação sejam feitas como parte permanente do banco de dados. Uma confirmação também libera recursos, como bloqueios, usados pela transação.

  • Reverter

    Se ocorrer um erro em uma transação, ou se o usuário decidir cancelar a transação, reverta a transação. Uma instrução ROLLBACK confirma todas as modificações feitas na transação, retornando os dados ao estado em que estavam no início da transação. A reversão também libera recursos detidos pela transação.

Observação

Em várias sessões de conjuntos de resultados ativos (MARS), uma transação explícita iniciada por meio de uma função de API não pode ser confirmada enquanto houver solicitações de execução pendentes. Qualquer tentativa de confirmar esse tipo de transação durante a execução de solicitações resulta em um erro.

Erros durante o processamento da transação

Se um erro impedir a conclusão bem-sucedida de uma transação, o Mecanismo de Banco de Dados reverterá automaticamente a transação e liberará todos os recursos mantidos pela transação. Se a conexão de rede do cliente com uma instância do Mecanismo de Banco de Dados for interrompida, todas as transações pendentes da conexão serão revertidas quando a rede notificar a instância da interrupção da conexão. Se o aplicativo cliente falhar ou se o computador cliente ficar inativo ou for reiniciado, isso também interromperá a conexão e a instância do Mecanismo de Banco de Dados reverterá todas as transações pendentes quando a rede notificá-lo da interrupção da conexão. Se o cliente se desconectar do Mecanismo de Banco de Dados, 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 é reverter apenas a instrução que gerou o erro. Você pode alterar esse comportamento usando a instrução SET XACT_ABORT ON. Depois que SET XACT_ABORT ON é executado, qualquer erro de instrução em tempo de execução causa uma reversão automática da transação atual. Erros de compilação, como erros de sintaxe, não são afetados por SET XACT_ABORT. Para obter mais informações, consulte SET XACT_ABORT (Transact-SQL).

Quando ocorrem erros, a ação apropriada (COMMIT ou ROLLBACK) deve ser incluída no código do aplicativo. Uma ferramenta eficaz para lidar com erros, incluindo aqueles em transações, é a construção Transact-SQL TRY...CATCH. Para obter mais informações com exemplos que incluem transações, consulte TRY... CATCH (Transact-SQL). A partir do SQL Server 2012 (11.x), você pode usar a instrução THROW para gerar uma exceção e transferir a execução para um bloco CATCH de uma construção TRY...CATCH. Para obter mais informações, consulte THROW (Transact-SQL).

Erros de compilação e tempo de execuçã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 reverteu um lote inteiro em vez de apenas uma instrução SQL. Isso 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 crie um plano de execução, portanto, nada no lote pode ser executado. Embora pareça que todas as instruções anteriores àquela que gerou o erro foram revertidas, o erro impediu que qualquer coisa no lote fosse executada. No exemplo a seguir, nenhuma das instruções INSERT no terceiro lote é executada devido a um erro de compilação. Parece que as duas primeiras instruções INSERT são revertidas quando nunca são 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 chave primária duplicada em tempo de execução. As primeiras duas instruções INSERT são concluídas com sucesso e confirmadas, portanto, permanecem mesmo após um erro 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 usa resolução de nomes adiada, onde os nomes de objeto são resolvidos em tempo de execução, não em tempo de compilação. No exemplo a seguir, as duas primeiras 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 de bloqueio e versionamento de linha

O Mecanismo de Banco de Dados 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 estão acessando dados ao mesmo tempo:

  • Bloqueio

    Cada transação solicita bloqueios de tipos diferentes nos recursos, como linhas, páginas ou tabelas, dos quais a transação é dependente. Os bloqueios impedem que outras transações modifiquem os recursos de uma forma que causaria problemas para a transação que solicita o bloqueio. Cada transação libera seus bloqueios quando não tem mais dependência dos recursos bloqueados.

  • de versionamento de linha

    Quando um nível de isolamento baseado em controle de versão de linha é usado, o Mecanismo de Banco de Dados mantém versões de cada linha modificada. Os aplicativos podem especificar que uma transação use as versões de linha para exibir os dados como existiam no início da transação ou instrução, em vez de proteger todas as leituras com bloqueios. Ao utilizar o versionamento de linhas, a probabilidade de que uma operação de leitura bloqueie outras transações é significativamente 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 bloqueio ou controle de versão de linha, as consultas executadas nesses 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 de transação, que definem o nível de proteção para a transação contra modificações feitas por outras transações. Sugestões ao nível da tabela podem ser especificadas para instruções Transact-SQL individuais para adaptar ainda mais o comportamento para atender aos requisitos da aplicação.

Gerenciar o acesso simultâneo a dados

Diz-se que os usuários que acessam um recurso ao mesmo tempo estão acessando o recurso simultaneamente. O acesso simultâneo a dados requer mecanismos para evitar efeitos adversos quando vários usuários tentam modificar recursos que outros usuários estão usando ativamente.

Efeitos de concorrência

Os usuários que modificam dados podem afetar outros usuários que estão lendo ou modificando os mesmos dados ao mesmo tempo. Diz-se que esses usuários estão acessando os dados simultaneamente. Se um banco de dados não tem controle de simultaneidade, os usuários podem ver os seguintes efeitos colaterais:

  • Atualizações perdidas

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

    Por exemplo, dois editores fazem uma cópia eletrônica do mesmo documento. Cada editor altera a cópia de forma independente e, em seguida, salva a cópia alterada, substituindo assim o documento original. O editor que salva a cópia alterada substitui por último 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 concluído e confirmado a transação.

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

    A dependência não confirmada ocorre quando uma segunda transação lê uma linha que está sendo atualizada por outra transação. A segunda transação está a ler dados que ainda não foram validados e que podem ser alterados pela transação que está a atualizar a linha.

    Por exemplo, um editor está fazendo alterações em um documento eletrônico. Durante as alterações, um segundo editor pega uma cópia do documento que inclui todas as alterações feitas até agora e distribui o documento para o público-alvo. O primeiro editor então decide que as alterações feitas até agora estão erradas e remove as edições e salva o documento. O documento distribuído contém edições que já não existem e devem 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 faça o salvamento final das modificações e confirme a transação.

  • Análise inconsistente (leitura irrepetível)

    A análise inconsistente ocorre 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 comprometida, na medida em que outra transação está alterando os dados que uma segunda transação está lendo. No entanto, numa análise inconsistente, os dados lidos pela segunda transação foram confirmados pela transação que efetuou a alteração. Além disso, a análise inconsistente envolve várias leituras (duas ou mais) da mesma linha, e em cada uma, as informações são alteradas por outra transação: daí o termo 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, ele é alterado. A leitura original não era repetível. Este problema poderia ser evitado se o escritor não pudesse alterar o documento até que o editor tenha terminado de lê-lo pela última vez.

  • Fantasma lê

    Uma leitura fantasma é uma situação que ocorre quando duas consultas idênticas são executadas e o conjunto de linhas retornadas pela segunda consulta é diferente. O exemplo a seguir mostra como isso pode ocorrer. Suponha que as duas transações estejam sendo executadas ao mesmo tempo. As duas SELECT instruções na primeira transação podem retornar resultados diferentes porque 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 duplas causadas pelas atualizações de linha

    • Falta uma linha atualizada ou ver a mesma linha atualizada várias vezes.

      As transações que estão a ser executadas no nível de READ UNCOMMITTED (ou instruções usando a dica de tabela NOLOCK) não estabelecem bloqueios partilhados a fim de impedir que outras transações modifiquem os dados lidos pela transação em curso. As transações que estão sendo executadas no nível READ COMMITTED emitem bloqueios compartilhados, mas os bloqueios de linha ou página são liberados depois que a linha é lida. Em ambos os casos, quando você estiver verificando um índice, se outro usuário alterar a coluna de chave de índice da linha durante a leitura, a linha poderá aparecer novamente se a alteração de chave mover a linha para uma posição à frente da verificação. Da mesma forma, a linha pode não ser lida se a alteração de chave mover a linha para uma posição no índice que você já leu. Para evitar isso, use a sugestão SERIALIZABLE ou HOLDLOCK, ou o versionamento de linha. Para obter mais informações, consulte Dicas de Tabela (Transact-SQL).

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

      Quando estiver a usar READ UNCOMMITTED, se a sua consulta ler linhas usando uma verificação de ordem de alocação (usando páginas do IAM), poderá perder linhas se outra transação estiver a causar uma divisão de páginas. Isso não ocorre quando você estiver usando o nível de isolamento READ COMMITTED.

Tipos de concorrência

Quando várias transações tentam modificar dados em um banco de dados ao mesmo tempo, um sistema de controles deve ser implementado para que as modificações feitas por uma transação não afetem negativamente as de outra transação. Isso é chamado de controle de simultaneidade.

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

  • Pessimista controlo de concorrência

    Um sistema de bloqueios impede que as transações modifiquem os dados de uma forma que afete outras transações. Depois que uma transação executa uma ação que faz com que um bloqueio seja aplicado, outras transações não podem executar ações que entrariam em conflito com o bloqueio até que o proprietário o libere. Isso é chamado de controle pessimista porque normalmente é usado em sistemas onde há alta contenção de dados, onde o custo de proteger dados com bloqueios é menor do que o custo de reverter transações se ocorrerem conflitos de simultaneidade.

  • Controle de Simultaneidade Otimista

    No controle de simultaneidade otimista, as transações não bloqueiam os dados quando os leem. No entanto, quando uma transação atualiza dados, o sistema verifica se outra transação alterou os dados depois que eles foram lidos. Se outra transação atualizou os dados, um erro é gerado. Normalmente, a transação que recebe o erro é revertida e recomeça. Isso é chamado de otimista porque normalmente é usado em sistemas onde há baixa contenção de dados e onde o custo de reverter ocasionalmente uma transação é menor do que o custo de bloquear dados quando lidos.

O Mecanismo de Banco de Dados oferece suporte a ambos os métodos de controle de simultaneidade. Os usuários especificam o tipo de controle de simultaneidade selecionando níveis de isolamento de transação para conexões ou opções de simultaneidade em cursores. Esses atributos podem ser definidos usando instruções Transact-SQL ou através das propriedades e atributos de 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

As transações especificam um nível de isolamento que define o grau em que uma transação deve ser isolada do recurso ou das modificações de dados feitas por outras transações. Os níveis de isolamento são descritos em termos de quais efeitos secundários de concorrência, como leituras sujas ou leituras fantasmas, são permitidos.

Controle de níveis de isolamento de transações:

  • Se os bloqueios são obtidos quando os dados são lidos e que tipo de bloqueios são pedidos.
  • 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 que existia no momento em que a instrução ou transação foi iniciada.
    • Lê a modificação de dados não confirmada.

Importante

A escolha de um nível de isolamento de transação não afeta os bloqueios adquiridos para proteger as modificações de dados. Uma transação sempre mantém um bloqueio exclusivo para executar a modificação de dados e mantém esse bloqueio até que a transação seja concluída, independentemente do nível de isolamento definido para essa transação. Para operações de leitura, os níveis de isolamento de transações definem principalmente o nível de proteção contra os efeitos de modificações feitas por outras transações.

Um nível de isolamento mais baixo aumenta a capacidade de muitas transações de acessar dados ao mesmo tempo, mas também aumenta o número de efeitos de simultaneidade (como leituras sujas ou atualizações perdidas) que as transações podem encontrar. Por outro lado, um nível de isolamento mais alto reduz os tipos de efeitos de simultaneidade que as transações podem encontrar, mas requer mais recursos do sistema e aumenta as chances de que uma transação bloqueie outra. A escolha do nível de isolamento apropriado depende do equilíbrio dos requisitos de integridade de dados do aplicativo em relação à sobrecarga de cada nível de isolamento. O nível de isolamento mais alto, SERIALIZABLE, garante que uma transação recupere exatamente os mesmos dados toda vez que repete uma operação de leitura, mas faz isso executando um nível de bloqueio que provavelmente afetará outras transações em sistemas multiusuário. O nível de isolamento mais baixo, READ UNCOMMITTED, pode recuperar dados que foram modificados, mas não confirmados por outras transações. Todos os efeitos colaterais de simultaneidade podem acontecer em READ UNCOMMITTED, mas não há bloqueio de leitura ou controle de versão, portanto, a sobrecarga é minimizada.

Níveis de isolamento do Mecanismo de Banco de Dados

A norma ISO define os seguintes níveis de isolamento, todos suportados pelo Mecanismo de Banco de Dados:

Nível de isolamento Definição
READ UNCOMMITTED O nível de isolamento mais baixo, em que as transações são isoladas apenas o suficiente para garantir que dados fisicamente inconsistentes não sejam lidos. Nesse nível, leituras sujas são permitidas, portanto, uma transação pode ver alterações ainda não confirmadas feitas por outras transações.
READ COMMITTED Permite que uma transação leia dados lidos anteriormente (não modificados) por outra transação sem esperar que a primeira transação seja concluída. O Mecanismo de Banco de Dados mantém bloqueios de gravação (adquiridos em dados selecionados) até o final da transação, mas os bloqueios de leitura são liberados assim que a operação de leitura é executada. Este é o nível padrão do Mecanismo de Banco de Dados.
REPEATABLE READ O Mecanismo de Banco de Dados mantém bloqueios de leitura e gravação adquiridos em dados selecionados até o final da transação. No entanto, como os bloqueios de intervalo não são gerenciados, leituras fantasmas podem ocorrer.
SERIALIZABLE O nível mais alto onde as transações são completamente isoladas umas das outras. O Mecanismo de Banco de Dados mantém bloqueios de leitura e gravação adquiridos em dados selecionados até o final da transação. Os bloqueios de intervalo são adquiridos quando uma operação SELECT usa uma cláusula range WHERE para evitar leituras fantasmas.

Nota: As operações DDL e as transações em tabelas replicadas podem falhar quando é solicitado o nível de isolamento SERIALIZABLE. Isso ocorre porque as consultas de replicação usam dicas que podem ser incompatíveis com o nível de isolamento SERIALIZABLE.

O motor de base de dados também oferece suporte a dois níveis adicionais de isolamento de transação que utilizam o versionamento de linha. Um é a implementação do nível de isolamento READ COMMITTED, e o outro é o nível de isolamento de transação SNAPSHOT.

Nível de isolamento de versionamento de linha Definição
Read Committed Snapshot (RCSI) Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT é definida ON, que é a configuração padrão no Banco de Dados SQL do Azure, o nível de isolamento READ COMMITTED usa o versionamento de linhas para fornecer consistência de leitura no nível da instrução. As operações de leitura requerem apenas bloqueios de estabilidade do esquema ao nível da tabela (Sch-S) e nenhum bloqueio de página ou linha. Ou seja, o Mecanismo de Banco de Dados usa o controle de versão de linha para apresentar cada instrução com um instantâneo transacionalmente consistente dos dados como existiam no início da instrução. Os bloqueios não são usados para proteger os dados de atualizações por outras transações. Uma função definida pelo usuário pode retornar dados que foram confirmados após o momento em que a instrução que contém o UDF começou.

Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT é definida OFF, que é a configuração padrão no SQL Server e na Instância Gerenciada SQL do Azure, o isolamento READ COMMITTED usa bloqueios compartilhados para impedir que outras transações modifiquem linhas durante a execução de uma operação de leitura pela transação atual. Os bloqueios compartilhados também impedem que a instrução leia 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.
SNAPSHOT O nível de isolamento de instantâneo usa o versionamento de linha para fornecer consistência de leitura ao nível de transação. As operações de leitura não adquirem bloqueios de página ou de linha; somente são adquiridos bloqueios de estabilidade do esquema (Sch-S) da tabela. Ao ler linhas modificadas por outra transação, as operações de leitura recuperam a versão da linha que existia quando a transação foi iniciada. Você só pode usar o isolamento SNAPSHOT quando a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION estiver definida como ON. Por padrão, essa opção é definida como OFF para bancos de dados de usuário no SQL Server e na Instância Gerenciada SQL do Azure e definida como ON para bancos de dados no Banco de Dados SQL do Azure.

Nota: O Mecanismo de Banco de Dados não oferece suporte ao controle de versão de metadados. Por esse motivo, há restrições sobre quais operações DDL podem ser executadas em uma transação explícita que está sendo executada sob isolamento de instantâneo. As seguintes instruções DDL não são permitidas sob isolamento de instantâneo após uma instrução BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEMEou qualquer instrução DDL do Common Language Runtime (CLR). Essas instruções são permitidas quando você está usando o isolamento de instantâneo em transações implícitas. Uma transação implícita, por definição, é uma única instrução que permite aplicar a semântica do isolamento de instantâneo, mesmo quando envolvem instruções DDL. Violações deste princípio podem causar 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 de concorrência ativados pelos diferentes níveis de isolamento.

Nível de isolamento Leitura suja Leitura irrepetível Fantasma
READ UNCOMMITTED Sim Sim Sim
READ COMMITTED Não Sim Sim
REPEATABLE READ Não Não Sim
SNAPSHOT Não Não Não
SERIALIZABLE Não Não Não

Para obter mais informações sobre os tipos específicos de bloqueio ou revisão de linhas controlados por cada nível de isolamento de transação, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Os níveis de isolamento de transações podem ser definidos usando Transact-SQL ou por meio de uma API de banco de dados.

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

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

ADO.NET
ADO.NET aplicativos 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, Serializableou 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_SNAPSHOTou 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_SESSIONDBPROP_SESS_AUTOCOMMITISOLEVELS como DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATEDou 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_READou SQL_TXN_SERIALIZABLE.

Para transações de instantâneo, 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 do Motor de Banco de Dados

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

Antes que uma transação adquira uma dependência do estado atual de uma parte de dados, por exemplo, lendo ou modificando os dados, ela deve se proteger dos efeitos de outra transação modificando os mesmos dados. A transação faz isso solicitando um bloqueio no pedaço de dados. Os bloqueios têm diferentes modos, como compartilhado (S) ou exclusivo (X). O modo de bloqueio define o nível de dependência que a transação tem sobre os dados. Nenhuma transação pode receber um bloqueio que entre em conflito com o modo de bloqueio já concedido nesses dados para outra transação. Se uma transação solicitar um modo de bloqueio que entre em conflito com um bloqueio que já tenha sido concedido nos mesmos dados, o Mecanismo de Banco de Dados pausará a transação solicitante até que o primeiro bloqueio seja liberado.

Quando uma transação modifica um dado, ele mantém certos bloqueios protegendo a modificação até o final da transação. Por quanto tempo uma transação mantém os bloqueios adquiridos para proteger as operações de leitura depende de se a configuração do nível de isolamento da transação está ativa e se o bloqueio otimizado () está habilitado ou não.

  • Quando o bloqueio optimizado não está activado, os bloqueios de linha e de página necessários para escritas são mantidos até ao final da transação.

  • Quando o bloqueio otimizado está ativado, apenas um bloqueio de ID de transação (TID) é mantido até o final da transação. No nível de isolamento padrão READ COMMITTED, as transações não manterão bloqueios de linha e página necessários para escritas até ao final da transação. Isso reduz a memória de bloqueio necessária e reduz a necessidade de escalonamento de bloqueio. Além disso, quando o bloqueio otimizado está ativado, o bloqueio após a qualificação (LAQ) na otimização avalia os predicados de uma consulta na versão confirmada mais recente da linha de dados sem adquirir um bloqueio, melhorando a concorrência.

Todos os bloqueios mantidos por uma transação são liberados quando a transação é concluída (quer ao confirmar, quer ao reverter).

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

Bloquear granularidade e hierarquias

O Mecanismo de Banco de Dados 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 bloqueia recursos automaticamente em um nível adequado à tarefa. O bloqueio em uma granularidade menor, como linhas, aumenta a simultaneidade, mas tem uma sobrecarga maior porque mais bloqueios devem ser mantidos se muitas linhas estiverem bloqueadas. O bloqueio em uma granularidade maior, como o bloqueio de tabelas, é custoso em termos de concorrência porque bloquear uma tabela inteira restringe o acesso a qualquer parte da tabela por outras transações. No entanto, ele tem uma sobrecarga menor porque menos fechaduras estão sendo mantidas.

O Mecanismo de Banco de Dados 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 hierarquia de bloqueio. Por exemplo, para proteger totalmente uma leitura de um índice, uma instância do Mecanismo de Banco de Dados pode ter que adquirir bloqueios compartilhados em linhas e bloqueios compartilhados de intenção nas páginas e na tabela.

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

Recurso Descrição
RID Um identificador de linha usado para bloquear uma única linha dentro de um montículo.
KEY Um bloqueio de linha para bloquear uma única linha em um índice B-tree.
PAGE Uma página de 8 kilobytes (KB) em um banco de dados, como páginas de dados ou índice.
EXTENT Um grupo contíguo de oito páginas, como páginas de dados ou de índice.
HoBT 1 Uma pilha ou árvore B. Um bloqueio que protege uma árvore B (índice) ou as páginas de dados de pilha em uma tabela que não tem um índice clusterizado.
TABLE 1 A tabela inteira, incluindo todos os dados e índices.
FILE Um arquivo de banco de dados.
APPLICATION Um recurso especificado pelo aplicativo.
METADATA Bloqueios de metadados.
ALLOCATION_UNIT Uma unidade de alocação.
DATABASE Toda a base de dados.
XACT 2 Bloqueio de ID de transação (TID) usado no de bloqueio otimizado. Para obter mais informações, consulte Bloqueio de ID de Transação (TID).

fechaduras de 1HoBT e TABLE podem ser afetadas pela opção LOCK_ESCALATION de ALTER TABLE.

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

Modos de bloqueio

O Mecanismo de Banco de Dados bloqueia 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 usados pelo Mecanismo de Banco de Dados.

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. Impede uma forma comum de impasse que ocorre quando várias sessões estão lendo, bloqueando e potencialmente atualizando recursos posteriormente.
Exclusivo (X) Usado para operações de modificação de dados, como INSERT, UPDATEou DELETE. Garante que várias atualizações não possam ser feitas no mesmo recurso ao mesmo tempo.
Intenção Usado para estabelecer uma hierarquia de bloqueio. Os tipos de bloqueio de intenção são: intenção partilhada (IS), intenção exclusiva (IX), e partilhada 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).
Atualização em massa (BU) Usado ao copiar dados em massa para uma tabela com a dica TABLOCK.
Gama de chaves Protege o intervalo de linhas lidas por uma consulta ao usar o nível de isolamento de transação SERIALIZABLE. Garante que outras transações não possam inserir linhas que seriam válidas para as consultas da transação SERIALIZABLE se as consultas fossem novamente executadas.

Fechaduras compartilhadas

Os bloqueios partilhados (S) permitem que transações simultâneas leiam um recurso sob controlo pessimista de concorrência. Nenhuma outra transação pode modificar os dados enquanto existirem bloqueios compartilhados (S) no recurso. Os bloqueios compartilhados (S) em um recurso são liberados assim que a operação de leitura é concluída, a menos que o nível de isolamento da transação esteja definido como REPEATABLE READ ou superior, ou uma dica de bloqueio seja usada para manter os bloqueios compartilhados (S) durante a transação.

Atualizar bloqueios

O Mecanismo de Banco de Dados coloca bloqueios de atualização (U) enquanto se prepara para executar uma atualização. U bloqueios são compatíveis com S bloqueios, mas apenas uma transação pode manter um bloqueio de U de cada vez num determinado recurso. Isso é fundamental - muitas transações simultâneas podem manter bloqueios S, mas apenas 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.

Os bloqueios de atualização (U) também podem ser obtidos por instruções que não sejam UPDATE, quando a dica de tabela UPDLOCK é especificada na instrução.

  • Alguns aplicativos usam o padrão "selecione uma linha e atualize a linha", onde a leitura e a gravação são explicitamente separadas dentro da transação. Nesse caso, se o nível de isolamento for REPEATABLE READ ou SERIALIZABLE, atualizações simultâneas podem causar um impasse, da seguinte maneira:

    Uma transação lê dados, adquire um bloqueio compartilhado (S) no recurso e, em seguida, modifica os dados, o que requer a conversão de bloqueio para um bloqueio exclusivo (X). Se duas transações adquirirem bloqueios partilhados (S) num recurso e, em seguida, tentarem atualizar dados simultaneamente, uma transação tenta converter o bloqueio para um bloqueio exclusivo (X). A conversão de bloqueio compartilhado para exclusivo deve aguardar porque o bloqueio exclusivo (X) para 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 a atualização. Como ambas as transações se estão a converter em bloqueios exclusivos (X) e cada uma delas está à espera que a outra transação liberte o seu bloqueio partilhado (S), ocorre um impasse.

    No nível de isolamento de READ COMMITTED padrão, os bloqueios de S são de curta duração, liberados assim que são usados. Embora o impasse descrito acima ainda seja possível, é muito menos provável com bloqueios de curta duração.

    Para evitar este tipo de impasse, as aplicações podem seguir um padrão "selecionar uma linha com a dica UPDLOCK, e em seguida, atualizar a linha".

  • Se a dica UPDLOCK for usada numa escrita quando o isolamento SNAPSHOT estiver em uso, a transação deverá ter acesso à versão mais recente da linha. Se a versão mais recente não estiver mais visível, é possível receber Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict. Para obter um exemplo, consulte Trabalhar com isolamento de instantâneo.

Fechaduras exclusivas

Bloqueios exclusivos (X) impedem o acesso a um recurso por transações simultâneas. Com um bloqueio exclusivo (X), nenhuma outra transação pode modificar os dados protegidos pelo bloqueio; as operações de leitura só podem ocorrer com o uso da sugestão de NOLOCK ou do nível de isolamento READ UNCOMMITTED.

As instruções de modificação de dados, como INSERT, UPDATEe DELETE combinam operações de leitura e modificação. A instrução primeiro executa operações de leitura para adquirir dados antes de executar as operações de modificação necessárias. As instruções de modificação de dados, portanto, normalmente solicitam bloqueios compartilhados e bloqueios exclusivos. Por exemplo, uma instrução UPDATE pode 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 tabela de junção, além de solicitar bloqueios exclusivos nas linhas atualizadas.

Bloqueios de intenção

O Mecanismo de Banco de Dados usa bloqueios de intenção para proteger a colocação de um bloqueio compartilhado (S) ou exclusivo (X) em um recurso inferior na hierarquia de bloqueio. Os bloqueios de intenção são chamados de "bloqueios de intenção" porque são adquiridos antes de um bloqueio no nível inferior e, portanto, sinalizam a intenção de colocar bloqueios em um nível mais baixo.

Os bloqueios de intenção servem dois propósitos:

  • Para evitar que outras transações modifiquem o recurso de nível superior de uma forma que invalidaria o bloqueio no nível inferior.
  • Para melhorar a eficiência do Mecanismo de Banco de Dados na deteção de conflitos de bloqueio no nível mais alto de granularidade.

Por exemplo, um bloqueio de intenção compartilhada é solicitado no nível da tabela antes que os bloqueios compartilhados (S) sejam solicitados em páginas ou linhas dentro dessa tabela. Definir um bloqueio de intenção no nível da tabela impede que outra transação adquira posteriormente um bloqueio exclusivo (X) na tabela que contém essa página. Os bloqueios de intenção melhoram o desempenho porque o Mecanismo de Banco de Dados examina os bloqueios de intenção somente no nível da tabela para determinar se uma transação pode adquirir com segurança um bloqueio nessa tabela. Isso elimina o requisito de examinar cada bloqueio de linha ou página na tabela para determinar se uma transação pode bloquear a tabela inteira.

Os bloqueios de intenção incluem intenção compartilhada (IS), intenção exclusiva (IX) e compartilhada com intenção exclusiva (SIX).

Modo de bloqueio Descrição
Intenção compartilhada (IS) Protege bloqueios compartilhados solicitados ou adquiridos em alguns (mas não todos) recursos inferiores na hierarquia.
Intenção exclusiva (IX) Protege bloqueios exclusivos solicitados ou adquiridos em alguns (mas não todos) recursos inferiores na hierarquia. IX é um superconjunto de ISe também protege a solicitação de bloqueios compartilhados em recursos de nível inferior.
Partilhado com intenção exclusiva (SIX) Protege bloqueios compartilhados solicitados ou adquiridos em todos os recursos inferiores na hierarquia e bloqueia bloqueios exclusivos de intenção em alguns (mas não todos) dos recursos de nível inferior. São permitidos bloqueios de IS simultâneos no recurso de nível superior. Por exemplo, adquirir um bloqueio SIX numa tabela também adquire bloqueios de intenção exclusivos nas páginas em modificação e bloqueios exclusivos sobre as linhas modificadas. Pode haver apenas um bloqueio de SIX por recurso de cada vez, impedindo atualizações ao recurso feitas por outras transações, embora outras transações possam ler recursos mais abaixo na hierarquia obtendo bloqueios de IS no nível da tabela.
Atualização de intenção (IU) Protege bloqueios de atualização solicitados ou adquiridos em todos os recursos inferiores na hierarquia. IU locks são usados apenas em recursos de página. IU bloqueios são convertidos em bloqueios de IX quando ocorre uma operação de atualização.
Atualização de intenção compartilhada (SIU) Uma combinação de bloqueios S e IU, devido à aquisição desses bloqueios separadamente e mantendo simultaneamente ambos os bloqueios. Por exemplo, uma transação executa uma consulta com a dica PAGLOCK e, em seguida, executa uma operação de atualização. A consulta com a sugestão PAGLOCK adquire o bloqueio S, e a operação de atualização adquire o bloqueio IU.
Intenção de atualização exclusiva (UIX) Uma combinação de fechaduras U e IX, como resultado da aquisição dessas fechaduras separadamente e mantendo ambas as fechaduras ao mesmo tempo.

Bloqueios de esquema

O Mecanismo de Banco de Dados usa bloqueios de modificação de esquema (Sch-M) durante uma operação DDL (linguagem de definição de dados de tabela), como adicionar uma coluna ou soltar uma tabela. Enquanto é mantido, o bloqueio de Sch-M impede o acesso simultâneo à tabela. Isso significa que o bloqueio Sch-M bloqueia todas as operações externas até que o bloqueio seja liberado.

Algumas operações de linguagem de manipulação de dados (DML), como truncamento de tabela, usam bloqueios de Sch-M para impedir o acesso às tabelas afetadas por operações simultâneas.

O Mecanismo de Banco de Dados usa bloqueios de estabilidade de esquema (Sch-S) ao compilar e executar consultas. Sch-S bloqueios não bloqueiam nenhum bloqueio transacional, incluindo bloqueios exclusivos (X). Portanto, outras transações, incluindo aquelas com bloqueios de X em uma tabela, continuam a ser executadas enquanto uma consulta está sendo compilada. No entanto, operações DDL simultâneas e operações DML simultâneas que adquirem bloqueios Sch-M são bloqueadas pelos bloqueios Sch-S.

Bloqueios de atualização em massa

Os bloqueios de atualização em massa (BU) permitem que vários threads carreguem dados em massa simultaneamente na mesma tabela, impedindo que outros processos que não estão carregando dados em massa acessem a tabela. O Mecanismo de Banco de Dados usa bloqueios de atualização em massa (BU) quando ambas as condições a seguir forem verdadeiras.

  • Use a instrução Transact-SQL BULK INSERT ou a função OPENROWSET(BULK) ou use um dos comandos da API de inserção em massa, como .NET SqlBulkCopy, OLEDB Fast Load APIs ou ODBC Bulk Copy APIs para copiar dados em massa para uma tabela.
  • A dica TABLOCK é especificada ou a opção de tabela table lock on bulk load é definida usando sp_tableoption.

Dica

Ao contrário da instrução BULK INSERT, que contém um bloqueio Bulk Update (BU) menos restritivo, INSERT INTO...SELECT com a dica TABLOCK mantém um bloqueio exclusivo de intenção (IX) na tabela. Isso significa que você não pode inserir linhas usando operações de inserção paralela.

Fechaduras de alcance de chave

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

Compatibilidade de fechadura

A compatibilidade de bloqueio controla se várias transações podem adquirir bloqueios sobre o mesmo recurso simultaneamente. Se um recurso já estiver bloqueado por outra transação, uma nova solicitação de bloqueio só poderá ser concedida se o modo do bloqueio solicitado for compatível com o modo do bloqueio existente. Se o modo do bloqueio solicitado não for compatível com o bloqueio existente, a transação que solicita o novo bloqueio aguarda que o bloqueio existente seja liberado ou que o intervalo de tempo limite de bloqueio expire. Por exemplo, nenhum modo de bloqueio é compatível com bloqueios exclusivos. Enquanto um bloqueio exclusivo (X) é mantido, nenhuma outra transação pode adquirir um bloqueio de qualquer tipo (compartilhado, atualizado ou exclusivo) nesse recurso até que o bloqueio exclusivo (X) seja liberado. Por outro lado, se um bloqueio compartilhado (S) tiver sido aplicado a um recurso, outras transações também podem adquirir um bloqueio compartilhado ou um bloqueio de atualização (U) nesse recurso, mesmo que a primeira transação não tenha sido 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
Intenção compartilhada (IS) Sim Sim Sim Sim Sim Não
Compartilhado (S) Sim Sim Sim Não Não Não
Atualização (U) Sim Sim Não Não Não Não
Intenção exclusiva (IX) Sim Não Não Sim Não Não
Partilhado com intenção exclusiva (SIX) Sim Não Não Não Não Não
Exclusivo (X) Não Não Não Não Não Não

Observação

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

Use a tabela a seguir para determinar a compatibilidade de todos os modos de bloqueio disponíveis no Mecanismo de Banco de Dados.

Diagrama mostrando uma matriz de conflitos de bloqueio e compatibilidade.

Chave Descrição
N Sem conflito
Eu Ilegal
C Conflito
Países Baixos Sem bloqueio
SCH-S Bloqueio de estabilidade do esquema
SCH-M Bloqueio de modificação de esquema
S Compartilhada
U Atualizar
X Exclusivo
É Intenção partilhada
UI Atualização de intenção
IX Intenção exclusiva
SIU Compartilhar atualizações sobre a intenção
SEIS Partilhe com intenção exclusiva
UIX Atualize com propósito exclusivo
BU Atualização em massa
RS-S Intervalo partilhado
RS-U Atualização de intervalo compartilhado
RI-N Inserir intervalo nulo
RI-S Inserir intervalo compartilhado
RI-U Inserir atualização de alcance
RI-X Inserir gama exclusiva
RX-S Gama exclusiva partilhada
RX-U Atualização exclusiva da gama
RX-X Gama exclusivamente exclusiva

Bloqueio da gama de chaves

Os bloqueios de intervalo de chaves protegem um intervalo de linhas implicitamente incluídas 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 SERIALIZABLE. O nível de isolamento SERIALIZABLE requer que qualquer consulta executada durante uma transação obtenha o mesmo conjunto de linhas sempre que for executada durante a transação. Um bloqueio de intervalo de chaves satisfaz este requisito, impedindo que outras transações insiram novas linhas cujas chaves se enquadrariam no intervalo de chaves lidas pela transação SERIALIZABLE.

O bloqueio do alcance das teclas evita leituras fantasmas. Ao proteger os intervalos de chaves entre linhas, ele também impede inserções fantasmas em um conjunto de registros acessados por uma transação.

Um bloqueio de intervalo de chaves é colocado num índice, especificando um valor de chave inicial e final. Este bloqueio impede qualquer tentativa de inserir, atualizar ou excluir linhas com um valor de chave que caia dentro do intervalo, uma vez que essas operações precisam primeiro adquirir um bloqueio no índice. Por exemplo, uma transação SERIALIZABLE pode emitir uma instrução SELECT que lê todas as linhas cujos valores de chave correspondem à condição BETWEEN 'AAA' AND 'CZZ'. Um bloqueio de intervalo de chaves nos valores de chave no intervalo de 'AAA' a 'CZZ' impede que outras transações insiram linhas com valores de chave em qualquer lugar desse intervalo, como 'ADG', 'BBD', ou 'CAL'.

Modos de bloqueio de alcance de teclas

Os bloqueios de intervalo de chaves incluem tanto um intervalo como uma linha, especificados no formato de intervalo-linha.

  • Range representa o modo de bloqueio que protege o intervalo entre duas entradas de índice consecutivas.
  • Na linha representa-se o modo de bloqueio que protege a entrada do índice.
  • O modo representa o modo de bloqueio combinado utilizado. Os modos de bloqueio de alcance-de-chave consistem em duas partes. O primeiro representa o tipo de bloqueio usado para bloquear o intervalo de índice (RangeT) e o segundo representa o tipo de bloqueio usado para bloquear uma chave específica (K). As duas partes estão conectadas com um hífen (-), como RangeT-K.
Gama Linha Modo Descrição
RangeS S RangeS-S Intervalo compartilhado, bloqueio de recursos compartilhados; SERIALIZABLE varredura de alcance.
RangeS U RangeS-U Intervalo compartilhado, bloqueio de recursos de atualização; SERIALIZABLE verificação de atualização.
RangeI Null RangeI-N Inserir intervalo, bloqueio de recurso nulo; usado para testar intervalos antes de inserir uma nova chave em um índice.
RangeX X RangeX-X Gama exclusiva, bloqueio de recursos exclusivo; usado ao atualizar uma chave em um intervalo.

Observação

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

Os modos de bloqueio de alcance 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 Não Sim Não Sim Não
Exclusivo (X) Não Não Não Não Não Sim Não
RangeS-S Sim Sim Não Sim Sim Não Não
RangeS-U Sim Não Não Sim Não Não Não
RangeI-N Sim Sim Sim Não Não Sim Não
RangeX-X Não Não Não Não Não Não Não

Bloqueios de conversão

Os bloqueios de conversão são criados quando um bloqueio de intervalo de chaves se sobrepõe 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 em diferentes circunstâncias complexas, às vezes durante a execução de processos simultâneos.

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

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

  • Consulta de varredura por intervalo
  • Obtenção singleton de linha inexistente
  • Eliminar operação
  • Inserir operação

Antes de poder ocorrer o bloqueio do alcance das chaves, devem ser satisfeitas as seguintes condições:

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

Exemplos

A tabela e o índice a seguir são usados como base para os exemplos de bloqueio de intervalo de chaves a seguir.

Um diagrama de uma amostra de um Btree.

Consulta de análise de intervalo

Para garantir que uma consulta de verificação de intervalo seja serializável, a mesma consulta deve retornar os mesmos resultados sempre que for executada na 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 tornam-se inserções-fantasma. Por exemplo, a consulta a seguir usa a tabela e o índice na ilustração anterior:

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

Os bloqueios de intervalo de chaves são colocados nas entradas de índice correspondentes ao intervalo de linhas em que o nome está entre os valores Adam e Dale, impedindo que novas linhas qualificadas na consulta anterior sejam adicionadas ou excluídas. Embora o primeiro nome neste intervalo seja Adam, o bloqueio de intervalo de teclas do modo RangeS-S nesta entrada de índice garante que nenhum novo nome começando com a letra A possa ser adicionado antes Adam, como Abigail. Da mesma forma, o bloqueio de faixa de teclas RangeS-S na entrada de índice para Dale garante que não seja possível adicionar novos nomes que comecem com a letra C após Carlos, como Clive.

Observação

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

Tentativa de obter dados inexistentes com um padrão singleton

Se uma consulta dentro de uma transação tentar selecionar uma linha que não existe, emitir a consulta em um ponto posterior dentro da mesma transação deverá retornar o mesmo resultado. Nenhuma outra transação pode ser autorizada a inserir essa linha inexistente. Por exemplo, dada esta consulta:

SELECT name
FROM mytable
WHERE name = 'Bill';

Um bloqueio de intervalo de chaves é colocado na entrada de índice correspondente ao intervalo de nomes de Ben a Bing porque o nome Bill seria inserido entre essas duas entradas de índice adjacentes. O bloqueio da gama de teclas do modo RangeS-S é colocado na entrada do índice Bing. Isso impede que qualquer outra transação insira valores, como Bill, entre as entradas de índice Ben e Bing.

Excluir operação sem bloqueio otimizado

Ao excluir uma linha dentro de uma transação, o intervalo em que a linha se enquadra não precisa ser bloqueado durante a transação que executa a operação de exclusão. Bloquear o valor da chave excluída até o final da transação é suficiente para manter a serializabilidade. Por exemplo, conforme esta declaração DELETE:

DELETE mytable
WHERE name = 'Bob';

Um cadeado exclusivo (X) é colocado na entrada do índice correspondente ao nome Bob. Outras transações podem inserir ou excluir valores antes ou depois da linha com o valor Bob que está sendo excluído. No entanto, qualquer transação que tente ler, inserir ou excluir linhas correspondentes ao 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 confirmado anteriormente.)

A exclusão de intervalo pode ser executada usando três modos básicos de bloqueio: linha, página ou tabela. A estratégia de bloqueio de linha, página ou tabela é decidida pelo Otimizador de Consulta ou pode ser especificada pelo usuário por meio de dicas do Otimizador de Consulta, como ROWLOCK, PAGLOCKou TABLOCK. Quando PAGLOCK ou TABLOCK é usado, o Mecanismo de Banco de Dados deslocaliza imediatamente uma página de índice se todas as linhas forem excluídas dessa página. Em contrapartida, quando ROWLOCK é usada, todas as linhas eliminadas são marcadas apenas como eliminadas; são removidas da página de índice posteriormente através de uma tarefa em segundo plano.

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

Ao excluir uma linha dentro de uma transação, os bloqueios de linha e página são adquiridos e liberados incrementalmente, e não mantidos durante a transação. Por exemplo, dada esta instrução DELETE:

DELETE mytable
WHERE name = 'Bob';

Um bloqueio TID é colocado em todas as linhas modificadas durante a transação. Um bloqueio é estabelecido no TID das linhas de índice correspondentes ao valor Bob. Com o bloqueio otimizado, os bloqueios de página e linha continuam a ser adquiridos para atualizações, mas cada bloqueio de página e linha é liberado assim que cada linha é atualizada. O bloqueio TID protege as linhas contra atualizações até que a transação seja concluída. Qualquer transação que tente ler, inserir ou excluir linhas com o valor Bob é 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 confirmado anteriormente.)

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

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

Ao inserir uma linha dentro de uma transação, o intervalo em que a linha se enquadra não precisa ser bloqueado durante a transação que executa a operação de inserção. Bloquear o valor da chave inserida até o final da transação é suficiente para manter a serializabilidade. Por exemplo, dada esta instrução INSERT:

INSERT mytable VALUES ('Dan');

O bloqueio de intervalo de teclas do modo RangeI-N é colocado na linha de índice correspondente ao nome David para testar o intervalo. Se o bloqueio for concedido, uma linha com o valor Dan é inserida e um bloqueio exclusivo (X) é colocado na linha inserida. O bloqueio de intervalo de teclas no modo RangeI-N é necessário apenas 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 depois da linha inserida com o valor Dan. No entanto, qualquer transação que tente ler, inserir ou excluir a linha com o valor Dan é bloqueada até que a transação de inserção seja confirmada ou revertida.

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

Ao inserir uma linha dentro de uma transação, o intervalo em que a linha se enquadra não precisa ser bloqueado durante a transação que executa a operação de inserção. Os bloqueios de linha e página raramente são adquiridos, apenas quando há uma reconstrução de índice online em andamento ou quando há transações SERIALIZABLE simultâneas. Se os bloqueios de linha e página forem adquiridos, eles serão liberados imediatamente e não serão mantidos durante toda a transação. Colocar um bloqueio TID exclusivo no valor da chave inserida até o final da transação é suficiente para manter a serializabilidade. Por exemplo, dada a seguinte declaração INSERT:

INSERT mytable VALUES ('Dan');

Com o bloqueio otimizado, um bloqueio de RangeI-N só é 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 teclas do modo RangeI-N é colocado na linha de índice correspondente ao nome David para testar o intervalo. Se o bloqueio for concedido, uma linha com o valor Dan é inserida e um bloqueio exclusivo (X) é colocado na linha inserida. O bloqueio de intervalo de teclas no modo RangeI-N é necessário apenas 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 depois da linha inserida com o valor Dan. No entanto, qualquer transação que tente ler, inserir ou excluir a linha com o valor Dan é bloqueada até que a transação de inserção seja confirmada ou revertida.

Escalonamento de bloqueio

A escalada de bloqueios é o processo de conversão de muitos bloqueios de granularidade fina em menos bloqueios de granularidade grossa, reduzindo a sobrecarga do sistema enquanto aumenta a probabilidade de contenção de concorrência.

O escalonamento de bloqueio se comporta de forma diferente, dependendo se de bloqueio otimizado está habilitado.

Escalonamento de bloqueios sem gestão de bloqueios otimizada

À medida que o Mecanismo de Banco de Dados adquire bloqueios de baixo nível, ele também coloca bloqueios de intenção 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 coloca um bloqueio de intenção nas páginas que contêm as linhas ou chaves.
  • Ao bloquear páginas, o Mecanismo de Banco de Dados coloca um bloqueio de intenção nos objetos de nível superior que contêm as páginas. Além do bloqueio de intenção no objeto, os bloqueios de página de intenção são solicitados nos seguintes objetos:
    • Páginas ao nível de folha de índices não agrupados
    • Páginas de dados de índices agrupados
    • Páginas de dados do heap

O Motor de Base de Dados pode fazer bloqueio tanto de linha quanto de página para a mesma instrução, a fim de minimizar o número de bloqueios e reduzir a probabilidade de ser necessário o escalonamento de bloqueios. Por exemplo, o Mecanismo de Banco de Dados pode colocar bloqueios de página num índice não agrupado (se chaves contíguas suficientes no nó de índice forem selecionadas para satisfazer a consulta) e bloqueios de linha no índice ou heap agrupado.

Para escalar bloqueios, o Mecanismo de Banco de Dados tenta alterar o bloqueio de intenção na tabela para o respectivo bloqueio completo, por exemplo, alterando um bloqueio de intenção exclusiva (IX) para um bloqueio exclusivo (X) ou um bloqueio de intenção partilhada (IS) para um bloqueio partilhado (S). Se a tentativa de escalonamento de bloqueio for bem-sucedida e o bloqueio completo da tabela for adquirido, então todos os bloqueios HoBT, de página (PAGE) ou de nível de linha (RID, KEY) mantidos pela transação no heap ou índice serão liberados. Se o bloqueio total não puder ser adquirido, nenhum escalonamento de bloqueio ocorrerá nesse momento e o Mecanismo de Banco de Dados continuará a adquirir bloqueios de linha, chave ou página.

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

Observação

Os bloqueios de nível HoBT geralmente aumentam a simultaneidade, mas introduzem o potencial 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, o bloqueio por granularidade TABLE pode ter um desempenho melhor.

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

Cada evento de escalonamento opera principalmente no nível de uma única declaração de Transact-SQL. Quando o evento começa, o Mecanismo de Banco de Dados tenta aumentar todos os bloqueios pertencentes à transação atual em qualquer uma das tabelas referenciadas pela instrução ativa, desde que satisfaça os requisitos de limite de escalonamento. Se o evento de escalonamento começar antes que a instrução tenha acessado uma tabela, nenhuma tentativa será feita para escalar os bloqueios nessa tabela. Se o escalonamento de bloqueio for bem-sucedido, todos os bloqueios que a transação adquiriu numa instrução anterior e que ainda estão mantidos no momento em que o evento começa serão aumentados, desde que a tabela seja referenciada pela instrução atual e esteja incluída no evento de escalonamento.

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

  • Inicia uma transação.
  • Atualizações TableA. Isso gera bloqueios de linha exclusivos em TableA que são mantidos até que a transação seja concluída.
  • Atualizações TableB. Isso gera bloqueios de linha exclusivos em TableB que são mantidos até que a transação seja concluída.
  • Executa um SELECT que une TableA a TableC. O plano de execução de consulta pede que as linhas sejam recuperadas de TableA antes que as linhas sejam recuperadas de TableC.
  • A instrução SELECT aciona o escalonamento de bloqueio enquanto está a recuperar linhas do TableA e antes de aceder TableC.

Se o escalonamento de bloqueio for bem-sucedido, somente os bloqueios mantidos pela sessão em TableA serão elevados. Isso inclui os bloqueios compartilhados da declaração SELECT e os bloqueios exclusivos da declaração UPDATE anterior. Embora apenas os bloqueios que a sessão adquiriu em TableA para a instrução SELECT sejam considerados para determinar se o escalonamento de bloqueio deve ser feito, uma vez que o escalonamento é bem-sucedido, todos os bloqueios mantidos pela sessão em TableA são escalados para um bloqueio exclusivo na tabela, e todos os outros bloqueios de menor granularidade em TableA, incluindo bloqueios de intenção, são liberados.

Nenhuma tentativa é feita para aumentar os bloqueios em TableB porque não houve referência ativa a TableB na declaração SELECT. Da mesma forma, não se faz nenhuma tentativa de escalar os bloqueios em TableC, que não são escalados porque ainda não tinham sido acedidos quando a escalada ocorreu.

Escalonamento de bloqueio com bloqueio otimizado

O bloqueio otimizado ajuda a reduzir a memória de bloqueio, pois muito poucos bloqueios são mantidos durante a transação. À medida que o Mecanismo de Banco de Dados adquire bloqueios de linha e página, o escalonamento de bloqueio pode ocorrer de forma semelhante, mas com muito menos frequência. O bloqueio otimizado normalmente consegue evitar escalonamentos de bloqueio, 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 da Base de Dados libera os bloqueios de linha e página assim que a linha é modificada. Não são mantidos bloqueios de linhas e páginas durante a transação, exceto pelo bloqueio de um único ID de Transação (TID). Isso reduz a probabilidade de escalonamento de bloqueio.

Bloquear limites de escalonamento

O escalonamento de bloqueio é acionado quando o escalonamento de bloqueio não está desativado na tabela usando a opção ALTER TABLE SET LOCK_ESCALATION e quando alguma das seguintes condições se verifica:

  • Um único enunciado Transact-SQL adquire pelo menos 5.000 bloqueios numa única tabela ou índice não particionada.
  • 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 é 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 configuração.

Se os bloqueios não puderem ser escalados devido a conflitos de bloqueio, o Mecanismo de Banco de Dados acionará periodicamente o escalonamento de bloqueio 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 bloqueio 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 bloqueio é 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 bloqueio não será acionado se uma instrução adquirir 3.000 bloqueios em um índice e 3.000 bloqueios em outro índice da mesma tabela. Da mesma forma, o escalonamento de bloqueio não é acionado se uma instrução tiver uma auto-junção em uma tabela, e cada referência à tabela adquire apenas 3.000 bloqueios na tabela.

O escalonamento de bloqueio ocorre apenas para tabelas que foram acessadas no momento em que o escalonamento é acionado. Suponha que uma única instrução SELECT é uma junção que acessa três tabelas nessa sequência: TableA, TableBe TableC. A instrução adquire 3.000 bloqueios de linha no índice clusterizado para TableA e pelo menos 5.000 bloqueios de linha no índice clusterizado para TableB, mas ainda não acessou TableC. Quando o Mecanismo de Banco de Dados detecta que a instrução adquiriu pelo menos 5.000 bloqueios de linha em TableB, tenta escalar todos os bloqueios mantidos pela transação atual em TableB. Ele também tenta escalar todos os bloqueios mantidos pela transação atual em TableA, mas como o número de bloqueios em TableA é inferior a 5.000, o escalonamento não terá sucesso. Nenhum escalonamento de bloqueio é tentado para TableC porque ainda não tinha sido acessado 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 do que o limite de memória para escalonamento de bloqueio, o Mecanismo de Banco de Dados acionará o escalonamento de bloqueio. O limite de memória depende da opção de configuração dos bloqueios .

  • Se a opção locks estiver definida como sua configuração padrão de 0, o limite de escalonamento de bloqueio 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 memória dinamicamente para ajustar cargas de trabalho variáveis.

  • Se a opção locks for um valor diferente de 0, o limite de escalonamento de bloqueio será 40% (ou menos se houver uma pressão de memória) do valor da opção de 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.

Escalonamento de bloqueio com tipos de bloqueio mistos

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

Por exemplo, suponha uma sessão:

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

A declaração UPDATE adquire estes bloqueios:

  • Bloqueios exclusivos (X) sobre as linhas de dados atualizadas.
  • Bloqueios exclusivos de intenção (IX) nas páginas de índice clusterizadas que contêm essas linhas.
  • Um bloqueio de IX no índice agrupado e outro na tabela.

A declaração SELECT adquire estes bloqueios:

  • Bloqueios partilhados (S) são aplicados em todas as linhas de dados que lê, a menos que a linha já esteja protegida por um bloqueio X da instrução UPDATE.
  • A Intenção Compartilhada (IS) bloqueia todas as páginas de Índice Clusterizado que contêm essas linhas, a menos que a página já esteja protegida por um bloqueio IX.
  • Nenhum bloqueio no índice ou tabela clusterizados porque eles já estão protegidos por bloqueios IX.

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

Reduza o bloqueio e o escalonamento de bloqueio

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

  • Aproveite o bloqueio otimizado de .

    • Bloqueio Otimizado oferece um mecanismo aprimorado de bloqueio de transações que reduz o consumo de memória de bloqueio e a interferência para transações simultâneas. O escalonamento de bloqueio é muito menos provável de ocorrer quando o bloqueio otimizado está ativado.
    • Evite usar os indicadores com bloqueio otimizado. As dicas de tabela podem reduzir a eficácia do bloqueio otimizado.
    • Habilite a opção READ_COMMITTED_SNAPSHOT para o banco de dados para obter o máximo benefício do bloqueio otimizado. Este é o padrão no Banco de Dados SQL do Azure.
    • O bloqueio otimizado requer de recuperação acelerada do banco de dados (ADR) seja habilitado no banco de dados.

Se uma instância do Mecanismo de Banco de Dados gerar muitos bloqueios e estiver vendo escalonamentos de bloqueio frequentes, considere reduzir a quantidade de bloqueio com as seguintes estratégias:

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

    • READ COMMITTED nível de isolamento quando a opção de banco de dados READ_COMMITTED_SNAPSHOT estiver ON.
    • SNAPSHOT nível de isolamento.
    • READ UNCOMMITTED nível de isolamento. Isto apenas pode ser usado para sistemas que podem operar com leituras sujas.

  • 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 nível inferior. O uso dessa opção, no entanto, aumenta os problemas de usuários bloqueando outros usuários que tentam acessar os mesmos dados e não deve ser usado em sistemas com mais do que alguns usuários simultâneos.

  • Se o bloqueio otimizado não estiver disponível, para tabelas particionadas, use a opção LOCK_ESCALATION de ALTER TABLE para escalar bloqueios para a partição em vez da tabela ou para desabilitar o escalonamento de bloqueio para uma tabela.

  • Divida as operações de grandes lotes em várias operações menores. Por exemplo, suponha que você executou a seguinte consulta para remover várias centenas de milhares de linhas antigas de uma tabela de auditoria e, em seguida, descobriu que ela causou um escalonamento de bloqueio que bloqueou outros usuários:

    DELETE FROM LogMessages
WHERE LogDate < '2024-09-26'

    Ao remover estas linhas algumas centenas de cada vez, pode-se reduzir drasticamente o número de bloqueios que se acumulam por transação e evitar o escalonamento de bloqueio. Por exemplo:

    DECLARE @DeletedRows int;

WHILE @DeletedRows IS NULL OR @DeletedRows > 0
BEGIN
    DELETE TOP (500)
    FROM LogMessages
    WHERE LogDate < '2024-09-26'

    SELECT @DeletedRows = @@ROWCOUNT;
END;

  • Reduza o impacto do bloqueio de consulta tornando a consulta o mais eficiente possível. Grandes digitalizações ou um grande número de consultas de chaves podem aumentar a chance de escalonamento de bloqueio; além disso, isso aumenta a chance de deadlocks e afeta negativamente a simultaneidade e o desempenho de forma geral. Depois de encontrar a consulta que causa o escalonamento de bloqueio, procure oportunidades para criar novos índices ou adicionar colunas a um índice existente para remover verificações completas de índice ou tabela e maximizar a eficiência das buscas de índice. Considere 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. Um objetivo dessa otimização é fazer com que as buscas de índice retornem o menor número possível de linhas para minimizar o custo das pesquisas de chaves (maximizar 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 chave pode retornar muitas linhas, ele poderá usar uma otimização de pré-busca para executar a pesquisa. Se o Mecanismo de Banco de Dados usar a pré-busca para uma pesquisa, ele deverá aumentar o nível de isolamento da transação de uma parte da consulta para REPEATABLE READ. Isso significa que o que pode parecer semelhante a uma instrução SELECT em um nível de isolamento READ COMMITTED pode adquirir milhares de bloqueios de chave (no índice agrupado e em um índice não agrupado), o que pode fazer com que essa consulta exceda os limites de escalonamento de bloqueios. Isso é especialmente importante se você achar que o bloqueio escalado é um bloqueio de tabela compartilhado, que, no entanto, não é comumente visto no nível de isolamento de READ COMMITTED padrão.

    Se uma pesquisa de chave com a otimização de pré-busca estiver causando escalonamento de bloqueio, considere adicionar colunas adicionais ao índice não clusterizado que aparece no operador lógico Busca de índice ou Verificação de índice abaixo do operador lógico de pesquisa de chave no plano de consulta. É possível criar um índice que cubra todas as colunas de uma tabela que foram usadas na consulta, ou, pelo menos, um índice que cubra as colunas usadas como critérios de junção ou na cláusula WHERE, se for impraticável incluir tudo na lista de colunas SELECT. Uma associação de loop aninhado também pode usar a otimização de pré-busca, e isso pode causar o mesmo comportamento de bloqueio.

  • O escalonamento de bloqueio não pode ocorrer se um SPID diferente estiver atualmente mantendo um bloqueio de tabela incompatível. O escalonamento de bloqueios é sempre para um bloqueio de tabela, e nunca para bloqueios de página. Além disso, se uma tentativa de escalonamento de bloqueio falhar porque outro SPID mantém um bloqueio de tabela incompatível, a consulta que tentou o escalonamento não fica bloqueada enquanto aguarda por um bloqueio de tabela. Em vez disso, ele continua a adquirir bloqueios em seu nível original, mais granular (linha, chave ou página), periodicamente fazendo tentativas de escalonamento adicionais. Portanto, um método para evitar a escalação de bloqueio 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 escalado. Um bloqueio exclusivo de intenção (IX) no nível da tabela não bloqueia nenhuma linha ou página, mas ainda não é compatível com um bloqueio de tabela compartilhado escalonado (S) ou exclusivo (X). Por exemplo, suponha que tu devas executar um trabalho em lote que modifica um grande número de linhas na tabela mytable e que provocou um 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 de Transact-SQL que contenha o código a seguir e agendar o novo trabalho para iniciar vários minutos antes da hora 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 de IX no mytable por uma hora, o que impede a escalonamento de bloqueios na tabela durante esse período. Este lote não modifica dados nem bloqueia outras consultas (a menos que a outra consulta force um bloqueio de tabela com a dica de TABLOCK ou se um administrador tiver desativado bloqueios de página ou linha em um índice em mytable).

  • Você também pode usar os sinalizadores de rastreamento 1211 e 1224 para desativar alguns ou todos os escalonamentos de bloqueio. No entanto, esses sinalizadores de rastreamento desabilitam todo o escalonamento de bloqueio globalmente para todas as instâncias do Mecanismo de Banco de Dados. O escalonamento de bloqueio desempenha um papel útil no Mecanismo de Banco de Dados, maximizando a eficiência de consultas que, de outra forma, seriam desaceleradas pela sobrecarga de adquirir e liberar vários milhares de bloqueios. O escalonamento de bloqueio também ajuda a minimizar a memória necessária para manter o controle dos 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 bloqueio e a memória de bloqueio 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 usuários ou levar a um longo tempo de recuperação do banco de dados se você reiniciar o serviço de banco de dados.

    Observação

    Usar uma sugestão de bloqueio como ROWLOCK apenas altera a aquisição inicial do bloqueio. As dicas de bloqueio não impedem o escalonamento de bloqueio.

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

Monitorizar a escalação de bloqueio

Monitore o escalonamento de bloqueio usando o evento estendido lock_escalation, 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

Bloqueio dinâmico

O uso de bloqueios de baixo nível, como bloqueios de linha, aumenta a simultaneidade diminuindo a probabilidade de que duas transações solicitem bloqueios na mesma parte de dados ao mesmo tempo. O uso de bloqueios de baixo nível também aumenta o número de bloqueios e os recursos necessários para gerenciá-los. O uso de bloqueios de tabela ou página de alto nível reduz a sobrecarga, mas às custas da redução da simultaneidade.

Um gráfico do custo de bloqueio vs. custo de simultaneidade.

O Mecanismo de Banco de Dados usa uma estratégia de bloqueio dinâmico para determinar os bloqueios mais eficazes. O Mecanismo de Banco de Dados 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 bloqueio, o otimizador pode escolher bloqueios de página em um índice ao executar uma verificação de índice.

Particionamento de bloqueio

Para grandes sistemas de computador, os bloqueios em objetos frequentemente referenciados podem tornar-se um gargalo de desempenho, já que a aquisição e libertação de bloqueios coloca contenção nos recursos internos de bloqueio. O particionamento de bloqueio melhora o desempenho de bloqueio dividindo um único recurso de bloqueio em vários recursos de bloqueio. Este recurso só está disponível para sistemas com 16 ou mais CPUs lógicas, e é ativado automaticamente e não pode ser desativado. Apenas bloqueios de objetos podem ser particionados. Os bloqueios de objeto 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 pelo particionamento de bloqueio:

  • Spinlock

    Isso 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 experimentam um grande volume de atividade, a contenção pode ocorrer enquanto as solicitações de bloqueio aguardam que o spinlock fique disponível. Nessa situação, a aquisição de fechaduras pode se tornar um gargalo e impactar negativamente o desempenho.

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

  • de memória

    Isso é usado para armazenar as estruturas de recursos de bloqueio.

    Uma vez que o spinlock é adquirido, as estruturas de bloqueio são armazenadas na memória e, em seguida, acessadas e possivelmente modificadas. Distribuir o acesso de bloqueio em vários recursos ajuda a eliminar a necessidade de transferir blocos de memória entre CPUs, o que ajuda a melhorar o desempenho.

Implementar e monitorizar a segmentação de bloqueio

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

Ao adquirir bloqueios em um recurso particionado:

  • Apenas os modos de bloqueio NL, Sch-S, IS, IUe IX são adquiridos em uma única partição.

  • Bloqueios partilhados (S), exclusivos (X) e outros em modos distintos de NL, Sch-S, IS, IUe IX devem ser obtidos em todas as partições, começando com o ID de partição 0 e seguindo na ordem de ID de partição. Esses bloqueios em um recurso particionado usam mais memória do que bloqueios no mesmo modo em um recurso não particionado, uma vez que cada partição é efetivamente um bloqueio separado. O aumento de memória é determinado pelo número de partições. Os contadores de desempenho de bloqueio do SQL Server exibem 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 é iniciada. 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 a recursos de bloqueio do mesmo objeto por transações diferentes é distribuído em diferentes partições.

A coluna resource_lock_partition na Vista de Gestão Dinâmica do 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 partição de bloqueio

Os exemplos de código a seguir ilustram o particionamento de bloqueio. Nos exemplos, duas transações são executadas 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 dentro de uma transação. Devido à dica de bloqueio HOLDLOCK, esta instrução adquire e mantém um bloqueio de intenção compartilhada (IS) na tabela (para esta ilustração, os bloqueios de linha e página são ignorados). O bloqueio de IS é adquirido apenas na partição atribuída à transação. Para este exemplo, supõe-se que o bloqueio IS é obtido no ID de partição 7.

-- 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 está a ser executada sob essa transação adquire e retém um bloqueio compartilhado (S) na tabela. O bloqueio S é adquirido em todas as partições, o que resulta em vários bloqueios de tabela, um para cada partição. Por exemplo, num sistema com 16 CPUs, 16 bloqueios de S serão atribuídos aos IDs de partição de bloqueio de 0 a 15. Como o bloqueio de S é compatível com o bloqueio de IS mantido no ID de partição 7 pela transação na sessão 1, não há bloqueio entre as transações.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

Sessão 1:

A instrução SELECT a seguir é executada sob a transação que ainda está ativa na sessão 1. Devido à indicação de bloqueio de tabela exclusiva (X), a transação tenta adquirir um bloqueio de X na tabela. No entanto, o bloqueio S que a transação na sessão 2 mantém obstrui o bloqueio X no ID de partição 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Exemplo B

Sessão 1:

Uma declaração SELECT é executada dentro de uma transação. Devido à sugestão de bloqueio HOLDLOCK, esta instrução adquire e mantém um bloqueio de intenção partilhada (IS) na tabela (nesta ilustração, os bloqueios de linha e página são ignorados). O bloqueio de IS é adquirido apenas na partição atribuída à transação. Para este exemplo, assume-se que o bloqueio IS é obtido na partição com o ID 6.

-- 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 dentro de uma transação. Devido à indicação de bloqueio TABLOCKX, a transação tenta adquirir um bloqueio exclusivo (X) na tabela. Lembre-se de que o bloqueio X deve ser adquirido em todas as partições começando com o ID de partição 0. O bloqueio X é adquirido em todos os IDs de partição 0-5, mas é bloqueado pelo bloqueio IS que é adquirido no ID de partição 6.

Nas partições com IDs de 7 a 15 que o bloqueio de X ainda não atingiu, outras transações podem continuar a adquirir bloqueios.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Níveis de isolamento baseados em versionamento de linha no Motor de Base de Dados

A partir do SQL Server 2005 (9.x), o motor de base de dados oferece uma implementação de um nível de isolamento de transações já existente, READ COMMITTED, que fornece um instantâneo ao nível da instrução usando a versão de linhas. O Motor de Base de Dados também oferece um nível de isolamento de transação, SNAPSHOT, que fornece um instantâneo ao nível da transação também usando o versionamento de linhas.

O versionamento de linhas é uma estrutura geral no SQL Server que invoca um mecanismo de cópia em escrita quando uma linha é modificada ou excluída. Isso requer que, enquanto a transação estiver em execução, a versão antiga da linha esteja disponível para transações que exijam um estado transacional anterior consistente. O versionamento de linha é usado para implementar os seguintes recursos:

  • Crie as tabelas inserted e deleted nos gatilhos. Todas as linhas modificadas pelo gatilho são versionadas. Isso inclui as linhas que foram modificadas pela instrução que acionou o gatilho, assim como quaisquer modificações de dados feitas pelo gatilho.
  • 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, UPDATEou DELETE) em um momento em que houver um conjunto de resultados ativo, as linhas afetadas pela instrução de modificação serão versionadas.
  • Ofereça suporte a operações de índice que especificam a opção ONLINE.
  • Suporte a níveis de isolamento de transação baseados em versionamento de linhas:
    • Uma nova implementação do nível de isolamento READ COMMITTED que utiliza o versionamento de linha para garantir consistência de leitura ao nível da instrução.
    • Um novo nível de isolamento, SNAPSHOT, para fornecer consistência de leitura no nível de transação.

As versões de linha são armazenadas em um repositório de versões. Se Accelerated Database Recovery estiver ativado em um banco de dados, o armazenamento de versões será criado nesse banco de dados. Caso contrário, o armazém de versões é criado na base de dados tempdb.

O banco de dados deve ter espaço suficiente para o armazenamento de versão. Quando o armazenamento de versões está em tempdbe o banco de dados tempdb está cheio, as operações de atualização param de gerar versões, mas continuam a ser bem-sucedidas, mas as operações de leitura podem falhar porque uma versão de linha específica que é necessária não existe. Isso afeta operações como gatilhos, MARS e indexação online.

Quando a Recuperação Acelerada de Banco de Dados é usada e o armazenamento de versões está cheio, as operações de leitura continuam a ser bem-sucedidas, mas as operações de gravação que geram versões, como UPDATE e DELETE falham. As operações de INSERT continuam a ser bem-sucedidas se o banco de dados tiver espaço suficiente.

A utilização de versionamento de linha para transações READ COMMITTED e SNAPSHOT é um processo de 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 de transação apropriado em um aplicativo:

    • Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT é ON, as transações que definem o nível de isolamento READ COMMITTED utilizam o controle de versão de linhas.
    • Quando a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION é ON, as transações podem definir o nível de isolamento SNAPSHOT.

Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION é definida como ON, o Mecanismo de Banco de Dados 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. No entanto, o número de sequência da transação começa com a primeira operação de leitura ou gravação após a instrução BEGIN TRANSACTION. O número de sequência da transação é incrementado em um cada vez que é atribuído.

Quando as opções de banco de dados READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION são definidas como ON, cópias lógicas (versões) são mantidas para todas as modificações de dados executadas no banco de dados. Sempre que uma linha é modificada por uma transação específica, a instância do Mecanismo de Banco de Dados armazena uma versão da imagem confirmada anteriormente da linha no repositório de versões. Cada versão é marcada com o número de sequência 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 versionadas no repositório de versões.

Observação

Para modificação de objetos grandes (LOBs), apenas o fragmento alterado é copiado para o armazenamento de versões.

As versões de linha são mantidas por tempo suficiente para satisfazer os requisitos de transações executadas em níveis de isolamento baseados em versionamento de linha. O Mecanismo de Banco de Dados rastreia o número de sequência de transação útil mais antigo 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 útil mais antigo.

Quando ambas as opções de banco de dados são definidas como OFF, somente as linhas modificadas por gatilhos ou sessões MARS, ou lidas por operações de índice online, são versionadas. Essas versões de linha são liberadas quando não são mais necessárias. Um processo em segundo plano remove versões de linha obsoletas.

Observação

Para transações de execução curta, uma versão de uma linha modificada pode ser armazenada em cache no pool de buffers sem ser gravada no repositório de versões. Se a necessidade da linha versionada for de curta duração, a linha será descartada do pool de buffers e não incorrerá em sobrecarga de E/S.

Comportamento ao ler dados

Quando as transações executadas sob isolamento baseado em controle de versão de linha leem dados, as operações de leitura não adquirem bloqueios compartilhados (S) nos dados que estão sendo lidos e, portanto, não bloqueiam transações que estão modificando dados. Além disso, a sobrecarga de recursos de bloqueio é minimizada à medida que o número de bloqueios adquiridos é reduzido. O isolamento READ COMMITTED usando versionamento de linha e o isolamento SNAPSHOT são projetados para fornecer consistência de leitura ao nível da instrução ou da transação dos dados versionados.

Todas as consultas, incluindo transações executadas em níveis de isolamento baseados em controle de versão de linha, adquirem bloqueios de estabilidade de esquema (Sch-S) durante a compilação e a execução. Devido a isso, as consultas são bloqueadas quando uma transação simultânea mantém um bloqueio de modificação de esquema (Sch-M) na tabela. Por exemplo, uma operação DDL (linguagem de definição de dados) adquire um bloqueio de Sch-M antes de modificar as informações de esquema da tabela. As transações, incluindo aquelas executadas sob um nível de isolamento baseado em versionamento de linhas, são bloqueadas ao tentar adquirir um bloqueio de Sch-S. Por outro lado, uma consulta que contém um bloqueio Sch-S bloqueia uma transação concorrente que tenta adquirir um bloqueio Sch-M.

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

  • O mais próximo, mas inferior ao número de sequência da transação de captura que lê a linha.

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

As operações de leitura executadas por uma transação SNAPSHOT recuperam a última versão de cada linha que foi confirmada no momento em que a transação SNAPSHOT foi iniciada. Isso fornece um instantâneo transacionalmente consistente dos dados tal como existiam no início da transação.

READ COMMITTED transações usando versionamento de linhas operam da mesma maneira. A diferença é que a transação READ COMMITTED não usa o seu próprio número de sequência de transação ao escolher versões de linha. Cada vez que uma instrução é iniciada, a transação READ COMMITTED lê o número de sequência de transação mais recente emitido para essa instância específica do Motor de Base de Dados. Este é o número de sequência da transação usado para selecionar as versões de linha para essa instrução. Isso permite que as transações READ COMMITTED vejam um instantâneo dos dados tal como eles existem no início de cada instrução.

Observação

Embora, transações READ COMMITTED usando o controle de versão de linha forneçam uma exibição transacionalmente consistente dos dados a nível de declaração, as versões de linha geradas ou acessadas por esse tipo de transação são mantidas até que a transação seja concluída.

Comportamento ao modificar dados

O comportamento das gravações de dados é diferente com e sem bloqueio otimizado habilitado.

Modificar dados sem bloqueio otimizado

Em uma transação READ COMMITTED usando controle de versão de linha, a seleção de linhas para atualização é feita por meio de uma análise de bloqueio, onde um bloqueio de atualização (U) é adquirido na linha de dados à medida que os valores dos dados são lidos. Isso é o mesmo que uma transação READ COMMITTED que não utiliza versionamento 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 sob isolamento SNAPSHOT adotam uma abordagem otimista para a modificação de dados, adquirindo bloqueios nos dados antes de executar a modificação apenas para impor restrições. Caso contrário, os bloqueios só serão aplicados nos dados quando estes forem modificados. Quando uma linha de dados atende aos critérios de atualização, a transação SNAPSHOT verifica se a linha de dados não foi modificada por uma transação simultânea confirmada após o início da transação SNAPSHOT. Se a linha de dados tiver sido modificada fora da transação SNAPSHOT, ocorrerá um conflito de atualização e a transação SNAPSHOT será encerrada. O conflito de atualização é tratado pelo Mecanismo de Banco de Dados e não há como desabilitar a deteção de conflito de atualização.

Observação

As operações de atualização executadas sob isolamento SNAPSHOT, internamente, são realizadas sob isolamento READ COMMITTED quando a transação SNAPSHOT acede a algum dos seguintes:

Uma tabela com uma restrição de chave estrangeira.

Uma tabela referenciada na restrição de chave estrangeira de outra tabela.

Uma exibição indexada que faz referência a mais de uma tabela.

No entanto, mesmo nessas condições, a operação de atualização continua a verificar se os dados não foram modificados por outra transação. Se os dados tiverem sido modificados por outra transação, a transação SNAPSHOT encontrará um conflito de atualização e será encerrada. Os conflitos de atualização devem ser tratados e tentados novamente pela aplicação.

Modificar dados com bloqueio otimizado

Com o bloqueio otimizado ativado e a opção de base de dados READ_COMMITTED_SNAPSHOT (RCSI) ativada, utilizando o nível de isolamento padrão READ COMMITTED, os leitores não adquirem nenhum bloqueio, e os escritores adquirem bloqueios de curta duração e baixo nível, em vez de bloqueios que expiram no final da transação.

Recomenda-se a ativação do RCSI para maior eficiência com o bloqueio otimizado. Ao utilizar níveis de isolamento mais rígidos, como REPEATABLE READ ou SERIALIZABLE, o motor de base de dados mantém bloqueios de linha e página até ao final da transação, tanto para leitores como para gravadores, resultando num aumento do bloqueio e da memória de bloqueios.

Com o RCSI ativado, e ao usar o nível de isolamento READ COMMITTED padrão, os gravadores qualificam as linhas de acordo com o predicado com base na versão mais recente da linha confirmada, sem adquirir bloqueios U. Uma consulta espera somente quando a linha é elegível e há outra transação de gravação ativa nessa linha ou página. A qualificação com base na versão confirmada mais recente e o bloqueio apenas das linhas qualificadas reduzem o bloqueio e aumentam a simultaneidade.

Se forem detetados conflitos de atualização com o RCSI e no nível de isolamento de READ COMMITTED predefinido, estes são tratados e repetidos automaticamente sem qualquer impacto nas cargas de trabalho do cliente.

Com o bloqueio otimizado habilitado e ao usar o nível de isolamento SNAPSHOT, o comportamento dos conflitos de atualização é o mesmo que sem o bloqueio otimizado. Os conflitos de atualização devem ser tratados e repetidos pela aplicação.

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 em resumo

A tabela a seguir resume as diferenças entre o isolamento SNAPSHOT e o isolamento READ COMMITTED usando o versionamento de linhas.

Propriedade READ COMMITTED nível de isolamento usando o versionamento de linha SNAPSHOT nível de isolamento
A opção de banco de dados que deve ser definida como ON para habilitar o suporte necessário. READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION
Como uma sessão solicita o tipo específico de versionamento de linha. Use o nível de isolamento de READ COMMITTED padrão ou execute a instrução SET TRANSACTION ISOLATION LEVEL para especificar o nível de isolamento READ COMMITTED. Isso pode ser feito após o início da transação. Requer 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 pelas declarações. Todos os dados que foram confirmados antes do início de cada declaração. Todos os dados que foram confirmados antes do início de cada transação.
Como as atualizações são tratadas. Sem bloqueio otimizado: Reverte de versões de linha para dados reais para selecionar linhas a serem atualizadas e usa bloqueios de atualização nas linhas de dados selecionadas. Adquire bloqueios exclusivos nas linhas de dados reais que serão modificadas. Nenhuma deteção de conflito de atualização.

Com bloqueio otimizado: 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 detetados conflitos de atualização, eles serão tratados e repetidos automaticamente.		 Usa versões de linha para selecionar linhas a serem atualizadas. Tenta adquirir um bloqueio exclusivo na linha de dados real 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á encerrada.
Atualizar a deteção de conflitos Sem bloqueio otimizado: Nenhum.

Com bloqueio otimizado: Se forem detetados conflitos de atualização, eles serão tratados e repetidos automaticamente.		 Suporte integrado. Não pode ser desativado.

Uso de recursos de versionamento de linha

A plataforma de versionamento de linha oferece suporte aos seguintes recursos do Mecanismo de Banco de Dados:

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

A estrutura de controle de versão de linha também suporta os seguintes níveis de isolamento de transação baseados em controle de versão de linha.

  • Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT é definida como ON, as transações READ_COMMITTED fornecem consistência de leitura a nível de instrução usando o controle de versão de linha.
  • Quando a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION é definida como ON, as transações SNAPSHOT fornecem consistência de leitura ao nível de transação usando versionamento de linha.

Os níveis de isolamento baseados em versionamento de linha reduzem o número de bloqueios adquiridos por 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 que uma transação é bloqueada por bloqueios adquiridos por outras transações.

Os níveis de isolamento baseados em versão de linha aumentam os recursos necessários para modificações de dados. Habilitar essas opções faz com que todas as modificações de dados para o banco de dados sejam versionadas. Uma cópia dos dados antes da modificação é armazenada no armazenamento de versão, mesmo quando não há transações ativas usando isolamento baseado em versionamento de linha. Os dados após a modificação contêm um ponteiro para os dados versionados no repositório de versões. Para objetos grandes, apenas parte do objeto que foi alterado é armazenada no armazenamento de versão.

Espaço usado no tempdb

Para cada instância do Mecanismo de Banco de Dados, o espaço de armazenamento de versões deve ser suficiente para suportar as versões de linha. O administrador do banco de dados deve garantir que tempdb e outros bancos de dados (se a Recuperação Acelerada de Banco de Dados estiver habilitada) tenham amplo espaço para dar suporte ao armazenamento de versão. Existem dois tipos de repositórios de versões:

  • A loja de versões de construções de índices online é utilizada para a construção de índices online.
  • O armazenamento de versão comum é usado para todas as outras operações de modificação de dados.

As versões de linha devem ser armazenadas pelo tempo que uma transação ativa precisar acessá-las. Periodicamente, um thread em segundo plano remove versões de linha que não são mais necessárias e libera espaço no armazenamento de versões. Uma transação de longa duração impede que o espaço no repositório de versões seja liberado se ele atender a qualquer uma das seguintes condições:

  • Ele usa isolamento baseado em versionamento de linha.
  • Ele usa gatilhos, MARS ou operações de compilação de índice on-line.
  • Ele gera versões de linha.

Observação

Quando um gatilho é invocado dentro de uma transação, as versões de linha criadas pelo gatilho são mantidas até o final da transação, mesmo que as versões de linha não sejam mais necessárias após a conclusão do gatilho. Isso também se aplica a transações READ COMMITTED que usam versionamento de linhas. Com esse tipo de transação, uma exibição transacionalmente consistente do banco de dados é necessária apenas para cada instrução na transação. Isso significa que as versões das linhas criadas para uma declaração na transação não são mais necessárias após a conclusão da declaração. No entanto, as versões de linha criadas por cada instrução na transação são mantidas até que a transação seja concluída.

Se o armazenamento de versão estiver em tempdbe o tempdb ficar sem espaço, o Mecanismo de Banco de Dados força os armazenamentos de versão a reduzirem-se. Durante o processo de redução, as transações de maior duração que ainda não geraram versões de linha são marcadas como vítimas. Uma mensagem 3967 é gerada no log de erros para cada transação de vítima. Se uma transação for marcada como vítima, ela não poderá mais ler as versões de linha no repositório de versões. Quando tenta ler versões de linha, a mensagem 3966 é gerada e a transação é anulada. Se o processo de encolhimento for bem-sucedido, o espaço fica disponível em tempdb. Caso contrário, tempdb fica sem espaço e ocorre o seguinte:

  • As operações de gravação continuam a ser executadas, mas não geram versões. Uma mensagem informativa (3959) aparece no log de erros, mas a transação que grava dados não é afetada.

  • As transações que tentam aceder a versões de linhas que não foram geradas devido a um retrocesso total tempdb e concluem com um erro 3958.

Espaço usado em linhas de dados

Cada linha de banco de dados pode usar até 14 bytes no final da linha para informações de controle de versão de linha. A informação de versionamento de linhas contém o número de sequência da transação que confirmou a versão e o ponteiro para a linha versionada. Esses 14 bytes são adicionados na primeira vez que a linha é modificada ou quando uma nova linha é inserida, sob qualquer uma destas condições:

  • As opções READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION estão definidas para ON.
  • A tabela tem um gatilho.
  • Vários conjuntos de resultados ativos (MARS) estão sendo usados.
  • As operações de compilação de índice on-line estão atualmente em execução na tabela.

Se o armazenamento de versão estiver em tempdb, esses 14 bytes serão removidos da linha do banco de dados na primeira vez que a linha for modificada sob todas estas condições:

  • As opções READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION estão definidas como OFF.
  • O gatilho não existe mais na mesa.
  • O MARS não está a ser utilizado.
  • As operações de compilação de índice on-line não estão em execução no momento.

Os 14 bytes também são removidos quando uma linha é modificada se a Recuperação Acelerada de Banco de Dados não estiver mais habilitada e as condições acima forem satisfeitas.

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

Diminuir o fator de preenchimento pode ajudar a evitar ou diminuir a fragmentação das páginas de índice. Para exibir as informações atuais de densidade de página para os dados e índices de uma tabela ou exibição, você pode usar sys.dm_db_index_physical_stats.

Espaço utilizado em objetos grandes

O Mecanismo de Banco de Dados oferece suporte a vários tipos de dados que podem conter cadeias de caracteres grandes de até 2 gigabytes (GB) de comprimento, como: nvarchar(max), varchar(max), varbinary(max), ntext, texte image. Os grandes dados armazenados usando esses tipos de dados são armazenados em uma série de fragmentos de dados vinculados à linha de dados. As informações de versionamento de linhas são armazenadas em cada fragmento usado para armazenar grandes cadeias de caracteres. Os fragmentos de dados são armazenados em um conjunto de páginas dedicadas a objetos grandes em uma tabela.

À medida que novos valores grandes são adicionados a um banco de dados, eles são alocados usando um máximo de 8040 bytes de dados por fragmento. As versões anteriores do Mecanismo de Banco de Dados armazenavam até 8080 bytes de ntext, textou image dados por fragmento.

Os dados dos objetos grandes (LOB) ntext, texte image existentes não são atualizados para dar lugar às informações de versão de linha quando um banco de dados é atualizado para o SQL Server a partir de uma versão anterior do SQL Server. No entanto, na primeira vez que os dados LOB são modificados, eles são atualizados dinamicamente para permitir o armazenamento de informações de controle de versão. Isso acontece mesmo se as versões de linha não forem geradas. Depois que os dados 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 se apenas 1 byte seja modificado. Esta é uma operação única para cada coluna ntext, textou 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 LOB. Ele também pode gerar uma grande quantidade de atividade de registro se a modificação for totalmente registrada. WRITETEXT e UPDATETEXT são operações minimamente registadas se o modelo de recuperação do banco de dados não estiver configurado como "FULL".

Espaço em disco suficiente deve ser alocado para acomodar esse requisito.

Monitorizar o versionamento de linhas e o repositório de versões

Para monitorar o controle de versão de linhas, o armazenamento de versões e os processos de isolamento de instantâneo quanto a desempenho e problemas, o Mecanismo de Banco de Dados fornece ferramentas na forma de DMVs (Exibições de Gerenciamento Dinâmico) e contadores de desempenho.

IMT

Os seguintes DMVs fornecem informações sobre o estado atual do sistema de tempdb e o armazenamento de versão, bem como sobre as transações que utilizam o versionamento de linhas.

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

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

  • sys.dm_db_task_space_usage. Retorna a atividade de alocação e desalocação de página por tarefa para o banco de dados. Para obter mais informações, consulte 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ões. Ele agrupa os 256 melhores comprimentos de registro agregados por database_id e rowset_id. Use esta função para encontrar os maiores consumidores da loja de versões. Aplica-se apenas ao armazenamento de versões em tempdb. Para obter mais informações, consulte sys.dm_tran_top_version_generators (Transact-SQL).

  • sys.dm_tran_version_store. Retorna uma tabela virtual que mostra todos os registos de versão no armazenamento de versão comum. Aplica-se apenas ao armazenamento de versões em tempdb. Para obter mais informações, consulte sys.dm_tran_version_store (Transact-SQL).

  • sys.dm_tran_version_store_space_usage. Retorna uma tabela virtual que exibe o espaço total em tempdb usado pelos registros de armazenamento de versão para cada banco de dados. Aplica-se apenas ao armazenamento de versões em tempdb. Para obter mais informações, consulte sys.dm_tran_version_store_space_usage (Transact-SQL).

    Observação

    Consultar sys.dm_tran_top_version_generators e sys.dm_tran_version_store pode ser dispendioso, já que ambos varrem todo o armazenamento de versões, que pode ser grande. sys.dm_tran_version_store_space_usage é eficiente e não é caro de executar porque não navega pelos registros de armazenamento de versão individuais e, em vez disso, retorna o espaço de armazenamento de versão agregado consumido em 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 na instância do SQL Server que usam controle de versão de linha. As transações do sistema não aparecem nesta DMV. Para obter mais informações, consulte sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

  • sys.dm_tran_transactions_snapshot. Retorna uma tabela virtual que exibe capturas feitas por cada transação. O instantâneo contém o número de sequência das transações ativas que usam o versionamento de linhas. Para obter mais informações, consulte 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 da transação na sessão atual. Para obter mais informações, consulte sys.dm_tran_current_transaction (Transact-SQL).

  • sys.dm_tran_current_snapshot. Retorna uma tabela virtual que exibe todas as transações ativas quando a transação atual de isolamento por instantâneo é iniciada. Se a transação atual estiver usando isolamento de instantâneo, esta função não retornará linhas. A DMV sys.dm_tran_current_snapshot é semelhante ao sys.dm_tran_transactions_snapshot, exceto que retorna apenas as transações ativas para a foto instantânea atual. Para obter mais informações, consulte sys.dm_tran_current_snapshot (Transact-SQL).

  • sys.dm_tran_persistent_version_store_stats. Retorna estatísticas para o armazenamento de versão persistente em cada banco de dados usado quando a Recuperação Acelerada de Banco de Dados está habilitada. Para obter mais informações, consulte sys.dm_tran_persistent_version_store_stats (Transact-SQL).

Contadores de desempenho

Os contadores de desempenho a seguir monitoram o armazenamento de versão no tempdb, bem como as transações usando o 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 kilobytes (KB), de espaço livre no banco de dados tempdb. Deve haver espaço livre suficiente no tempdb para lidar com o armazenamento de versão que oferece suporte ao isolamento de instantâneo.

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

    [tamanho do armazenamento de versão comum] = 2 * [dados de armazenamento de versão gerados por minuto] * [maior tempo de execução (minutos) da transação]

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

  • Tamanho do armazenamento de versão (KB). Monitora o tamanho em KB de todos os armazenamentos de versão no tempdb. Essas informações ajudam a determinar a quantidade de espaço necessária no banco de dados tempdb para o armazenamento de versão. A monitorização deste 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ões (KB/s). Monitora a taxa de criação de versões em kilobytes por segundo em todos os armazenamentos de versão no tempdb.

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

    Observação

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

  • Número de unidades do Armazenamento de Versão. Monitora o número de unidades de armazenamento de versão.

  • criação da unidade do Repositório de Versões. Monitora o número total de unidades de armazenamento de versão criadas para armazenar versões de linha desde que a instância foi iniciada.

  • Truncamento da Unidade do Repositório de Versão. Monitora o número total de unidades de armazenamento de versão truncadas desde que a instância foi 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 atualização instantânea que têm conflitos de atualização em relação ao número total de transações de atualização instantânea.

  • Maior Tempo de Execução da Transação. Monitora o maior tempo de execução em segundos de qualquer transação usando o controle de versão de linha. Isso pode ser usado para determinar se alguma transação está sendo executada por um período de tempo inesperado.

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

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

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

  • Transações de versão não instantânea. Monitora o número total de transações ativas que não são instantâneas e que geram registos de versão.

    Observação

    A soma de Transações de Captura de Atualização e Transações de Versão Não Instantânea representa o número total de transações que participam na geração de versões. A diferença entre Transações de snapshot e Transações de snapshot de atualização representa o número de transações de snapshot somente leitura.

Exemplo de nível de isolamento baseado em versionamento de linhas

Os exemplos a seguir mostram as diferenças de comportamento entre as transações de isolamento SNAPSHOT e as transações READ COMMITTED que usam versionamento de linha.

Um. Trabalhar com isolamento SNAPSHOT

Neste exemplo, uma transação executada sob isolamento SNAPSHOT lê dados que são modificados por outra transação. A transação SNAPSHOT não bloqueia a operação de atualização executada pela outra transação e continua a ler dados da linha versionada, ignorando a modificação de dados. No entanto, quando a transação SNAPSHOT tenta modificar os dados que já foram modificados pela outra transação, a transação SNAPSHOT gera um erro e é encerrada.

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;

Na 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 older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Na 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 isolamento READ COMMITTED usando versionamento de linha

Neste exemplo, uma transação READ COMMITTED usando controle de versão de linha é executada simultaneamente com outra transação. A transação READ COMMITTED comporta-se de forma diferente de uma transação SNAPSHOT. Como uma transação SNAPSHOT, a transação READ COMMITTED lerá linhas versionadas mesmo depois que a outra transação tiver modificado os dados. No entanto, ao contrário de uma transação SNAPSHOT, a transação READ COMMITTED:

  • Lê os dados modificados depois que a outra transação confirma as alterações de dados.
  • É capaz de atualizar os dados modificados pela outra transação, enquanto a transação SNAPSHOT não conseguia.

Na sessão 1:

USE AdventureWorks2022;
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;

Na 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;

Na 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

Ativar níveis de isolamento baseados em versão de linha

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

Quando a opção READ_COMMITTED_SNAPSHOT banco de dados é definida como ON, os mecanismos usados para dar suporte à opção são ativados imediatamente. Ao definir a opção READ_COMMITTED_SNAPSHOT, somente 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ída. O banco de dados não precisa estar no modo de usuário único.

A seguinte instrução Transact-SQL permite READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

Quando a opção ALLOW_SNAPSHOT_ISOLATION banco de dados é definida como ON, a instância do Mecanismo de Banco de Dados não começa a gerar 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 Mecanismo de Banco de Dados definirá o estado da opção como PENDING_ON. Após a conclusão de todas as transações de modificação, o estado da opção é alterado para ON. Os utilizadores não podem iniciar uma transação de SNAPSHOT na base de dados até que a opção seja ON. Da mesma forma, o banco de dados passa por um estado PENDING_OFF quando o administrador do banco de dados define a opção ALLOW_SNAPSHOT_ISOLATION como OFF.

A seguinte instrução Transact-SQL permite ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

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

Estado de isolamento SNAPSHOT para o banco de dados atual Descrição
OFF O suporte para transações de isolamento SNAPSHOT não está ativado. Transações de isolamento SNAPSHOT não são permitidas.
PENDING_ON O suporte para transações de isolamento SNAPSHOT está em estado de transição (de OFF para ON). As transações abertas devem ser concluídas.

Não são permitidas transações de isolamento do tipo SNAPSHOT.
ON O suporte para transações de isolamento SNAPSHOT foi ativado.

As SNAPSHOT transações são permitidas.
PENDING_OFF O suporte para transações de isolamento SNAPSHOT está em estado de transição (de ON para OFF).

SNAPSHOT transações iniciadas após esse período não podem acessar esse banco de dados. As transações de SNAPSHOT existentes ainda podem acessar esse banco de dados. As transações de gravação existentes ainda usam controle de versão nesse banco de dados. O estado PENDING_OFF não passa a ser OFF até que todas as transações SNAPSHOT, iniciadas quando o estado de isolamento do banco de dados SNAPSHOT era ON, sejam concluídas.

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

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

A opção ALLOW_SNAPSHOT_ISOLATION é definida automaticamente 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, tempdbou msdb.

Usar níveis de isolamento baseados em versionamento de linha

O sistema de versionamento de linhas está sempre ativado e é utilizado por várias funcionalidades. Além de fornecer níveis de isolamento baseados em versionamento de linha, é usado para suportar modificações feitas em disparadores, em sessões com vários conjuntos de resultados ativos (MARS), e para suportar leituras de dados em operações de índice online.

Os níveis de isolamento baseados em versionamento de linhas são habilitados ao nível da base de dados. Qualquer aplicativo que acesse objetos de bancos de dados habilitados pode executar consultas usando os seguintes níveis de isolamento:

  • READ COMMITTED que usa o controle de versão de linha definindo a opção de banco de dados READ_COMMITTED_SNAPSHOT como ON conforme mostrado no exemplo de código a seguir:

    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.

  • SNAPSHOT isolamento definindo a opção ALLOW_SNAPSHOT_ISOLATION banco de dados como ON conforme mostrado no exemplo de código a seguir:

    ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

    Ao usar consultas entre bancos de dados, uma transação em execução sob isolamento SNAPSHOT pode acessar tabelas no(s) banco(s) de dados que têm a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION definida como ON. Para acessar tabelas em bancos de dados que não têm a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION definida como ON, o nível de isolamento deve ser alterado. Por exemplo, o exemplo de código a seguir mostra uma instrução SELECT que une duas tabelas durante a execução em uma transação SNAPSHOT. Uma tabela pertence a um banco de dados no qual o isolamento SNAPSHOT não está habilitado. Quando a instrução SELECT é executada no isolamento SNAPSHOT, ela falha em ser executada com êxito.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

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 READ COMMITTED ao acessar uma tabela específica. Devido a essa alteração, a instrução SELECT é executada com êxito.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

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 baseados em versionamento de linhas

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

  • READ_COMMITTED_SNAPSHOT não pode ser ativado em tempdb, msdbou master.

  • As tabelas temp globais são armazenadas em tempdb. Ao acessar tabelas temporárias globais dentro de uma transação SNAPSHOT, uma das seguintes situações deve acontecer:

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

  • SNAPSHOT transações falham quando:

    • Uma base de dados torna-se só de leitura depois de iniciada a transação SNAPSHOT, mas antes que a transação SNAPSHOT aceda à base de dados.
    • Se acessar objetos de vários bancos de dados, um estado do banco de dados foi alterado de tal forma que a recuperação do banco de dados ocorreu depois que uma transação de SNAPSHOT é iniciada, mas antes que a transação SNAPSHOT acesse o banco de dados. Por exemplo: o banco de dados foi definido como OFFLINE e, em seguida, para ONLINE, o banco de dados foi automaticamente fechado e reaberto devido à opção AUTO_CLOSE definida como ONou o banco de dados foi desanexado e reanexado.

  • Não há suporte para transações distribuídas, incluindo consultas em bancos de dados particionados distribuídos, sob o isolamento SNAPSHOT.

  • O Mecanismo de Banco de Dados não mantém várias versões dos metadados do sistema. Instruções DDL (linguagem de definição de dados) em tabelas e outros objetos de banco de dados (índices, exibições, tipos de dados, procedimentos armazenados e funções comuns de tempo de execução de linguagem) alteram metadados. Se uma instrução DDL modifica um objeto, qualquer referência simultânea ao objeto sob isolamento SNAPSHOT faz com que a transação SNAPSHOT falhe. READ COMMITTED transações não têm essa limitação quando a opção de banco de dados READ_COMMITTED_SNAPSHOT está definida como ON.

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

    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 é executada recebe um erro se tentar fazer referência à tabela HumanResources.Employee depois que a instrução ALTER INDEX for executada. READ COMMITTED transações que usam o controle de versão de linha não são afetadas.

    Observação

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

Personalizar o bloqueio e a versão de linhas

Personalizar o tempo limite de bloqueio

Quando uma instância do Mecanismo de Banco de Dados 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 que o bloqueio existente seja liberado. Por padrão, não há período de tempo limite para esperas de bloqueio, portanto, uma transação pode ser bloqueada indefinidamente.

Observação

Use o modo de exibição de gerenciamento dinâmico sys.dm_os_waiting_tasks para determinar se uma tarefa está sendo bloqueada e o que está bloqueando-a. Para obter mais informações e exemplos, consulte Compreender e resolver problemas de bloqueio do SQL Server.

A configuração LOCK_TIMEOUT permite que um aplicativo defina um tempo máximo de espera de uma instrução em um recurso bloqueado. Quando uma instrução espera mais 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. Qualquer transação contendo a declaração, todavia, não é revertida. Portanto, o aplicativo deve ter um manipulador de erros que pode intercetar a mensagem de erro 1222. Se um aplicativo não intercetar o erro, o aplicativo poderá prosseguir sem saber que uma instrução individual dentro de uma transação foi cancelada, mas a transação permanece ativa. Erros podem ocorrer porque instruções posteriores na transação podem depender da instrução que nunca foi executada.

A implementação de um manipulador de erros que captura a mensagem de erro 1222 permite que uma aplicação lide com a situação de tempo limite e adote medidas corretivas, como: reenviar automaticamente a instrução que foi bloqueada ou reverter toda a transação.

Importante

Os aplicativos que usam transações explícitas e exigem que a transação seja encerrada ao receber o erro 1222 devem reverter explicitamente a transação como parte do tratamento de erros. Sem isso, outras instruções podem ser executadas involuntariamente na mesma sessão enquanto a transação permanece ativa, levando ao crescimento ilimitado do log de transações e perda de dados se a transação for revertida mais tarde.

Para determinar a configuração de 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. Se um aplicativo deve operar em um nível de isolamento diferente, ele pode usar os seguintes métodos para definir o nível de isolamento:

  • Execute a instrução SET TRANSACTION ISOLATION LEVEL.
  • ADO.NET aplicativos que usam o namespace System.Data.SqlClient podem especificar uma opção IsolationLevel usando o método SqlConnection.BeginTransaction.
  • Os aplicativos que usam o 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 para o nível de isolamento de transação desejado.
  • 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 para todas as consultas e instruções DML (linguagem de manipulação de dados) na sessão opera nesse nível de isolamento. O nível de isolamento permanece em vigor até que a sessão termine ou até que o nível de isolamento seja definido para outro nível.

O exemplo a seguir define o nível de isolamento SERIALIZABLE:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

O nível de isolamento pode ser substituído para instruções individuais de consulta ou DML, se necessário, especificando uma indicação ao nível da tabela. Especificar uma dica no nível da tabela não afeta outras instruções na sessão.

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

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

Aqui está 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.

Sugestões de bloqueio

Sugestões de bloqueio podem ser especificadas para referências de tabela individuais nas instruções SELECT, INSERT, UPDATE, DELETE e MERGE. As sugestões especificam o tipo de bloqueio ou versionamento de linhas que a instância do Mecanismo de Banco de Dados utiliza para os dados da tabela. As sugestões de bloqueio ao nível da tabela podem ser usadas quando é requerido um controlo mais fino sobre os tipos de bloqueios adquiridos num objeto. Essas dicas de bloqueio substituem o nível de isolamento de transação atual para a sessão.

Observação

As dicas de bloqueio não são recomendadas para uso quando o bloqueio otimizado está ativado. 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, consulte Dicas de tabela (Transact-SQL).

Observação

Recomendamos que as dicas de bloqueio no nível da tabela sejam usadas para alterar o comportamento de bloqueio padrão somente quando necessário. Forçar um nível de bloqueio pode afetar negativamente a simultaneidade.

O Motor de Base de Dados pode ter que adquirir bloqueios ao ler metadados, mesmo ao processar uma instrução com uma sugestão de bloqueio que impede solicitações de bloqueios compartilhados ao ler dados. Por exemplo, uma instrução SELECT executada sob o nível de isolamento READ UNCOMMITTED ou usando a dica NOLOCK não adquire bloqueios de compartilhamento ao ler dados, mas pode, em algum momento, solicitar bloqueios ao ler uma exibição de catálogo do sistema. Isso significa que é possível que uma instrução SELECT seja bloqueada quando uma transação simultânea estiver modificando os metadados da tabela.

Como mostrado no exemplo a seguir, se o nível de isolamento da transação for definido como SERIALIZABLEe se a dica de bloqueio ao nível da tabela NOLOCK for usada com a instrução SELECT, os bloqueios de intervalo de chaves, normalmente usados para manter as transações SERIALIZABLE, não sã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 que foi adquirido e que faz referência ao HumanResources.Employee é um bloqueio de estabilidade do esquema (Sch-S). Nesse caso, a serializabilidade não é mais garantida.

A opção LOCK_ESCALATION de ALTER TABLE evita bloqueios de tabela durante a escalada de bloqueios e permite bloqueios HoBT (partição) em tabelas particionadas. Essa opção não é uma dica de bloqueio e pode ser usada para reduzir escalonamento de bloqueio. Para obter mais informações, consulte ALTER TABLE (Transact-SQL).

Personalizar o bloqueio para um índice

O Mecanismo de Banco de Dados usa uma estratégia de bloqueio dinâmico que escolhe automaticamente a melhor granularidade de bloqueio para consultas na maioria dos casos. Recomendamos que você não substitua os níveis de bloqueio padrão, a menos que os padrões de acesso à tabela ou índice sejam bem compreendidos e consistentes e haja um problema de contenção de recursos a ser resolvido. Substituir um nível de bloqueio pode impedir significativamente o acesso simultâneo a uma tabela ou índice. Por exemplo, especificar apenas bloqueios no nível da tabela em uma tabela grande que os usuários acessam fortemente pode causar gargalos, porque os usuários devem esperar que o bloqueio no nível da tabela seja liberado antes de acessar a tabela.

Há alguns casos em que não permitir o bloqueio de página ou linha pode ser benéfico, se os padrões de acesso forem bem compreendidos e consistentes. Por exemplo, um aplicativo de banco de dados usa uma tabela de pesquisa que é atualizada semanalmente em um processo em lote. Os leitores simultâneos acessam a tabela com um bloqueio compartilhado (S) e a atualização semanal em lote acessa a tabela com um bloqueio exclusivo (X). Desativar o bloqueio de página e linha na tabela reduz a sobrecarga de bloqueio ao longo da semana, permitindo que os leitores acessem simultaneamente a tabela por meio de bloqueios de tabela compartilhados. Quando a tarefa em lote é executada, ela pode concluir a atualização eficientemente porque obtém um bloqueio de tabela exclusivo.

Desativar o bloqueio de página e linha pode ou não ser aceitável porque a atualização semanal em lote bloqueia o acesso dos leitores simultâneos à tabela enquanto a atualização é executada. Se o trabalho em lote alterar apenas algumas linhas ou páginas, você poderá alterar o nível de bloqueio para permitir o bloqueio no nível de linha ou página, o que permitirá que outras sessões leiam a tabela sem bloquear. Se o trabalho em lote tiver um grande número de atualizações, obter um bloqueio exclusivo na tabela pode ser a melhor maneira de garantir que o trabalho em lote seja executado de forma eficiente.

Em algumas cargas de trabalho, um tipo de impasse pode ocorrer quando duas operações simultâneas adquirem bloqueios de linha na mesma tabela e, em seguida, bloqueiam uma à outra porque ambas precisam bloquear a página. Não permitir bloqueios de fila força uma das operações a esperar, evitando o impasse. Para saber mais sobre deadlocks, consulte o guia Deadlocks.

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

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

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

Transações Nest

As transações explícitas podem ser aninhadas. Isso destina-se principalmente a dar suporte a transações em procedimentos armazenados que podem ser chamados a partir de um processo já em uma transação ou de processos que não têm transação ativa.

O exemplo a seguir mostra o uso de transações aninhadas. Se TransProc for chamado quando uma transação estiver ativa, o resultado da transação aninhada em TransProc é controlado pela transação externa, e as suas instruções INSERT são executadas ou revertidas com base na confirmação ou reversão da transação externa. Se TransProc for executado por um processo que não tenha uma transação pendente, o COMMIT TRANSACTION no final do procedimento confirmará as INSERT declarações.

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 roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

A confirmação de transações internas é ignorada pelo Mecanismo de Banco de Dados quando uma transação externa está ativa. A transação é confirmada ou desfeita com base na confirmação ou desfazimento no final da transação mais externa. Se a transação externa for efetivada, as transações aninhadas internas também serão efetivadas. Se a transação externa for revertida, todas as transações internas também serão revertidas, independentemente de as transações internas terem sido ou não confirmadas individualmente.

Cada chamada para COMMIT TRANSACTION ou COMMIT WORK se aplica à última BEGIN TRANSACTIONexecutada. Se as instruções BEGIN TRANSACTION estiverem aninhadas, uma instrução COMMIT se aplicará somente à última transação aninhada, que é a transação mais interna. Mesmo que uma instrução COMMIT TRANSACTION transaction_name dentro de uma transação aninhada se refira ao nome de uma transação externa, o commit aplica-se apenas à transação mais interna.

Não é permitido que o parâmetro transaction_name de uma instrução ROLLBACK TRANSACTION se refira à transação interna num conjunto de transações aninhadas nomeadas. transaction_name pode referir-se apenas ao nome da transação mais externa. Se uma instrução ROLLBACK TRANSACTION transaction_name usando 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, ela reverterá todas as transações aninhadas, incluindo a transação mais externa.

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

Usar sessões vinculadas

As sessões vinculadas facilitam a coordenação de ações em várias sessões no mesmo servidor. As sessões acopladas permitem que duas ou mais sessões compartilhem a mesma transação e bloqueios e podem trabalhar nos mesmos dados sem conflitos de bloqueio. As sessões acopladas 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 acoplada, uma sessão chama sp_getbindtoken ou srv_getbindtoken (por meio do Open Data Services) para obter um token de ligação. Um token de associação é uma cadeia de caracteres que identifica exclusivamente cada transação vinculada. O token de ligação é então enviado para as outras sessões para ser vinculado à sessão atual. As outras sessões se ligam à transação chamando sp_bindsession, usando o token de ligação recebido da primeira sessão.

Observação

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

Os tokens de vinculação devem ser transmitidos do código da aplicação que cria a primeira sessão para o código da aplicação que posteriormente vincula as 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 ligação para 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 as sessões forem todas iniciadas a partir do mesmo processo de aplicativo, os tokens de ligação poderão ser armazenados na memória global ou passados para funções como um parâmetro.

  • Se as sessões forem feitas a partir de processos de aplicativo separados, os tokens de ligação podem 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 ligação podem ser armazenados numa tabela numa instância do Mecanismo de Base de Dados que pode ser lida por processos que desejam ligar à primeira sessão.

Apenas uma sessão em um conjunto de sessões vinculadas 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 vinculada ao mesmo token 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 outra das sessões acopladas, ocorrerá um erro indicando que o espaço de transação está em uso e a sessão deve tentar novamente mais tarde.

Quando você vincula sessões, cada sessão mantém sua configuração de nível de isolamento. Usar 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 vinculada ao mesmo token.

Tipos de sessões vinculadas

Os dois tipos de sessões vinculadas são locais e distribuídas.

  • Sessão vinculada local Permite que sessões vinculadas partilhem o espaço de transação de uma única transação numa única instância do Mecanismo de Base de Dados.

  • Sessão vinculada distribuída Permite que sessões vinculadas compartilhem a mesma transação em duas ou mais instâncias até que toda a transação seja confirmada ou revertida usando o Microsoft Distributed Transaction Coordinator (MS DTC).

As sessões vinculadas distribuídas não são identificadas por um token de associação de cadeia de caracteres; são identificados por números de identificação de transações distribuídas. Se uma sessão vinculada estiver envolvida numa transação local e executar uma RPC num servidor remoto com SET REMOTE_PROC_TRANSACTIONS ON, a transação local vinculada será automaticamente promovida para uma transação vinculada distribuída pelo MS DTC, e uma sessão MS DTC será iniciada.

Quando usar sessões vinculadas

Em versões anteriores do SQL Server, as sessões vinculadas eram usadas principalmente no desenvolvimento de procedimentos armazenados estendidos que devem executar instruções Transact-SQL em nome do processo que as chama. Fazer com que o processo chamador passe um token de vinculação como um parâmetro do procedimento armazenado estendido permite que o procedimento se junte ao espaço de transação do processo chamador, integrando dessa forma o procedimento armazenado estendido com o processo chamador.

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

As sessões vinculadas podem ser usadas para desenvolver aplicativos de três camadas nos quais a lógica de negócios é incorporada em programas separados que trabalham cooperativamente em uma única transação comercial. Esses programas devem ser codificados para coordenar cuidadosamente seu acesso 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. A qualquer momento, apenas uma sessão pode estar fazendo trabalho como parte da transação; não pode haver execução paralela. A transação só pode ser alternada entre sessões em pontos de rendimento bem definidos, como quando todas as instruções DML foram concluídas e seus resultados foram recuperados.

Transações eficientes em código

É importante manter as transações o mais curtas possível. Quando uma transação é iniciada, um sistema de gerenciamento de banco de dados (DBMS) deve manter muitos recursos até o final da transação para proteger as propriedades de atomicidade, consistência, isolamento e durabilidade (ACID) da transação. Se os dados forem modificados, as linhas modificadas devem ser protegidas com bloqueios exclusivos que impedem qualquer outra transação de ler as linhas, e bloqueios exclusivos devem ser mantidos até que a transação seja confirmada ou revertida. Dependendo das configurações do nível de isolamento da transação, as instruções SELECT podem adquirir bloqueios que devem ser mantidos até que a transação seja confirmada ou revertida. Especialmente em sistemas com muitos utilizadores, as transações devem ser mantidas o mais curtas possível para reduzir a contenção de bloqueio de recursos entre ligações simultâneas. Transações de longa duração e ineficientes podem não ser um problema com um pequeno número de usuários, mas são altamente problemáticas em um sistema com milhares de usuários. A partir do SQL Server 2014 (12.x), o Mecanismo de Banco de Dados oferece suporte a transações duráveis atrasadas. Transações duráveis atrasadas podem melhorar a escalabilidade e o desempenho, mas não garantem durabilidade. Para mais informações, veja Durabilidade do Controle de Transações.

Diretrizes de código

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

  • Não exija a entrada dos usuários durante uma transação. Obtenha todas as informações necessárias dos usuários antes que uma transação seja iniciada. Se for necessária uma entrada de usuário adicional 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 utilizadores respondam imediatamente, os tempos de reação humana são muito mais lentos do que as velocidades do computador. Todos os recursos detidos pela transação são mantidos por um tempo extremamente longo, o que tem o potencial de 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é horas.

  • Não abra uma transação enquanto navega pelos dados, se possível. As transações não devem ser iniciadas até que toda a análise preliminar dos dados tenha sido concluída.

  • Mantenha a transação o mais curta possível. Depois de saber as modificações que devem ser feitas, inicie uma transação, execute as instruções de modificação e, em seguida, confirme ou reverta imediatamente. Não abra a transação antes que ela seja necessária.

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

  • Faça uso inteligente de níveis mais baixos de isolamento de transações. Muitos aplicativos podem ser codificados para usar o nível de isolamento de transação READ COMMITTED. Poucas transações exigem o SERIALIZABLE nível de isolamento de transações.

  • Faça uso inteligente de opções de simultaneidade otimistas. Em um sistema com baixa probabilidade de atualizações simultâneas, a sobrecarga de lidar com um erro ocasional "alguém alterou seus dados depois de lê-los" pode ser muito menor do que a sobrecarga de sempre bloquear linhas à medida que são lidas.

  • Acesse a menor quantidade de dados possível durante uma transação. Isso diminui o número de linhas bloqueadas, reduzindo, dessa forma, a contenção entre as transações.

  • Evite dicas de bloqueio pessimistas, como HOLDLOCK sempre que possível. Dicas como HOLDLOCK ou SERIALIZABLE nível de isolamento podem fazer com que os processos esperem mesmo em bloqueios compartilhados e reduzam a simultaneidade.

  • Evite usar transações implícitas sempre que possível. As transações implícitas podem introduzir um comportamento imprevisível devido à sua natureza. Consulte Transações implícitas e problemas de concorrência.

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

Para evitar problemas de simultaneidade e recursos, gerencie transações implícitas com cuidado. Ao usar transações implícitas, o próximo extrato de 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 mesmo quando requer entrada do usuário. Depois de concluir a última transação necessária para proteger as modificações de dados, desative as transações implícitas até que uma transação seja novamente necessária para proteger as modificações de dados. Esse processo permite que o Mecanismo de Banco de Dados use o modo de confirmação automática enquanto o aplicativo está navegando dados e recebendo entrada do usuário.

Além disso, quando o nível de isolamento SNAPSHOT estiver habilitado, embora uma nova transação não mantenha bloqueios, uma transação de longa duração impedirá que as versões antigas sejam removidas do armazenamento de versões.

Gerencie transações de longa duração

Uma transação de longa duração é uma transação ativa que não foi nem confirmada nem revertida em tempo hábil. Por exemplo, se o início e o fim de uma transação são controlados pelo usuário, uma causa típica de uma transação de longa duração é um usuário iniciando uma transação e, em seguida, saindo enquanto a transação aguarda uma resposta do usuário.

Uma transação de longa duração pode causar sérios problemas para um banco de dados, da seguinte maneira:

  • Se uma instância do servidor for encerrada depois que uma transação ativa tiver executado muitas modificações não confirmadas, a fase de recuperação da reinicialização subsequente poderá levar muito mais tempo do que o tempo especificado pela opção de configuração do servidor recovery interval ou pela opção ALTER DATABASE ... SET TARGET_RECOVERY_TIME. Essas opções controlam pontos de verificação ativos e indiretos, respectivamente. Para obter mais informações sobre os tipos de pontos de verificação, consulte pontos de verificação de banco de dados (SQL Server).

  • Mais importante ainda, embora uma transação em espera possa gerar muito pouco log, ela impede o truncamento de log indefinidamente, fazendo com que o log de transações cresça e, possivelmente, seja completamente preenchido. Se o log de transações for preenchido, o banco de dados não poderá executar mais gravações. Para obter mais informações, consulte o guia de arquitetura e gerenciamento do log de transações do SQL Server, Resolver problemas com um log de transações cheio (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.

Descubra transações de longa duração

Para procurar transações de longa duração, use uma das seguintes opções:

  • sys.dm_tran_database_transactions

    Essa exibição de gerenciamento dinâmico retorna informações sobre transações no nível do banco de dados. Para uma transação de longa duração, as colunas de particular interesse incluem a hora do primeiro registo 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 registo de início no log de transações (database_transaction_begin_lsn).

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

  • DBCC OPENTRAN

    Esta instrução permite identificar o ID de usuário do proprietário da transação, para que você possa rastrear a origem da transação para o término apropriado (confirmar ou reverter). Para obter mais informações, consulte DBCC OPENTRAN (Transact-SQL).

Encerrar uma transação

Para encerrar uma transação em uma sessão específica, use a instrução KILL. No entanto, use esta declaração com muito cuidado, especialmente quando processos críticos estão em execução. Para obter mais informações, consulte KILL (Transact-SQL).

Impasses

Os deadlocks são um tema complexo relacionado ao bloqueio, mas diferente dos bloqueios.

Conteúdo relacionado