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

ESTE TEMA SE APLICA A: síSQL Server (a partir de 2008)síAzure SQL DatabasesíAzure SQL Data Warehouse síAlmacenamiento de datos paralelos THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Controla el comportamiento del bloqueo y de las versiones de fila de las instrucciones Transact-SQLTransact-SQL emitidas por una conexión a SQL ServerSQL Server.Controls the locking and row versioning behavior of Transact-SQLTransact-SQL statements issued by a connection to SQL ServerSQL Server.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

-- 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 las instrucciones pueden leer filas que han sido modificadas por otras transacciones pero todavía no se han confirmado.Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transacciones que se ejecutan en el nivel READ UNCOMMITTED no emiten bloqueos compartidos para impedir que otras transacciones modifiquen los datos leídos por la transacción actual.Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. Las transacciones READ UNCOMMITTED tampoco se bloquean mediante bloqueos exclusivos que impedirían que la transacción actual leyese las filas modificadas pero no confirmadas por otras transacciones.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. Cuando se establece esta opción, es posible leer las modificaciones no confirmadas, denominadas lecturas de datos sucios.When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Los valores de los datos se pueden cambiar, y las filas pueden aparecer o desaparecer en el conjunto de datos antes de que finalice la transacción.Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. Esta opción tiene el mismo efecto que establecer NOLOCK en todas las tablas y en todas las instrucciones SELECT de una transacción.This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. Se trata del nivel de aislamiento menos restrictivo.This is the least restrictive of the isolation levels.

En SQL ServerSQL Server, también se puede reducir al mínimo la contención de bloqueos y, al mismo tiempo, proteger las transacciones de las lecturas de datos sucios de modificaciones de datos no confirmadas mediante una de estas dos alternativas:In SQL ServerSQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

  • El nivel de aislamiento READ COMMITTED con la opción de base de datos READ_COMMITTED_SNAPSHOT se establece en ON.The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

  • El nivel de aislamiento SNAPSHOT.The SNAPSHOT isolation level.

    READ COMMITTEDREAD COMMITTED
    Especifica que las instrucciones no pueden leer datos que hayan sido modificados, pero no confirmados, por otras transacciones.Specifies that statements cannot read data that has been modified but not committed by other transactions. Esto evita las lecturas de datos sucios.This prevents dirty reads. Otras transacciones pueden cambiar datos entre cada una de las instrucciones de la transacción actual, dando como resultado lecturas no repetibles o datos fantasma.Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. Esta opción es la predeterminada para SQL ServerSQL Server.This option is the SQL ServerSQL Server default.

    El comportamiento de READ COMMITTED depende del valor de la opción de base de datos READ_COMMITTED_SNAPSHOT:The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

  • Si READ_COMMITTED_SNAPSHOT se establece en OFF (valor predeterminado), el Motor de base de datosDatabase Engine utiliza bloqueos compartidos para impedir que otras transacciones modifiquen las filas mientras la transacción actual esté ejecutando una operación de lectura.If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Motor de base de datosDatabase Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. Los bloqueos compartidos impiden también que la instrucción lea las filas modificadas por otras transacciones hasta que la otra transacción haya finalizado.The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. El tipo de bloqueo compartido determina cuándo se liberará.The shared lock type determines when it will be released. Los bloqueos de fila se liberan antes de que se procese la fila siguiente.Row locks are released before the next row is processed. Bloqueos de página se liberan cuando se lee la página siguiente y bloqueos de tabla se liberan cuando termina la instrucción.Page locks are released when the next page is read, and table locks are released when the statement finishes.

    Nota

    Si READ_COMMITTED_SNAPSHOT se establece en ON, el Motor de base de datosDatabase Engine utiliza versiones de fila para presentar a cada instrucción una instantánea coherente, desde el punto de vista transaccional, de los datos tal como se encontraban al comenzar la instrucción.If READ_COMMITTED_SNAPSHOT is set to ON, the Motor de base de datosDatabase 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. No se emplean bloqueos para impedir que otras transacciones actualicen los datos.Locks are not used to protect the data from updates by other transactions.

    El aislamiento de instantánea admite datos FILESTREAM.Snapshot isolation supports FILESTREAM data. En el modo de aislamiento de instantánea, los datos FILESTREAM leídos por cualquier instrucción de una transacción serán la versión coherente, desde el punto de vista transaccional, de los datos existentes al comienzo de la transacción.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.

    Cuando la opción de base de datos READ_COMMITTED_SNAPSHOT es ON, se puede usar la sugerencia de tabla READCOMMITTEDLOCK para solicitar el uso del bloqueo compartido en lugar de versiones de fila para las instrucciones individuales de las transacciones que se ejecutan en el nivel de aislamiento 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.

