Compartilhar via

DEFINIR NÍVEL DE ISOLAMENTO DE TRANSAÇÃO (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Banco de dados SQL no Microsoft Fabric

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

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe para SQL Server, Banco de Dados SQL do Azure e Banco de Dados SQL no Microsoft Fabric.

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

Sintaxe para Azure Synapse Analytics e Parallel Data Warehouse.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Observação

O Azure Synapse Analytics implementa transações ACID. O nível de isolamento padrão é READ UNCOMMITTED. Você pode alterá-lo transformando READ COMMITTED SNAPSHOT ISOLATIONON a opção de READ_COMMITTED_SNAPSHOT banco de dados para um banco de dados de usuário quando conectado ao master banco de dados. Uma vez habilitada, todas as transações neste banco de dados são executadas READ COMMITTED SNAPSHOT ISOLATION e a configuração READ UNCOMMITTED no nível da sessão não é respeitada. Para obter mais informações, consulte as opções ALTER DATABASE SET (Transact-SQL).

Argumentos

LEITURA NÃO CONFIRMADA

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

As transações em execução no READ UNCOMMITTED nível não emitem bloqueios compartilhados para impedir que outras transações modifiquem os dados lidos pela transação atual. READ UNCOMMITTED as transações 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 é definida, é possível ler modificações não confirmadas, que são 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 que a configuração NOLOCK em todas as tabelas em todas as SELECT instruções 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 READ COMMITTED nível de isolamento com a opção READ_COMMITTED_SNAPSHOT de banco de dados definida como ON.

  • O SNAPSHOT nível de isolamento. Para obter mais informações sobre isolamento de instantâneo, confira Isolamento de instantâneo no SQL Server.

LEIA CONFIRMADO

Especifica que as instruções não podem ler dados que foram modificados, mas não 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 opção é o padrão SQL Server.

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

  • Se READ_COMMITTED_SNAPSHOT estiver definido como OFF (o padrão no SQL Server), o Mecanismo de Banco de Dados usará bloqueios compartilhados para impedir que outras transações modifiquem linhas enquanto a transação atual está 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 é 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.

  • Se READ_COMMITTED_SNAPSHOT estiver definido como ON, o Mecanismo de Banco de Dados usará o controle de versão de linha para apresentar cada instrução com um instantâneo transacionalmente consistente dos dados como eles 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.

    • READ_COMMITTED_SNAPSHOT ON é o padrão no Azure SQL Database e SQL Database no Microsoft Fabric.

Importante

Escolher 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 obtém um bloqueio exclusivo em quaisquer dados que modifica e mantém tal bloqueio até que a transação seja concluída, sem considerar o conjunto de níveis de isolamento para a transação em questão. Além disso, uma atualização feita no nível de READ COMMITTED isolamento usa bloqueios de atualização nas linhas de dados selecionadas, enquanto uma atualização feita no nível de SNAPSHOT isolamento usa versões de linha para selecionar linhas a serem atualizadas. Para operações de leitura, níveis de isolamento da transação definem principalmente o nível de proteção dos efeitos das modificações feitas por outras transações. Para obter mais informações, consulte o Guia de Controle de Versão de Linha e Bloqueio de Transações.

O isolamento de instantâneo oferece suporte a dados FILESTREAM. No modo de isolamento de instantâneo, os dados FILESTREAM lidos por qualquer instrução em uma transação são a versão transacionalmente consistente dos dados que existiam no início da transação.

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

Observação

Quando você define a opção READ_COMMITTED_SNAPSHOT , somente a conexão que executa o ALTER DATABASE comando é permitida no banco de dados. Não deve haver nenhuma outra conexão aberta no banco de dados até ALTER DATABASE que seja concluída. O banco de dados não precisa estar no modo de usuário único.

LEITURA REPETÍVEL

Especifica que as instruções não podem ler dados que foram modificados, mas ainda não 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 as linhas que foram lidas 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. Se a transação atual tentar novamente a instrução, ela recuperará as novas linhas, o que resultará em leituras fantasmas. Como os bloqueios compartilhados são mantidos até o final de uma transação em vez de serem liberados no final de cada instrução, a simultaneidade é menor que o nível de isolamento padrão READ COMMITTED . Use essa opção apenas quando necessário.

SNAPSHOT

Especifica que os dados lidos por qualquer instrução em uma transação são a versão transacionalmente consistente dos dados que existiam 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. As modificações de dados feitas por outras transações após o início da transação atual não são visíveis para instruções executadas na transação atual. O efeito é 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, SNAPSHOT as transações não solicitam bloqueios ao ler dados. SNAPSHOT transações que leem dados não impedem que outras transações gravem dados. Transações que gravam dados não impedem SNAPSHOT transações de ler dados.

Durante a fase de reversão de uma recuperação de banco de dados, SNAPSHOT as transações solicitam um bloqueio se for feita uma tentativa de ler dados bloqueados por outra transação que está sendo revertida. A SNAPSHOT transação é bloqueada até que essa transação seja revertida. O bloqueio é liberado imediatamente após sua concessão.

A ALLOW_SNAPSHOT_ISOLATION opção de banco de dados deve ser definida para ON antes que você possa iniciar uma transação que use o SNAPSHOT nível de isolamento. Se uma transação usando o SNAPSHOT nível de isolamento acessar dados em vários bancos de dados, ALLOW_SNAPSHOT_ISOLATION deverá ser definida ON como em cada banco de dados.

Uma transação não pode ser definida como SNAPSHOT nível de isolamento que começou com outro nível de isolamento; isso faz com que a transação seja anulada. Se uma transação for iniciada no nível de SNAPSHOT isolamento, você poderá alterá-la para outro nível de isolamento e voltar para SNAPSHOT. Uma transação é iniciada na primeira vez em que ela acessa dados.

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

Observação

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

SERIALIZABLE

Especifica as seguintes condições:

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

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

  • Outras transações não podem inserir novas linhas com valores de chave que cairiam no intervalo de chaves lidas por quaisquer instruções na transação atual até que a transação atual 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. Isso significa que, se qualquer uma das instruções em uma transação for executada uma segunda vez, elas lerão 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 que a configuração HOLDLOCK em todas as tabelas em todas as SELECT instruções em uma transação.

Comentários

Apenas uma das opções de nível de isolamento pode ser definida por vez e permanece definida para essa conexão até que ela seja explicitamente alterada. Todas as operações de leitura executadas na transação operam sob as regras para o nível de isolamento especificado, a menos que uma dica de tabela na FROM cláusula de uma instrução especifique um 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 escalonados para bloqueios de página ou tabela se um número significativo de linhas em uma página ou tabela for referenciado pela leitura. Se a transação modificar uma linha após a leitura, a transação adquirirá um bloqueio exclusivo para proteger essa linha e o bloqueio exclusivo será mantido até que a transação seja concluída. Por exemplo, se uma REPEATABLE READ transação tiver um bloqueio compartilhado em uma linha e a transação modificar a linha, o bloqueio de linha compartilhada será convertido em um 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 SNAPSHOT isolamento. Isso faz com que a transação falhe e seja revertida. No entanto, você pode alterar uma transação iniciada isoladamente 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 foi alterada de READ COMMITTED para SERIALIZABLE, os bloqueios compartilhados adquiridos após a alteração serão mantidos até o final da transação.

Se você emitir SET TRANSACTION ISOLATION LEVEL em um procedimento armazenado ou gatilho, quando o objeto retornar o controle, o nível de isolamento será redefinido para o nível 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 de nível de isolamento será revertida para REPEATABLE READ quando o procedimento armazenado retornar o controle para o lote.

Observação

Funções definidas pelo usuário e tipos clr (common language runtime) definidos pelo usuário não podem ser executados SET TRANSACTION ISOLATION LEVEL. Porém, você pode substituir o nível de isolamento com o uso de uma dica de tabela. Para obter mais informações, confira Dicas de tabela (Transact-SQL).

Quando você usa sp_bindsession para associar duas 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 associada a ela.

SET TRANSACTION ISOLATION LEVEL entra em vigor em tempo de execução ou 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 as operações de carregamento em massa, veja Importação e exportação de dados em massa (SQL Server).

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

Nível de isolamento Transact-SQL acesso Acesso ao sistema de arquivos
Leitura não confirmada SQL Server Sem suporte
Leitura confirmada SQL Server SQL Server
Leitura repetível SQL Server Sem suporte
Serializável SQL Server Sem suporte
Ler instantâneo confirmado SQL Server SQL Server
Snapshot SQL Server SQL Server

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

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

Conteúdo relacionado

  • Last updated on