SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Controla o comportamento de bloqueio e controle de versão de linha das instruções Transact-SQL emitidas por uma conexão com o SQL Server.

Convenções de sintaxe Transact-SQL

Sintaxe

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]

Argumentos

• READ UNCOMMITTED
  Especifica que as instruções podem ler linhas que foram modificadas por outras transações, mas que ainda não foram confirmadas.

  Transações em execução em nível READ UNCOMMITTED não emitem bloqueios compartilhados para impedir que outras transações modifiquem os dados lidos pela transação atual. Transações READ UNCOMMITTED também não são bloqueadas por bloqueios exclusivos que impediriam a transação atual de ler linhas que foram modificadas, mas não confirmadas, por outras transações. Quando essa opção está definida, é possível ler modificações não confirmadas, chamadas de leituras sujas. Os valores nos dados podem ser alterados e linhas podem aparecer ou desaparecer do conjunto de dados antes do término da transação. Essa opção tem o mesmo efeito de definir NOLOCK em todas as tabelas em todas as instruções SELECT em uma transação. Esse é o menos restritivo dos níveis de isolamento.

  No SQL Server, você também pode minimizar a contenção de bloqueios e, ao mesmo tempo, proteger as transações contra leituras sujas de modificações de dados não confirmadas, usando:

  • O nível de isolamento READ COMMITTED com a opção de banco de dados READ_COMMITTED_SNAPSHOT definida como ON.

  • O nível de isolamento SNAPSHOT.

• READ COMMITTED
  Especifica que as instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações. Isso impede leituras sujas. Os dados podem ser alterados por outras transações entre instruções individuais dentro da transação atual, resultando em leituras não repetíveis ou dados fantasmas. Essa é a opção padrão do SQL Server.

  O comportamento de READ COMMITTED depende da configuração da opção de banco de dados READ_COMMITTED_SNAPSHOT:

  • Se READ_COMMITTED_SNAPSHOT estiver definido como OFF (o padrão), o Mecanismo de Banco de Dados usará bloqueios compartilhados para impedir que outras transações modifiquem linhas enquanto a transação atual estiver executando uma operação de leitura. Os bloqueios compartilhados também bloqueiam a instrução de ler linhas modificadas por outras transações até que a outra transação seja concluída. O tipo de bloqueio compartilhado determina quando ele será liberado. Os bloqueios de linha são liberados antes que a próxima linha seja processada. Os bloqueios de página são liberados quando a próxima página é lida e bloqueios de tabela são liberados quando a instrução é finalizada.

    Observação

    Se READ_COMMITTED_SNAPSHOT estiver definido como ON, o Mecanismo de Banco de Dados usará o controle de versão de linhas para apresentar a cada instrução um instantâneo transacionalmente consistente dos dados conforme se encontravam no início da instrução. Não são usados bloqueios para proteger os dados contra atualizações efetuadas por outras transações. No SQL Server 2008 R2, o isolamento de instantâneo foi estendido para oferecer suporte aos dados FILESTREAM. Sob o modo de isolamento de instantâneo, os dados FILESTREAM lidos por qualquer instrução em uma transação serão a versão transacionalmente consistente dos dados que existiam no início da transação.

  Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT for ON, você poderá usar a dica de tabela READCOMMITTEDLOCK para solicitar bloqueio compartilhado, em vez do controle de versão de linhas, para instruções individuais em transações em execução no nível de isolamento READ COMMITTED.

  Observação
  Quando a opção READ_COMMITTED_SNAPSHOT está definida, apenas a conexão que executa o comando ALTER DATABASE é permitida no banco de dados. Não deve haver nenhuma outra conexão aberta no banco de dados até que ALTER DATABASE esteja concluído. O banco de dados não precisa estar no modo de usuário único.

• REPEATABLE READ
  Especifica que as instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações e que nenhuma outra transação pode modificar dados que foram lidos pela transação atual até que a transação atual seja concluída.

  Os bloqueios compartilhados são colocados em todos os dados lidos por cada instrução na transação, sendo mantidos até que a transação seja concluída. Isso impede que outras transações modifiquem qualquer linha que tenha sido lida pela transação atual. Outras transações podem inserir novas linhas que correspondam às condições de pesquisa das instruções emitidas pela transação atual. Então, se a transação atual tentar a instrução novamente, ela recuperará as novas linhas, o que resultará em leituras fantasmas. Como os bloqueios compartilhados são mantidos até o término da transação, em vez de serem liberados ao final de cada instrução, a simultaneidade é menor que o nível de isolamento READ COMMITTED padrão. Use essa opção apenas quando necessário.