Nota

Al establecer la opción READ_COMMITTED_SNAPSHOT, solo se permite en la base de datos la conexión que ejecuta el comando ALTER DATABASE.When you set the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. No debe haber ninguna otra conexión de base de datos abierta hasta que ALTER DATABASE haya finalizado.There must be no other open connection in the database until ALTER DATABASE is complete. La base de datos no tiene que estar en modo de usuario único.The database does not have to be in single-user mode.

REPEATABLE READREPEATABLE READ
Especifica que las instrucciones no pueden leer datos que han sido modificados pero aún no confirmados por otras transacciones y que ninguna otra transacción puede modificar los datos leídos por la transacción actual hasta que ésta finalice.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.

Se aplican bloqueos compartidos a todos los datos leídos por cada instrucción de la transacción, y se mantienen hasta que la transacción finaliza.Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. De esta forma, se evita que otras transacciones modifiquen las filas que han sido leídas por la transacción actual.This prevents other transactions from modifying any rows that have been read by the current transaction. Otras transacciones pueden insertar filas nuevas que coincidan con las condiciones de búsqueda de las instrucciones emitidas por la transacción actual.Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. Si la transacción actual vuelve a ejecutar la instrucción, recuperará las filas nuevas, dando como resultado lecturas fantasma.If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Debido a que los bloqueos compartidos se mantienen hasta el final de la transacción en lugar de liberarse al final de cada instrucción, la simultaneidad es inferior que en el nivel de aislamiento predeterminado READ COMMITTED.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. Utilice esta opción solamente cuando sea necesario.Use this option only when necessary.

SNAPSHOTSNAPSHOT
Especifica que los datos leídos por cualquier instrucción de una transacción serán la versión transaccionalmente coherente de los datos existentes al comienzo de la transacción.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. La transacción únicamente puede reconocer las modificaciones de datos confirmadas antes del comienzo de la misma.The transaction can only recognize data modifications that were committed before the start of the transaction. Las instrucciones que se ejecuten en la transacción actual no verán las modificaciones de datos efectuadas por otras transacciones después del inicio de la transacción actual.Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. El efecto es el mismo que se obtendría si las instrucciones de una transacción obtuviesen una instantánea de los datos confirmados tal como se encontraban al comienzo de la transacción.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.

Las transacciones SNAPSHOT no solicitan bloqueos al leer los datos, excepto cuando se recupera una base de datos.Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. Las transacciones SNAPSHOT que leen datos no bloquean la escritura de datos de otras transacciones.SNAPSHOT transactions reading data do not block other transactions from writing data. Las transacciones que escriben datos no bloquean la lectura de datos de las transacciones SNAPSHOT.Transactions writing data do not block SNAPSHOT transactions from reading data.

Durante la fase de reversión de la recuperación de una base de datos, las transacciones SNAPSHOT solicitan un bloqueo si se intenta leer datos bloqueados por otra transacción que está en proceso de reversión.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. La transacción SNAPSHOT se bloquea hasta que finalice la reversión de esa transacción.The SNAPSHOT transaction is blocked until that transaction has been rolled back. El bloqueo se libera justo después de haberse concedido.The lock is released immediately after it has been granted.

La opción de base de datos ALLOW_SNAPSHOT_ISOLATION debe establecerse en ON para poder iniciar una transacción que utilice el nivel de aislamiento SNAPSHOT.The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. Si una transacción que utiliza el nivel de aislamiento SNAPSHOT obtiene acceso a datos de varias bases de datos, será necesario establecer ALLOW_SNAPSHOT_ISOLATION en ON en cada una de ellas.If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

No es posible establecer en el nivel de aislamiento SNAPSHOT una transacción que se inició con otro nivel de aislamiento; si lo hace, la cancelará.A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. Si una transacción comienza en el nivel de aislamiento SNAPSHOT, puede cambiarla a otro nivel de aislamiento y, después, de nuevo a SNAPSHOT.If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. Una transacción se inicia la primera vez que obtiene acceso a los datos.A transaction starts the first time it accesses data.

