ESTABLECER NIVEL DE AISLAMIENTO DE TRANSACCIONES (Transact-SQL)

Aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Analítica (PDW)Base de datos SQL en Microsoft Fabric

Controla el comportamiento del bloqueo y de las versiones de fila de las instrucciones Transact-SQL emitidas por una conexión a SQL Server.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para SQL Server, Azure SQL Database y SQL Database en Microsoft Fabric.

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

Sintaxis para Azure Synapse Analytics y Almacenamiento de datos paralelos.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Nota:

Azure Synapse Analytics implementa transacciones ACID. El nivel de aislamiento predeterminado es READ UNCOMMITTED. Para cambiarla, READ COMMITTED SNAPSHOT ISOLATION active ON la READ_COMMITTED_SNAPSHOT opción de base de datos para una base de datos de usuario cuando esté conectada a la master base de datos. Una vez habilitada, todas las transacciones de esta base de datos se ejecutan en READ COMMITTED SNAPSHOT ISOLATION y no se respeta la configuración READ UNCOMMITTED en el nivel de sesión. Para obtener más información, vea Opciones de ALTER DATABASE SET (Transact-SQL).

Argumentos

LECTURA NO CONFIRMADA

Especifica que las instrucciones pueden leer filas modificadas por otras transacciones, pero que aún no se han confirmado.

Las transacciones que se ejecutan en el READ UNCOMMITTED nivel no emiten bloqueos compartidos para evitar que otras transacciones modifiquen los datos leídos por la transacción actual. READ UNCOMMITTED las transacciones tampoco están bloqueadas por bloqueos exclusivos que impedirían que la transacción actual lea las filas modificadas pero no confirmadas por otras transacciones. Cuando se establece esta opción, es posible leer modificaciones no confirmadas, que se denominan lecturas desfasadas. 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. Esta opción tiene el mismo efecto que establecer NOLOCK en todas las tablas de todas las SELECT instrucciones de una transacción. Se trata del nivel de aislamiento menos restrictivo.

En SQL 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:

• Nivel READ COMMITTED de aislamiento con la READ_COMMITTED_SNAPSHOT opción de base de datos establecida en ON.

• Nivel de SNAPSHOT aislamiento. Para obtener más información sobre el aislamiento de instantáneas, vea Aislamiento de instantáneas en SQL Server.

READ COMMITTED (La lectura comprometida)

Especifica que las instrucciones no pueden leer datos modificados pero no confirmados por otras transacciones. Esto evita las lecturas de datos sucios. 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. Esta opción es la predeterminada para SQL Server.

El comportamiento de READ COMMITTED depende de la configuración de la READ_COMMITTED_SNAPSHOT opción de base de datos:

• Si READ_COMMITTED_SNAPSHOT se establece OFF en (el valor predeterminado en SQL Server), el motor de base de datos usa bloqueos compartidos para evitar que otras transacciones modifiquen filas mientras la transacción actual ejecuta una operación de lectura. 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. El tipo de bloqueo compartido determina cuándo se libera. Los bloqueos de fila se liberan antes de que se procese la fila siguiente. Los bloqueos de página se liberan cuando se lee la página siguiente, y los bloqueos de tabla se liberan cuando la instrucción finaliza.

• Si READ_COMMITTED_SNAPSHOT se establece ONen , el motor de base de datos usa el control de versiones de fila para presentar cada instrucción con una instantánea transaccionalmente coherente de los datos tal como existía al principio de la instrucción. Los bloqueos no se usan para proteger los datos de las actualizaciones de otras transacciones.

  • READ_COMMITTED_SNAPSHOT ON es el predeterminado en Azure SQL Database y SQL Database en Microsoft Fabric.

Importante

Elegir un nivel de aislamiento de transacción no afecta a los bloqueos adquiridos para proteger las modificaciones de datos. Siempre se obtiene un bloqueo exclusivo en los datos modificados de una transacción, bloqueo que se mantiene hasta que se completa la transacción, independientemente del nivel de aislamiento seleccionado para la misma. Además, una actualización realizada en el READ COMMITTED nivel de aislamiento usa bloqueos de actualización en las filas de datos seleccionadas, mientras que una actualización realizada en el SNAPSHOT nivel de aislamiento usa versiones de fila para seleccionar las filas que se van a actualizar. En el caso de las operaciones de lectura, los niveles de aislamiento de transacción definen básicamente el nivel de protección contra los efectos de las modificaciones que realizan otras transacciones. Para obtener más información, consulte La Guía de control de versiones de fila y bloqueo de transacciones.