• SNAPSHOT
  Especifica que os dados lidos por qualquer instrução em uma transação serão a versão transacionalmente consistente que existia no início da transação. A transação pode reconhecer apenas modificações de dados que estavam confirmadas antes do início da transação. Modificações de dados efetuadas por outras transações após o início da transação atual não são visíveis para as instruções em execução na transação atual. O efeito será como se as instruções em uma transação obtivessem um instantâneo dos dados confirmados conforme existiam no início da transação.

  Exceto quando um banco de dados está sendo recuperado, as transações SNAPSHOT não requerem bloqueios ao ler dados. Transações SNAPSHOT que leem dados não bloqueiam outras transações de gravar dados. Transações que gravam dados não bloqueiam transações SNAPSHOT de ler dados.

  Durante a fase de reversão de uma recuperação de banco de dados, as transações SNAPSHOT solicitarão um bloqueio se houver uma tentativa de ler dados que se encontram bloqueados por outra transação que está sendo revertida. A transação SNAPSHOT será bloqueada até que aquela transação seja revertida. O bloqueio será liberado tão logo seja concedido.

  A opção de banco de dados ALLOW_SNAPSHOT_ISOLATION deve ser definida como ON para que uma transação que usa o nível de isolamento SNAPSHOT seja iniciada. Se uma transação que usa o nível de isolamento SNAPSHOT acessar dados em vários bancos de dados, ALLOW_SNAPSHOT_ISOLATION deve ser definida como ON em cada banco de dados.

  Uma transação iniciada com outro nível de isolamento não pode ser definida com o nível de isolamento SNAPSHOT; isso causaria a anulação da transação. Se uma transação for iniciada no nível de isolamento SNAPSHOT, você poderá alterar seu nível de isolamento e retorná-la para o SNAPSHOT. Uma transação é iniciada na primeira vez em que ela acessa dados.

  Uma transação em execução sob o nível de isolamento SNAPSHOT pode exibir as alterações feitas por essa transação. Por exemplo, se a transação executar um UPDATE em uma tabela e, em seguida, emitir uma instrução SELECT na mesma tabela, os dados modificados serão incluídos no conjunto de resultados.

  Observação
  Sob o modo de isolamento de instantâneo, os dados FILESTREAM lidos por qualquer instrução em uma transação serão a versão transacionalmente consistente dos dados que existiam no início da transação, não no início da instrução.

  Importante
  Quando o nível de isolamento do instantâneo estiver habilitado, se você estiver excluindo uma linha em um heap (uma tabela sem índice clusterizado) e o log de transações for preenchido antes de o registro de log da linha fantasma persistir, o banco de dados ficará offline. Se isto ocorrer, o banco de dados reiniciará automaticamente, passará por uma recuperação completa e voltará a ficar online.

• SERIALIZABLE
  Especifica o seguinte:

  • As instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações.

  • Nenhuma outra transação pode modificar dados lidos pela transação atual até que a transação atual seja concluída.

  • Outras transações não podem inserir linhas novas com valores chave que estejam no intervalo de chaves lido por qualquer instrução da transação atual até que esta seja concluída.

  Bloqueios de intervalo são colocados no intervalo de valores chave que corresponde às condições de pesquisa de cada instrução executada em uma transação. Isso bloqueia que outras transações atualizem ou insiram qualquer linha que seja qualificada para qualquer uma das instruções executadas pela transação atual. Isto significa que, se qualquer uma das instruções de uma transação for executada uma segunda vez, ela lerá o mesmo conjunto de linhas. Os bloqueios de intervalo são mantidos até que a transação seja concluída. Esse é o mais restritivo dos níveis de isolamento, pois ele bloqueia intervalos de chaves inteiros até que a transação seja concluída. Como a simultaneidade é menor, use essa opção apenas quando necessário. Essa opção tem o mesmo efeito de definir HOLDLOCK em todas as tabelas em todas as instruções SELECT de uma transação.

Comentários

Apenas uma única opção de nível de isolamento pode ser definida por vez, permanecendo definida para aquela conexão até que seja explicitamente alterada. Todas as operações de leitura executadas na transação operam sob as regras do nível de isolamento especificado, a menos que uma dica de tabela na cláusula FROM de uma instrução especifique comportamento de bloqueio ou controle de versão diferente para uma tabela.