Una transacción que se ejecuta en el nivel de aislamiento SNAPSHOT puede ver los cambios realizados por esa transacción.A transaction running under SNAPSHOT isolation level can view changes made by that transaction. Por ejemplo, si la transacción realiza una operación UPDATE en una tabla y después emite una instrucción SELECT para la misma tabla, los datos modificados se incluirán en el 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.

Nota

En el modo de aislamiento de instantánea, los datos FILESTREAM leídos por cualquier instrucción de una transacción serán la versión coherente, desde el punto de vista transaccional, de los datos existentes al comienzo de la transacción, no al comienzo de la instrucción.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 lo siguiente:Specifies the following:

  • Las instrucciones no pueden leer datos que hayan sido modificados, pero aún no confirmados, por otras transacciones.Statements cannot read data that has been modified but not yet committed by other transactions.

  • Ninguna otra transacción puede modificar los datos leídos por la transacción actual hasta que la transacción actual finalice.No other transactions can modify data that has been read by the current transaction until the current transaction completes.

  • Otras transacciones no pueden insertar filas nuevas con valores de clave que pudieran estar incluidos en el intervalo de claves leído por las instrucciones de la transacción actual hasta que ésta finalice.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.

    Se colocan bloqueos de intervalo en el intervalo de valores de clave que coincidan con las condiciones de búsqueda de cada instrucción ejecutada en una transacción.Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. De esta manera, se impide que otras transacciones actualicen o inserten filas que satisfagan los requisitos de alguna de las instrucciones ejecutadas por la transacción actual.This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. Esto significa que, si alguna de las instrucciones de una transacción se ejecuta por segunda vez, leerá el mismo conjunto de filas.This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. Los bloqueos de intervalo se mantienen hasta que la transacción finaliza.The range locks are held until the transaction completes. Este es el nivel de aislamiento más restrictivo, porque bloquea intervalos de claves completos y mantiene esos bloqueos hasta que la transacción finaliza.This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Al ser menor la simultaneidad, solo se debe utilizar esta opción cuando sea necesario.Because concurrency is lower, use this option only when necessary. Esta opción tiene el mismo efecto que establecer HOLDLOCK en todas las tablas de todas las instrucciones SELECT de la transacción.This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

ComentariosRemarks

Solo es posible establecer una de las opciones de nivel de aislamiento cada vez, y permanecerá activa para la conexión hasta que se cambie explícitamente.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 las operaciones de lectura realizadas dentro de la transacción se rigen por las reglas del nivel de aislamiento especificado, a menos que se utilice una sugerencia de tabla en la cláusula FROM de una instrucción para especificar un comportamiento de bloqueo o versiones diferente para una tabla.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.

Los niveles de aislamiento de transacciones definen el tipo de bloqueo que se adquiere en las operaciones de lectura.The transaction isolation levels define the type of locks acquired on read operations. Los bloqueos compartidos que se adquieren para READ COMMITTED o REPEATABLE READ suelen ser bloqueos de fila, aunque éstos se pueden escalar a bloqueos de página o tabla si la operación de lectura hace referencia a un número significativo de filas de una página o tabla.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. Si la transacción modifica una fila después de haberse leído, la transacción adquiere un bloqueo exclusivo para proteger esa fila, y ese bloqueo exclusivo se mantiene hasta que la transacción finaliza.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 ejemplo, si una transacción REPEATABLE READ tiene un bloqueo compartido en una fila y, después, la transacción modifica esa fila, el bloqueo compartido de fila se convierte en un bloqueo exclusivo de fila.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.

Con una excepción, se puede cambiar de un nivel de aislamiento a otro en cualquier momento de una transacción.With one exception, you can switch from one isolation level to another at any time during a transaction. La excepción se produce cuando se cambia de cualquier nivel de aislamiento al aislamiento SNAPSHOT.The exception occurs when changing from any isolation level to SNAPSHOT isolation. Esta acción generará un error en la transacción y hará que se revierta.Doing this causes the transaction to fail and roll back. Sin embargo, puede cambiar una transacción iniciada en aislamiento SNAPSHOT a cualquier otro nivel de aislamiento.However, you can change a transaction started in SNAPSHOT isolation to any other isolation level.

