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

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada do AzureAzure Synapse AnalyticsSistema de Plataforma Analítica (PDW)Banco de Dados SQL no Microsoft Fabric Preview

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

Transact-SQL convenções de sintaxe

Sintaxe

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

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

Sintaxe para o Azure Synapse Analytics e o Parallel Data Warehouse.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Arguments

LEIA SEM COMPROMISSO

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

As transações executadas 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 as linhas podem aparecer ou desaparecer no conjunto de dados antes do final da transação. Esta opção tem o mesmo efeito que a configuração NOLOCK em todas as tabelas em todas as SELECT instruções de uma transação. Este é o nível de isolamento menos restritivo.

No SQL Server, você também pode minimizar a contenção de bloqueio enquanto protege as transações de leituras sujas de modificações de dados não confirmadas usando:

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

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

LER COMPROMETIDO

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

O comportamento de READ COMMITTED depende da configuração da READ_COMMITTED_SNAPSHOT opção de 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 impedem que a instrução leia 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 os bloqueios de tabela são liberados quando a instrução é concluída.

• 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 existiam no início da instrução. Os bloqueios não são usados para proteger os dados contra atualizações de outras transações.

  • READ_COMMITTED_SNAPSHOT ON é o padrão no Banco de Dados SQL do Azure e no Banco de Dados SQL no Microsoft Fabric Preview.

O isolamento de instantâneo suporta 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 READ_COMMITTED_SNAPSHOT opção de banco de dados é ON, você pode usar a dica READCOMMITTEDLOCK de tabela para solicitar bloqueio compartilhado em vez de controle de versão de linha para instruções individuais em transações executadas no READ COMMITTED nível de isolamento.

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 extrato na transação e são mantidos até que a transação seja concluída. Isso impede que outras transações modifiquem quaisquer linhas que foram lidas pela transação atual. Outras transações podem inserir novas linhas que correspondam às condições de pesquisa dos extratos emitidos pela transação atual. Se a transação atual tentar novamente a instrução, ela recuperará as novas linhas, o que resulta 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 do que o nível de isolamento padrão READ COMMITTED . Utilize esta opção apenas quando necessário.

INSTANTÂNEO

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 só pode reconhecer modificações de dados que foram 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 as 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 como 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 As transações de leitura de dados não impedem que outras transações gravem dados. As transações que gravam dados não impedem SNAPSHOT que as transações leiam 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 seja revertida. O bloqueio é liberado imediatamente após ser concedido.

A ALLOW_SNAPSHOT_ISOLATION opção de banco de dados deve ser definida como ON antes que você possa iniciar uma transação que usa 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 como ON em cada banco de dados.

Uma transação não pode ser definida como SNAPSHOT um 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 começar no SNAPSHOT nível de isolamento, você poderá alterá-la para outro nível de isolamento e, em seguida, voltar para SNAPSHOT. Uma transação é iniciada na primeira vez que acessa dados.

Uma transação executada no SNAPSHOT nível de isolamento pode exibir as alterações feitas por essa transação. Por exemplo, se a transação executa um UPDATE em uma tabela e, em seguida, emite uma SELECT instrução em relação à mesma tabela, os dados modificados são incluídos no conjunto de resultados.

SERIALIZÁVEL

Especifica as seguintes condições:

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

• Nenhuma outra transação pode modificar os 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.

Os bloqueios de intervalo são colocados no intervalo de valores de chave que correspondem às condições de pesquisa de cada instrução executada em uma transação. Isso impede que outras transações atualizem ou insiram quaisquer linhas que se qualificariam 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. Este é o mais restritivo dos níveis de isolamento porque bloqueia intervalos inteiros de chaves e mantém os bloqueios até que a transação seja concluída. Como a simultaneidade é menor, use essa opção somente quando necessário. Esta opção tem o mesmo efeito que a configuração HOLDLOCK em todas as tabelas em todas as SELECT instruções de uma transação.

Observações

Apenas uma das opções de nível de isolamento pode ser definida de cada vez e permanece definida para essa conexão até que 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 FROM de tabela na cláusula de uma instrução especifique um comportamento diferente de bloqueio ou controle de versão para uma tabela.

Os níveis de isolamento de transação definem o tipo de bloqueios adquiridos em operações de leitura. Os 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 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 depois de lida, a transação adquire um bloqueio exclusivo para proteger essa linha, e o bloqueio exclusivo é 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 compartilhado será convertido em um bloqueio de linha exclusivo.

Com uma exceção, você pode alternar de um nível de isolamento para outro a qualquer momento durante uma transação. A exceção ocorre quando se muda de qualquer nível de isolamento para isolamento SNAPSHOT . 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. Os recursos lidos antes da alteração continuam a ser protegidos de acordo com as regras do nível anterior. Por exemplo, se uma transação mudou de READ COMMITTED para SERIALIZABLE, os bloqueios compartilhados adquiridos após a alteração agora sã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.

Quando você usa sp_bindsession para vincular duas sessões, cada sessão mantém sua configuração de nível de isolamento. O uso 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 outras sessões vinculadas a ela.

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

Operações otimizadas de carga em massa em consultas de bloco de pilhas que estão sendo executadas nos seguintes níveis de isolamento:

• SNAPSHOT
• READ UNCOMMITTED
• READ COMMITTED Usando o controle de versão de linha

Por outro lado, as consultas executadas nesses níveis de isolamento bloqueiam operações otimizadas de carga em massa em heaps. Para obter mais informações sobre operações de carregamento em massa, consulte Importação e exportação em massa de dados (SQL Server).

Os bancos de dados habilitados para FILESTREAM suportam os seguintes níveis de isolamento de transação.

Examples

O exemplo a seguir define o TRANSACTION ISOLATION LEVEL para a sessão. Para cada instrução Transact-SQL a seguir, o SQL Server mantém todos os bloqueios compartilhados até o final 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

• ALTERAR BASE DE DADOS (Transact-SQL)
• DBCC USEROPTIONS (Transact-SQL)
• SELECIONAR (Transact-SQL)
• Instruções SET (Transact-SQL)
• Dicas de mesa (Transact-SQL)