Leer Instantánea Confirmada VS Nivel de Aislamiento de Instantánea

[![Tabla de niveles de aislamiento][2]][2]

Ver el siguiente ejemplo:

Read Committed Snapshot

Cambie la propiedad de la base de datos de la siguiente manera

ALTER DATABASE SQLAuthority
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

Sesión 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 4
WHERE i = 1

Sesión 2

USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 1

La consulta de resultados en la sesión 2 muestra el valor antiguo (1, UNO) porque la transacción actual NO está confirmada. Esta es la manera de evitar el bloqueo y leer los datos confirmados también.

Período de sesiones 1

COMMIT

Sesión 2

USE SQLAuthority
GO
SELECT *
FROM   DemoTable
WHERE i = 1

La consulta de resultados en la sesión 2 no muestra filas porque la fila se actualiza en la sesión 1. Así que de nuevo, estamos viendo datos comprometidos.

Nivel de Aislamiento de instantáneas

Este es el nuevo nivel de aislamiento, que estaba disponible desde SQL Server 2005 en adelante. Para esta característica, hay un cambio necesario en la aplicación, ya que tiene que usar un nuevo nivel de aislamiento.

Cambiar la configuración de la base de datos usando abajo. Necesitamos asegúrese de que no hay ninguna transacción en la base de datos.

ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON

Ahora, también tenemos que cambiar el nivel de aislamiento de la conexión mediante el uso de abajo

Sesión 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 10
WHERE i = 2

Sesión 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 2

Resultado - Incluso si hemos cambiado el valor a 10, todavía veremos el registro antiguo en la sesión 2 (2, DOS).

Ahora, confirmemos la transacción en la sesión 1

Sesión 1

COMMIT

Volvamos a la sesión 2 y ejecutemos seleccione de nuevo.

Sesión 2

SELECT *
FROM   DemoTable
WHERE i = 2

Todavía veremos el registro porque la sesión 2 ha indicado la transacción con aislamiento de instantáneas. A menos que completemos la transacción, no veremos el último registro.

Sesión 2

COMMIT
SELECT *
FROM   DemoTable
WHERE i = 2

Ahora, no debemos ver la fila ya que está actualizada.

Ver: Autoridad SQL, Safari Libros en línea

Ninguna comparación entre Snapshot y Snapshot Read Committed está completa sin una discusión de la temida excepción de "conflicto de actualización de snapshot" que puede ocurrir en Snapshot, pero no Snapshot Read Committed.

En pocas palabras, Snapshot isolation recupera una instantánea de los datos confirmados al inicio de una transacción, y luego usa un bloqueo optimista para lecturas y escrituras. Si, al intentar confirmar una transacción, resulta que algo más cambió algo de lo mismo datos, la base de datos revertirá toda la transacción y generará un error que causa una excepción de conflicto de actualización de instantáneas en el código de llamada. Esto se debe a que la versión de los datos afectados por la transacción no es la misma al final de la transacción que al principio.

Snapshot Read Committed no sufre este problema porque utiliza el bloqueo de escrituras (escrituras pesimistas) y obtiene información de la versión de snapshot de todos los datos confirmados en la estadística de cada declaración.

La posibilidad de que ocurran conflictos de actualización de instantáneas en Snapshot y NO en Snapshot Read Committed es una diferencia extremadamente significativa entre los dos.

Sigue siendo relevante, comenzando con los comentarios de Bill, leí más y tomé notas que podrían ser útiles para otra persona.

De forma predeterminada, las instrucciones individuales (incluido "SELECT") funcionan con datos " confirmados "(READ COMMITTED), la pregunta es: ¿esperan que los datos estén" inactivos " y evitan que otros trabajen al leer?

Configuración mediante clic derecho en la base de datos "Propiedades - > Opciones - > Varios":

Concurrencia / Bloqueo: Se lee Instantánea Confirmada En [por defecto desactivado, debe estar encendido]:

• Use SNAPSHOT para select (read), no espere a otros, ni los bloquee.
• Operación de efectos sin cambio de código
• ALTER DATABASE SET READ_COMMITTED_SNAPSHOT[ON|OFF]
• SELECCIONE name,is_read_committed_snapshot_on DESDE sys.bases de datos

Consistencia: Permitir el aislamiento de instantáneas [defaults off, discutible-OK off]:

• Permitir que el cliente solicite INSTANTÁNEAS a través de instrucciones SQL (transacciones).
• El código debe solicitar instantáneas de "transacción" (como SET TRANSACTION...)
• ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION[ON|OFF]
• SELECCIONE name,is_read_committed_snapshot_on DESDE sys.bases de datos

A la pregunta: no es uno o el otro entre Read Committed Snapshot y Allow Snapshot Isolation. Son dos casos de Instantáneas, y pueden estar activadas o desactivadas de forma independiente, con Permitir el aislamiento de instantáneas un poco más de un tema avanzado. Permitir el aislamiento de instantáneas permite que el código vaya un paso más allá controlando la tierra de instantáneas.

El problema parece claro si piensa en una fila: por defecto el sistema no tiene copia, por lo que un lector tiene que esperar si alguien más está escribiendo, y un escritor también tiene que esperar si alguien más está leyendo – la fila debe bloquearse todo el tiempo. Al habilitar "Is Read Committed Snapshot On" se activa la base de datos para admitir "snapshot copies" para evitar estos bloqueos.

Divagando...

En mi opinión" Is Read Committed Snapshot On "debería ser" TRUE "para cualquier base de datos MS SQLServer normal, y que es una optimización prematura que envíe" FALSE " por defecto.

Sin embargo, me han dicho que el bloqueo de una fila empeora no solo porque puede estar abordando varias filas en las tablas, sino porque en SQL Server los bloqueos de fila se implementan utilizando bloqueos de nivel de "bloque" (bloqueando filas aleatorias asociadas por proximidad de almacenamiento) y que hay un umbral donde múltiples bloqueos activan la tabla bloqueo-presumiblemente optimizaciones de rendimiento más "optimistas" con el riesgo de bloquear problemas en bases de datos ocupadas.