SET TRANSACTION ISOLATION LEVEL (Transact-SQL)SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

ESTE TÓPICO APLICA-SE A: simSQL Server (a partir de 2008)simBanco de Dados SQL do Microsoft AzuresimAzure SQL Data Warehouse simParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Controla o comportamento de bloqueio e do controle de versão de linha das instruções Transact-SQLTransact-SQL emitidas por uma conexão com o SQL ServerSQL Server.Controls the locking and row versioning behavior of Transact-SQLTransact-SQL statements issued by a connection to SQL ServerSQL Server.

Ícone de link do tópico Convenções da sintaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

-- Syntax for SQL Server and Azure SQL Database

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

ArgumentosArguments

READ UNCOMMITTEDREAD UNCOMMITTED
Especifica que as instruções podem ler linhas que foram modificadas por outras transações, mas que ainda não foram confirmadas.Specifies that statements can read rows that have been modified by other transactions but not yet committed.

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.Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. 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.READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. Quando essa opção está definida, é possível ler modificações não confirmadas, chamadas de leituras sujas.When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Os valores nos dados podem ser alterados e linhas podem aparecer ou desaparecer do conjunto de dados antes do término da transação.Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. Essa opção tem o mesmo efeito de definir NOLOCK em todas as tabelas em todas as instruções SELECT em uma transação.This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. Esse é o menos restritivo dos níveis de isolamento.This is the least restrictive of the isolation levels.

No SQL ServerSQL 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:In SQL ServerSQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

  • O nível de isolamento READ COMMITTED com a opção de banco de dados READ_COMMITTED_SNAPSHOT definida como ON.The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

  • O nível de isolamento SNAPSHOT.The SNAPSHOT isolation level.

    READ COMMITTEDREAD 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.Specifies that statements cannot read data that has been modified but not committed by other transactions. Isso impede leituras sujas.This prevents dirty reads. 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.Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. Essa é a opção padrão do SQL ServerSQL Server.This option is the SQL ServerSQL Server default.

    O comportamento de READ COMMITTED depende da configuração da opção de banco de dados READ_COMMITTED_SNAPSHOT:The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

  • Se READ_COMMITTED_SNAPSHOT estiver definido como OFF (o padrão), o Mecanismo de Banco de DadosDatabase Engine usará bloqueios compartilhados para impedir que outras transações modifiquem linhas enquanto a transação atual estiver executando uma operação de leitura.If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Mecanismo de Banco de DadosDatabase Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. 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.The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. O tipo de bloqueio compartilhado determina quando ele será liberado.The shared lock type determines when it will be released. Os bloqueios de linha são liberados antes que a próxima linha seja processada.Row locks are released before the next row is processed. 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 é concluída.Page locks are released when the next page is read, and table locks are released when the statement finishes.

    Observação

    Se READ_COMMITTED_SNAPSHOT estiver definido como ON, o Mecanismo de Banco de DadosDatabase Engine 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.If READ_COMMITTED_SNAPSHOT is set to ON, the Mecanismo de Banco de DadosDatabase Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Não são usados bloqueios para proteger os dados contra atualizações efetuadas por outras transações.Locks are not used to protect the data from updates by other transactions.

    O isolamento de instantâneo oferece suporte a dados FILESTREAM.Snapshot isolation supports FILESTREAM data. No modo de isolamento de instantâneos, 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.Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

    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.When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ COMMITTED isolation level.

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.When you set the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. Não deve haver nenhuma outra conexão aberta no banco de dados até que ALTER DATABASE esteja concluído.There must be no other open connection in the database until ALTER DATABASE is complete. O banco de dados não precisa estar no modo do usuário único.The database does not have to be in single-user mode.

REPEATABLE READREPEATABLE 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.Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

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.Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. Isso impede que outras transações modifiquem qualquer linha que tenha sido lida pela transação atual.This prevents other transactions from modifying any rows that have been read by the current transaction. Outras transações podem inserir novas linhas que correspondam às condições de pesquisa das instruções emitidas pela transação atual.Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. Então, se a transação atual tentar a instrução novamente, ela recuperará as novas linhas, o que resultará em leituras fantasmas.If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. 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.Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use essa opção apenas quando necessário.Use this option only when necessary.

SNAPSHOTSNAPSHOT
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.Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. A transação pode reconhecer apenas modificações de dados que estavam confirmadas antes do início da transação.The transaction can only recognize data modifications that were committed before the start of the transaction. 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.Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. 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.The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Exceto quando um banco de dados está sendo recuperado, as transações SNAPSHOT não exigeem bloqueios ao ler dados.Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. Transações SNAPSHOT que leem dados não bloqueiam outras transações de gravar dados.SNAPSHOT transactions reading data do not block other transactions from writing data. Transações que gravam dados não bloqueiam transações SNAPSHOT de ler dados.Transactions writing data do not block SNAPSHOT transactions from reading data.

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.During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. A transação SNAPSHOT será bloqueada até que aquela transação seja revertida.The SNAPSHOT transaction is blocked until that transaction has been rolled back. O bloqueio será liberado tão logo seja concedido.The lock is released immediately after it has been granted.

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.The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. 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.If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

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.A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. 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.If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. Uma transação é iniciada na primeira vez em que ela acessa dados.A transaction starts the first time it accesses data.

Uma transação em execução sob o nível de isolamento SNAPSHOT pode exibir as alterações feitas por essa transação.A transaction running under SNAPSHOT isolation level can view changes made by that transaction. 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.For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

Observação

No modo de isolamento de instantâneos, 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 da instrução.Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction, not at the start of the statement.