Cuando se cambia el nivel de aislamiento de una transacción por otro, los recursos leídos después del cambio se protegen de acuerdo con las reglas del nuevo nivel.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. Los recursos leídos antes del cambio siguen estando protegidos en función de las reglas del nivel anterior.Resources that are read before the change continue to be protected according to the rules of the previous level. Por ejemplo, si una transacción ha cambiado de READ COMMITTED a SERIALIZABLE, los bloqueos compartidos adquiridos después del cambio se mantienen hasta el final de la transacción.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.

Si se ejecuta SET TRANSACTION ISOLATION LEVEL en un procedimiento almacenado o un desencadenador, cuando el objeto devuelve el control, el nivel de aislamiento se restablece en el nivel en efecto cuando se invocó el objeto.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 ejemplo, si se establece REPEATABLE READ en un lote y, después, este lote llama a un procedimiento almacenado que establece el nivel de aislamiento en SERIALIZABLE, el valor del nivel de aislamiento vuelve a REPEATABLE READ cuando el procedimiento almacenado devuelve el control al 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.

Nota

Las funciones definidas por el usuario y los tipos definidos por el usuario para CLR (Common Language Runtime) no pueden ejecutar SET TRANSACTION ISOLATION LEVEL.User-defined functions and common language runtime (CLR) user-defined types cannot execute SET TRANSACTION ISOLATION LEVEL. Sin embargo, se puede anular este nivel de aislamiento mediante una sugerencia de tabla.However, you can override the isolation level by using a table hint. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).For more information, see Table Hints (Transact-SQL).

Cuando se utiliza sp_bindsession para enlazar dos sesiones, cada sesión mantiene su nivel de aislamiento.When you use sp_bindsession to bind two sessions, each session retains its isolation level setting. Si se utiliza SET TRANSACTION ISOLATION LEVEL para cambiar el valor del nivel de aislamiento de una sesión, no se verán afectados los valores de las sesiones enlazadas a ella.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 se aplica en tiempo de ejecución, no en tiempo de análisis.SET TRANSACTION ISOLATION LEVEL takes effect at execute or run time, and not at parse time.

Las operaciones de carga masiva optimizadas que se realizan en montones bloquean las consultas que se ejecutan con los siguientes niveles de aislamiento:Optimized bulk load operations on heaps block queries that are running under the following isolation levels:

  • SNAPSHOTSNAPSHOT

  • READ UNCOMMITTEDREAD UNCOMMITTED

  • READ COMMITTED con versiones de filaREAD COMMITTED using row versioning

    A la inversa, las consultas que se ejecutan con estos niveles de aislamiento bloquean las operaciones de carga masiva optimizadas que se realizan en montones:Conversely, queries that run under these isolation levels block optimized bulk load operations on heaps. Para obtener más información acerca de las operaciones de carga masiva, vea importación en bloque y exportar datos ( SQL Server ) .For more information about bulk load operations, see Bulk Import and Export of Data (SQL Server).

    Las bases de datos habilitadas con FILESTREAM admiten los niveles de aislamiento de transacción siguientes:FILESTREAM-enabled databases support the following transaction isolation levels.

Nivel de aislamientoIsolation level Transact el acceso a SQLTransact SQL access Acceso al sistema de archivosFile system access
Lectura no confirmadaRead uncommitted SQL Server 2017SQL Server 2017 No compatibleUnsupported
Lectura confirmadaRead committed SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
Lectura repetibleRepeatable read SQL Server 2017SQL Server 2017 No compatibleUnsupported
SerializableSerializable SQL Server 2017SQL Server 2017 No compatibleUnsupported
Instantánea de lectura confirmadaRead committed snapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
SnapshotSnapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017

EjemplosExamples

En el ejemplo siguiente se establece TRANSACTION ISOLATION LEVEL para la sesión.The following example sets the TRANSACTION ISOLATION LEVEL for the session. En cada instrucción Transact-SQLTransact-SQL siguiente, SQL ServerSQL Server mantendrá todos los bloqueos compartidos hasta el final de la transacción.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  

Vea tambiénSee Also

ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
DBCC USEROPTIONS ( Transact-SQL ) DBCC USEROPTIONS (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
Instrucciones SET (Transact-SQL) SET Statements (Transact-SQL)
Sugerencias de tabla (Transact-SQL)Table Hints (Transact-SQL)