El aislamiento de instantánea admite datos FILESTREAM. En el modo de aislamiento de instantáneas, los datos FILESTREAM leídos por cualquier instrucción de una transacción son la versión transaccionalmente coherente de los datos que existían al principio de la transacción.

Cuando la READ_COMMITTED_SNAPSHOT opción de base de datos es ON, puede usar la READCOMMITTEDLOCK sugerencia de tabla para solicitar bloqueo compartido en lugar de control de versiones de fila para instrucciones individuales en transacciones que se ejecutan en el nivel de READ COMMITTED aislamiento.

Nota:

Cuando se establece la READ_COMMITTED_SNAPSHOT opción , solo se permite la conexión que ejecuta el ALTER DATABASE comando en la base de datos. No debe haber ninguna otra conexión abierta en la base de datos hasta ALTER DATABASE que se complete. La base de datos no tiene que estar en modo de usuario único.

LECTURA REPETIBLE

Especifica que las instrucciones no pueden leer los datos 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 se complete la transacción actual.

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. Esto impide que otras transacciones modifiquen las filas que leyó la transacción actual. Otras transacciones pueden insertar filas nuevas que coincidan con las condiciones de búsqueda de las instrucciones emitidas por la transacción actual. Si la transacción actual vuelve a intentar la instrucción , recupera las nuevas filas, lo que da como resultado lecturas fantasma. Dado que los bloqueos compartidos se mantienen al final de una transacción en lugar de liberarse al final de cada instrucción, la simultaneidad es inferior al nivel de aislamiento predeterminado READ COMMITTED . Utilice esta opción solamente cuando sea necesario.

SNAPSHOT

Especifica que los datos leídos por cualquier instrucción de una transacción son la versión coherente transaccionalmente de los datos que existían al principio de la transacción. La transacción únicamente puede reconocer las modificaciones de datos confirmadas antes del comienzo de la misma. Las modificaciones de datos realizadas por otras transacciones después del inicio de la transacción actual no son visibles para las instrucciones que se ejecutan en la transacción actual. El efecto es como si las instrucciones de una transacción obtienen una instantánea de los datos confirmados tal como se encontraban al inicio de la transacción.

Excepto cuando se recupera una base de datos, SNAPSHOT las transacciones no solicitan bloqueos al leer datos. SNAPSHOT las transacciones que leen datos no impiden que otras transacciones escriban datos. Las transacciones que escriben datos no impiden SNAPSHOT que las transacciones lean datos.

Durante la fase de reversión de una recuperación de base de datos, SNAPSHOT las transacciones solicitan un bloqueo si se intenta leer datos bloqueados por otra transacción que se está reviertiendo. La SNAPSHOT transacción se bloquea hasta que se revierte esa transacción. El bloqueo se libera inmediatamente después de concederlo.

La ALLOW_SNAPSHOT_ISOLATION opción de base de datos debe establecerse en ON para poder iniciar una transacción que use el nivel de SNAPSHOT aislamiento. Si una transacción que usa el SNAPSHOT nivel de aislamiento accede a los datos de varias bases de datos, ALLOW_SNAPSHOT_ISOLATION debe establecerse ON en en cada base de datos.

Una transacción no se puede establecer en el SNAPSHOT nivel de aislamiento que se inició con otro nivel de aislamiento; al hacerlo, la transacción se anula. Si una transacción se inicia en el nivel de SNAPSHOT aislamiento, puede cambiarla a otro nivel de aislamiento y volver a SNAPSHOT. Una transacción se inicia la primera vez que obtiene acceso a los datos.

Una transacción que se ejecuta en SNAPSHOT el nivel de aislamiento puede ver los cambios realizados por esa transacción. Por ejemplo, si la transacción realiza una UPDATE en una tabla y, a continuación, emite una SELECT instrucción en la misma tabla, los datos modificados se incluyen en el conjunto de resultados.

Nota:

En el modo de aislamiento de instantáneas, los datos FILESTREAM leídos por cualquier instrucción de una transacción son la versión transaccionalmente coherente de los datos que existían al principio de la transacción, no al principio de la instrucción.

SERIALIZABLE

Especifica las condiciones siguientes:

• Las instrucciones no pueden leer los datos modificados pero aún no confirmados por otras transacciones.

• Ninguna otra transacción puede modificar los datos leídos por la transacción actual hasta que se complete la transacción actual.

• Otras transacciones no pueden insertar nuevas filas con valores de clave que caerían en el intervalo de claves leídos por las instrucciones de la transacción actual hasta que se complete la transacción actual.

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. 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. Esto significa que si alguna de las instrucciones de una transacción se ejecuta una segunda vez, lee el mismo conjunto de filas. Los bloqueos de intervalo se mantienen hasta que la transacción finaliza. 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. Al ser menor la simultaneidad, solo se debe utilizar esta opción cuando sea necesario. Esta opción tiene el mismo efecto que establecer HOLDLOCK en todas las tablas de todas las SELECT instrucciones de una transacción.

