sql server - transaction - Nivel de aislamiento confirmado de lectura en el servidor SQL para una declaración única

read uncommitted

Question

Diga, tengo una mesa de personas y solo tiene 1 fila -

id = 1, name = ''foo''

En una conexión

select p1.id, p1.name, p2.name
from person p1
join person p2 on p1.id = p2.id

En otra conexión al mismo tiempo:

update person set name = ''bar'' where person.id = 1

P1: ¿Alguna vez es posible, para mi selección, devolver un resultado como este en función del calendario de la declaración de actualización?

id = 1, p1.name = ''foo'', p2.name = ''bar''

Ninguna de las conexiones usa una transacción explícita y ambas usan el nivel de aislamiento de transacción predeterminado READ COMMITTED.

La cuestión es realmente ayudarme a entender si los bloqueos adquiridos al comienzo de un enunciado sql continúan existiendo hasta que se completa la declaración, o si es posible que un enunciado libere el candado y vuelva a adquirir el candado para el mismo fila si se usa dos veces en la misma declaración?

P2: ¿La respuesta a la pregunta cambia si el set read_committed_snapshot on está establecido en el db?

Answer 1

P1: Sí, esto es perfectamente posible al menos en teoría. read committed solo garantiza que no lea datos sucios, no hace promesas sobre la coherencia. En el nivel de lectura confirmada, los bloqueos compartidos se liberan tan pronto como se leen los datos (no al final de la transacción o incluso al final de la declaración)

Q2: Sí, la respuesta a esta pregunta cambiaría si read_committed_snapshot está read_committed_snapshot . Entonces se le garantizará la coherencia del nivel de la declaración . Me resulta difícil encontrar una fuente en línea que establezca esto sin ambigüedad, pero citando la p.648 de "Microsoft SQL Server 2008 Internals"

Una declaración en RCSI ve todo lo comprometido antes del inicio de la declaración. Cada nueva declaración en la transacción recoge los cambios confirmados más recientes.

Ver también esta publicación de blog de MSDN

Script de configuración

CREATE TABLE person
(
id int primary key,
name varchar(50)
)
INSERT INTO person
values(1, ''foo'');

Conexión 1

while 1=1
update person SET name = CASE WHEN name=''foo'' then ''bar'' ELSE ''foo'' END

Conexión 2

DECLARE @Results TABLE (
id int primary key,
name1 varchar(50),
name2 varchar(50))
while( NOT EXISTS(SELECT *
FROM @Results) )
BEGIN
INSERT INTO @Results
Select p1.id,
p1.name,
p2.name
from person p1
INNER HASH join person p2
on p1.id = p2.id
WHERE p1.name <> p2.name
END
SELECT *
FROM @Results 

Resultados

id name1 name2
----------- ----- -----
1 bar foo

Si observamos los otros tipos de combinación en Profiler, parece que este problema no podría surgir en el join de unión o en el nested loops para esta consulta en particular (no se liberan bloqueos hasta que se adquieran todos) pero sigue siendo read committed que read committed solo garantiza que lo haga no leer datos sucios, no hace promesas sobre la coherencia. En la práctica, es posible que no obtenga este problema para la consulta exacta que ha publicado, ya que SQL Server no elegiría este tipo de combinación de forma predeterminada. Sin embargo, entonces queda solo dependiendo de los detalles de implementación para producir el comportamiento que desea.

NB: si se preguntaba por qué faltan algunas cerraduras de nivel S esta es una optimización explicada aquí .