SERIALIZABLESERIALIZABLE
Especifica o seguinte:Specifies the following:

  • As instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações.Statements cannot read data that has been modified but not yet committed by other transactions.

  • Nenhuma outra transação pode modificar dados lidos pela transação atual até que a transação atual seja concluída.No other transactions can modify data that has been read by the current transaction until the current transaction completes.

  • 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.Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

    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.Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. 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.This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. 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.This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. Os bloqueios de intervalo são mantidos até que a transação seja concluída.The range locks are held until the transaction completes. Esse é o mais restritivo dos níveis de isolamento, pois ele bloqueia intervalos de chaves inteiros até que a transação seja concluída.This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Como a simultaneidade é menor, use essa opção apenas quando necessário.Because concurrency is lower, use this option only when necessary. Essa opção tem o mesmo efeito de definir HOLDLOCK em todas as tabelas em todas as instruções SELECT de uma transação.This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

ComentáriosRemarks

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.Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. 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.All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

Os níveis de isolamento da transação definem o tipo de bloqueio adquirido em operações de leitura.The transaction isolation levels define the type of locks acquired on read operations. 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.Shared locks acquired for READ COMMITTED or REPEATABLE READ are generally row locks, although the row locks can be escalated to page or table locks if a significant number of the rows in a page or table are referenced by the read. 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.If a row is modified by the transaction after it has been read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes. 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.For example, if a REPEATABLE READ transaction has a shared lock on a row, and the transaction then modifies the row, the shared row lock is converted to an exclusive row lock.

A não ser por uma exceção, é possível alternar de um nível de isolamento para outro durante uma transação.With one exception, you can switch from one isolation level to another at any time during a transaction. A exceção ocorre ao alterar de qualquer nível de isolamento para o isolamento SNAPSHOT.The exception occurs when changing from any isolation level to SNAPSHOT isolation. Isso faz com que a transação falhe e seja revertida.Doing this causes the transaction to fail and roll back. Porém, é possível alterar uma transação iniciada em isolamento SNAPSHOT para qualquer outro nível de isolamento.However, you can change a transaction started in SNAPSHOT isolation to any other isolation level.

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.When you change a transaction from one isolation level to another, resources that are read after the change are protected according to the rules of the new level. Recursos lidos antes da alteração continuam sendo protegidos de acordo com as regras do nível anterior.Resources that are read before the change continue to be protected according to the rules of the previous level. 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.For example, if a transaction changed from READ COMMITTED to SERIALIZABLE, the shared locks acquired after the change are now held until the end of the transaction.

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.If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. 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.For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

Observação

Funções e tipos CLR (Common Language Runtime) definidos pelo usuário não podem executar SET TRANSACTION ISOLATION LEVEL.User-defined functions and common language runtime (CLR) user-defined types cannot execute SET TRANSACTION ISOLATION LEVEL. Porém, você pode substituir o nível de isolamento com o uso de uma dica de tabela.However, you can override the isolation level by using a table hint. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).For more information, see Table Hints (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.When you use sp_bindsession to bind two sessions, each session retains its isolation level setting. 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.Using SET TRANSACTION ISOLATION LEVEL to change the isolation level setting of one session does not affect the setting of any other sessions bound to it.

SET TRANSACTION ISOLATION LEVEL entra em vigor na execução ou em tempo de execução, e não em tempo de análise.SET TRANSACTION ISOLATION LEVEL takes effect at execute or run time, and not at parse time.

Operações de carregamento em massa otimizadas em heaps bloqueiam consultas executadas com os seguintes níveis de isolamento:Optimized bulk load operations on heaps block queries that are running under the following isolation levels:

  • SNAPSHOTSNAPSHOT

  • READ UNCOMMITTEDREAD UNCOMMITTED

  • READ COMMITTED usando controle de versão de linhaREAD COMMITTED using row versioning

    De modo oposto, consultas executadas com esses níveis de isolamento bloqueiam operações de carregamento em massa otimizados em heaps.Conversely, queries that run under these isolation levels block optimized bulk load operations on heaps. Para obter mais informações sobre as operações de carregamento em massa, consulte importação e exportação de dados ( SQL Server ) .For more information about bulk load operations, see Bulk Import and Export of Data (SQL Server).

    Bancos de dados habilitados por FILESTREAM dão suporte aos seguintes níveis de isolamento de transação.FILESTREAM-enabled databases support the following transaction isolation levels.

Nível de isolamentoIsolation level Acesso ao Transact SQLTransact SQL access Acesso ao sistema de arquivosFile system access
Leitura não confirmadaRead uncommitted SQL Server 2017SQL Server 2017 Sem suporteUnsupported
Leitura confirmadaRead committed SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
Leitura repetidaRepeatable read SQL Server 2017SQL Server 2017 Sem suporteUnsupported
SerializávelSerializable SQL Server 2017SQL Server 2017 Sem suporteUnsupported
Instantâneo de leitura confirmadaRead committed snapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
InstantâneoSnapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017

ExemplosExamples

O exemplo a seguir define o TRANSACTION ISOLATION LEVEL da sessão.The following example sets the TRANSACTION ISOLATION LEVEL for the session. Para cada instrução Transact-SQLTransact-SQL a seguir, o SQL ServerSQL Server mantém todos os bloqueios compartilhados até o término da transação.For each Transact-SQLTransact-SQL statement that follows, SQL ServerSQL Server holds all of the shared locks until the end of the transaction.

USE AdventureWorks2012;  
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émSee Also

ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
DBCC USEROPTIONS ( Transact-SQL ) DBCC USEROPTIONS (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
Instruções SET (Transact-SQL) SET Statements (Transact-SQL)
Dicas de tabela (Transact-SQL)Table Hints (Transact-SQL)