Observaciones

Solo se puede establecer una de las opciones de nivel de aislamiento a la vez y permanece establecida para esa conexión hasta que se cambie explícitamente. Todas las operaciones de lectura realizadas dentro de la transacción funcionan con las reglas para el nivel de aislamiento especificado, a menos que una sugerencia de tabla en la FROM cláusula de una instrucción especifique un comportamiento de control de versiones o bloqueo diferente para una tabla.

Los niveles de aislamiento de transacciones definen el tipo de bloqueo que se adquiere en las operaciones de lectura. Los bloqueos compartidos adquiridos para READ COMMITTED o REPEATABLE READ suelen ser bloqueos de fila, aunque los bloqueos de fila se pueden escalar a bloqueos de página o tabla si la lectura hace referencia a un número significativo de filas de una página o tabla. Si la transacción modifica una fila después de leerla, la transacción adquiere un bloqueo exclusivo para proteger esa fila y el bloqueo exclusivo se conserva hasta que se completa la transacción. Por ejemplo, si una REPEATABLE READ transacción tiene un bloqueo compartido en una fila y, a continuación, modifica la fila, el bloqueo de fila compartido se convierte en un bloqueo de fila exclusivo.

Con una excepción, se puede cambiar de un nivel de aislamiento a otro en cualquier momento de una transacción. La excepción se produce al cambiar de cualquier nivel de aislamiento a SNAPSHOT aislamiento. Esta acción generará un error en la transacción y hará que se revierta. Sin embargo, puede cambiar una transacción iniciada de SNAPSHOT forma aislada a cualquier otro nivel de aislamiento.

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. Los recursos leídos antes del cambio siguen estando protegidos en función de las reglas del nivel anterior. 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.

Si emite SET TRANSACTION ISOLATION LEVEL en un procedimiento almacenado o desencadenador, cuando el objeto devuelve el control del nivel de aislamiento se restablece al nivel en vigor cuando se invocó el objeto. Por ejemplo, si establece REPEATABLE READ en un lote y, a continuación, llama a un procedimiento almacenado que establece el nivel SERIALIZABLEde aislamiento en , la configuración del nivel de aislamiento vuelve a REPEATABLE READ cuando el procedimiento almacenado devuelve el control al lote.

Nota:

Las funciones definidas por el usuario y los tipos definidos por el usuario (CLR) de Common Language Runtime no pueden ejecutar SET TRANSACTION ISOLATION LEVEL. Sin embargo, se puede anular este nivel de aislamiento mediante una sugerencia de tabla. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).

Cuando se usa sp_bindsession para enlazar dos sesiones, cada sesión conserva su configuración de nivel de aislamiento. El uso SET TRANSACTION ISOLATION LEVEL de para cambiar la configuración del nivel de aislamiento de una sesión no afecta a la configuración de ninguna otra sesión enlazada a ella.

SET TRANSACTION ISOLATION LEVEL surte efecto en tiempo de ejecución o ejecución, y no en tiempo de análisis.

Las operaciones de carga masiva optimizadas que se realizan en montones bloquean las consultas que se ejecutan con los siguientes niveles de aislamiento:

• SNAPSHOT
• READ UNCOMMITTED
• READ COMMITTED uso del control de versiones de fila

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: Para más información sobre operaciones de carga masiva, vea Importar y exportar datos en bloque (SQL Server).

Las bases de datos habilitadas con FILESTREAM admiten los niveles de aislamiento de transacción siguientes:

Nivel de aislamiento	acceso Transact-SQL	Acceso al sistema de archivos
Leer sin confirmar	SQL Server	No compatible
Lectura confirmada	SQL Server	SQL Server
Lectura repetible	SQL Server	No compatible
Serializable	SQL Server	No compatible
Instantánea confirmada de lectura	SQL Server	SQL Server
Snapshot	SQL Server	SQL Server

Ejemplos

En el ejemplo siguiente se establece TRANSACTION ISOLATION LEVEL para la sesión. En cada instrucción Transact-SQL siguiente, SQL Server mantendrá todos los bloqueos compartidos hasta el final de la transacción.

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

Contenido relacionado

• BASE DE DATOS ALTER (Transact-SQL)
• OPCIONES DE USUARIO DE DCC (Transact-SQL)
• SELECT (Transact-SQL)
• Instrucciones SET (Transact-SQL)
• Sugerencias de tabla (Transact-SQL)