Os níveis de isolamento da transação definem o tipo de bloqueio adquirido em operações de leitura. Bloqueios compartilhados adquiridos para READ COMMITTED ou REPEATABLE READ geralmente são bloqueios de linha, embora os bloqueios de linha possam ser escalados para bloqueios de página ou de tabela se um número significativo de linhas em uma página ou tabela forem referenciadas pela leitura. Se uma linha for modificada pela transação depois de ter sido lida, a transação irá adquirir um bloqueio exclusivo para proteger essa linha, sendo mantido até que a transação seja concluída. Por exemplo, se uma transação REPEATABLE READ tiver um bloqueio compartilhado em uma linha e a transação modificar essa linha, o bloqueio de linha compartilhado será convertido em bloqueio de linha exclusivo.

A não ser por uma exceção, é possível alternar de um nível de isolamento para outro durante uma transação. A exceção ocorre ao alterar de qualquer nível de isolamento para o isolamento SNAPSHOT. Isso faz com que a transação falhe e seja revertida. Porém, é possível alterar uma transação iniciada em isolamento SNAPSHOT para qualquer outro nível de isolamento.

Quando você altera uma transação de um nível de isolamento para outro, os recursos lidos após a alteração são protegidos de acordo com as regras do novo nível. Recursos lidos antes da alteração continuam sendo protegidos de acordo com as regras do nível anterior. Por exemplo, se uma transação mudar de READ COMMITTED para SERIALIZABLE, os bloqueios compartilhados adquiridos após a alteração serão, nesse caso, mantidos até o término da transação.

Se você emitir SET TRANSACTION ISOLATION LEVEL em um procedimento armazenado ou em um gatilho, quando o controle retornar para o objeto retornar, o nível de isolamento será redefinido como o nível que estava em vigor quando o objeto foi invocado. Por exemplo, se você definir REPEATABLE READ em um lote e o lote chamar um procedimento armazenado que define o nível de isolamento como SERIALIZABLE, a configuração do nível de isolamento será revertida para REPEATABLE READ quando o procedimento armazenado retornar o controle para o lote.

Observação
Funções e tipos CLR (Common Language Runtime) definidos pelo usuário não podem executar SET TRANSACTION ISOLATION LEVEL. Porém, você pode substituir o nível de isolamento usando uma dica de tabela. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).

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

SET TRANSACTION ISOLATION LEVEL entra em vigor na execução ou em tempo de execução, e não em tempo de análise.

Operações de carregamento em massa otimizadas em heaps bloqueiam consultas executadas com os seguintes níveis de isolamento:

• SNAPSHOT

• READ UNCOMMITTED

• READ COMMITTED usando controle de versão de linha

De modo oposto, consultas executadas com esses níveis de isolamento bloqueiam operações de carregamento em massa otimizados em heaps. Para obter mais informações sobre operações de carregamento em massa, consulte Sobre operações de importação e exportação em massa e Otimizando o desempenho de importação em massa.

Bancos de dados habilitados por FILESTREAM dão suporte aos seguintes níveis de isolamento de transação.

Nível de isolamento	Acesso ao Transact SQL	Acesso ao sistema de arquivos
Leitura não confirmada	SQL Server 2008	Sem suporte
Leitura confirmada	SQL Server 2008	SQL Server 2008
Leitura repetida	SQL Server 2008	Sem suporte
Serializável	SQL Server 2008	Sem suporte
Instantâneo de leitura confirmada	SQL Server 2008 R2	SQL Server 2008 R2
Instantâneo	SQL Server 2008 R2	SQL Server 2008 R2

Exemplos

O exemplo a seguir define o TRANSACTION ISOLATION LEVEL da sessão. Para cada instrução Transact-SQL a seguir, o SQL Server mantém todos os bloqueios compartilhados até o término da transação.

USE AdventureWorks2008R2;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT * 
    FROM HumanResources.EmployeePayHistory;
GO
SELECT * 
    FROM HumanResources.Department;
GO
COMMIT TRANSACTION;
GO

Consulte também

Referência

ALTER DATABASE (Transact-SQL)

DBCC USEROPTIONS (Transact-SQL)

SELECT (Transact-SQL)

SET (Transact-SQL)

Dicas de tabela (Transact-SQL)

Conceitos

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

Níveis de isolamento no Mecanismo de Banco de Dados