La Syntaxis para definir el nivel de aislamiento en SQL Server

SET TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED 

Especifica que las instrucciones pueden leer filas que han sido modificadas por otras transacciones pero todavía no se han confirmado.

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. 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. Cuando se establece esta opción, es posible leer las modificaciones no confirmadas, denominadas lecturas de datos sucios. 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 y en todas las instrucciones SELECT 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:

• El nivel de aislamiento READ COMMITTED con la opción de base de datos READ_COMMITTED_SNAPSHOT se establece en ON.
• El nivel de aislamiento SNAPSHOT.

| READ COMMITTED

Especifica que las instrucciones no pueden leer datos que hayan sido 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 del valor de la opción de base de datos READ_COMMITTED_SNAPSHOT:

• Si READ_COMMITTED_SNAPSHOT se establece en OFF (valor predeterminado), el Motor de base de datos utiliza bloqueos compartidos para impedir que otras transacciones modifiquen las filas mientras la transacción actual esté ejecutando 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 liberará. Los bloqueos de fila se liberan antes de que se procese la fila siguiente. Bloqueos de página se liberan cuando se lee la página siguiente y bloqueos de tabla se liberan cuando termina la instrucción.

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.

| REPEATABLE 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.

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. De esta forma, se evita que otras transacciones modifiquen las filas que han sido leídas por 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 ejecutar la instrucción, recuperará las filas nuevas, dando como resultado lecturas fantasma. 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. Utilice esta opción solamente cuando sea necesario.

| SNAPSHOT

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. La transacción únicamente puede reconocer las modificaciones de datos confirmadas antes del comienzo de la misma. 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. 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.

Las transacciones SNAPSHOT no solicitan bloqueos al leer los datos, excepto cuando se recupera una base de datos. Las transacciones SNAPSHOT que leen datos no bloquean la escritura de datos de otras transacciones. Las transacciones que escriben datos no bloquean la lectura de datos de las transacciones SNAPSHOT.

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. La transacción SNAPSHOT se bloquea hasta que finalice la reversión de esa transacción. El bloqueo se libera justo después de haberse concedido.

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. 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.

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á. 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. Una transacción se inicia la primera vez que obtiene acceso a los datos.

Una transacción que se ejecuta en el nivel de aislamiento SNAPSHOT puede ver los cambios realizados por esa transacción. 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.

| SERIALIZABLE

Especifica lo siguiente:

• Las instrucciones no pueden leer datos que hayan sido 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 la transacción actual finalice.
• 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.

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 por segunda vez, leerá 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 instrucciones SELECT de la transacción.