Las lecturas no confirmadas te pueden hacer leer muchas filas de más y/o de menos

Cuando se hacen lectura sucias (tanto eligiendo el nivel de aislamiento READ UNCOMMITTED, o utilizando el hint NOLOCK), estás leyendo datos que pueden ser inconsistentes desde el punto de vista transaccional; en este post vamos a reproducir un caso extremo para que te convenzas de los “desbarajustes” que se pueden estar haciendo.

Qué son las lecturas sucias

Partiendo de una tabla de pedidos, con las siguientes filas:

y dándose las siguientes operaciones ordenadas en el tiempo:

Puede suceder que el Usuario 2 lea datos que realmente no están confirmados.

Sin embargo, el Usuario 3, mientras la transacción no esté confirmada, no podrá leer el dato, y cuando la transacción esté confirmada, podrá leer el dato real.

Para aliviar la situación de que el Usuario 3 está bloqueado en SQL Server 2005 aparece el concepto de lectura confirmada de instantánea (READ COMMITTED SNAPSHOT) que se menciona al final de este post.

Preparando una demo “impactante”

Dada una tabla con XX filas, vamos a definir dos conexiones haciendo las siguientes operaciones:

• una conexión contará el número de filas de la tabla
• otra conexión actualizará la tabla entera

Para ello tenemos la siguiente estructura de tabla:

--
-- bloqueos (lectura sucia)
--
-- Solid Quality Mentors 2010
-- http://creativecommons.org/licenses/by-sa/3.0/
-- Attribution-NonCommercial-ShareAlike 3.0
--
-- https://blogvisionarios.com/elrinconDelDBA
-- http://siquelnet.com
--
use Adventureworks
go

if exists (select * from sys.tables where name='bloqueos')
    drop table bloqueos;
go

create table bloqueos (
    id  int not null identity,
    relleno char(8000),
    mi_guid uniqueidentifier default(newid())
)
go

create unique clustered index ci_bloqueos_malo 
on bloqueos (mi_guid);
create unique nonclustered index nci_bloqueos_id
on bloqueos (id);

En la que insertaremos 10.000 filas – comprobamos espacio usado y número de página asignadas:

insert bloqueos (relleno)
    select 'a' from master.dbo.fn_nums (10000)
go
 
select si.name, page_count, record_count
from sys.dm_db_index_physical_stats (
    db_id()
    , OBJECT_ID ('bloqueos'), null, null, 'detailed') v
    join sys.indexes si
    on v.index_id = si.index_id 
    and v.object_id = si.object_id 
where index_level = 0
go

sp_spaceused bloqueos

Donde obtenemos el siguiente resultado:

Nota como hemos “conseguido” que el índice clustered tenga sólo una fila por página (fíjate que en la creación de la tabla dijimos que la columna relleno ocupaba 8000 bytes). Además, fíjate que el índice nonclustered es muy “ligero” y apenas ocupa 50 páginas (la clave es integer  — 4 bytes por referencia).

Seguramente te has dado cuenta que hemos cometido una irresponsabilidad al definir como clustered una columna newid(); lo hemos hecho así para provocar que sentencias TSQL sucesivas que veréis después, fuercen a que la fila tratada sea muy volátil en cuanto a su ubicación lógica en la tabla.

Además, vamos a crear una tabla de auditoria, donde vamos a registrar las operaciones que se van a ir haciendo:

-- auditoria
if exists (select * from sys.tables where name='auditoria')
    drop table auditoria;
go

create table auditoria (
    id  int not null identity primary key,
    quien varchar(100),
    operacion varchar(100),
    cuando datetime default (getdate()),
    filas int
)

Provocando el escenario deseado para la demo

vamos a tener dos usuarios realizando las siguientes operaciones:

• Usuario 1: actualización completa de la tabla renovando el valor de la columna GUID.

• la función NEWID() genera valores muy “dispersos”, lo que provocará que las filas se muevan con un ratio muy alto de unas página a otras
• Usuario 2: bucle iterativo contando cuantas filas hay en la tabla en modo lectura sucia

• forzaremos que se use el índice clustered (donde están los datos) para mostrar el efecto deseado

Para ello, necesitas dos conexiones de SSMS; en las demostraciones de bloqueos suele ser útil ver las dos conexiones en vertical (menú Window, New Vertical Tab Group).

En una de las conexiones tendrás el siguiente código (la que va a hace los SELECT):

-- Conexión 1: actualizaciones
--
insert auditoria 
(quien, operacion, filas)
select 'Conexion1', 'INICIO UPDATE', NULL
go

update bloqueos
set mi_guid = newid()
go

insert auditoria 
(quien, operacion, filas)
select 'Conexion1', 'FIN UPDATE', NULL

En la segunda conexión, tendrás el siguiente código (el del UPDATE):

-- Conexión 2: contar filas -- por CI
--
truncate table auditoria
go

while 1 = 1
begin
    insert auditoria 
        (quien, operacion, filas)
    select 
        'Conexion2', 'SELECT (NOLOCK)', count(*) 
    from bloqueos with (nolock, index=ci_bloqueos_malo)
    waitfor delay '00:00:00.5'
end

A continuación pones en marcha la consulta que lee (bucle iterativo), y después ejecutas la primera consulta (la del UPDATE). Cuando finalice la consulta del UPDATE, paras (le das a STOP) a la consulta de la SELECT.

Ahora ejecuta la siguiente consulta, y verás la sorpresa:

select *
from auditoria

Las líneas de color Rojo, marcan la “zona” de tiempo en la que se está ejecutando el UPDATE.

Fíjate que existen una cuantas filas insertadas, en las que el número de filas contadas ha sido distinto de 10.000 que es el número de filas que tiene la tabla. ¿cual es la causa de esto?

Mientras la consulta SELECT se estaba procesando, SQL Server estaba leyendo filas de más o de menos; ¿cómo ha sucedido esto? mejor un dibujo 🙂

• En color Rojo, se representa en Index Scan, que lee todas las página para contar cuantas fijas hay
• En color Azul, se representa la actualización de una fija, que la mueve de una página a otra
• Cuando se estaba leyendo a la altura del asterisco (*), se movió la fija a la ubicación donde finaliza la flecha azul
• lo cual implica que cuando el Index Scan pasó por donde se movió la fila ya leída, lo leyera otra vez

Como ejercicio, te propongo que simules el proceso reemplazando el código de Conexión 2 por el siguiente:

-- Conexión 3: contar filas por NCI
--
while 1 = 1
begin
    insert auditoria 
        (quien, operacion, filas)
    select 
        'Conexion3', 'SELECT (NOLOCK) -- NCI', count(*) 
    from bloqueos with (nolock)
    waitfor delay '00:00:00.5'
end

y verás como siempre se leerán el mismo número de filas; ¿por qué razón? porque el UPDATE de la columna mi_guid, no proboca cambio de ubicación de las filas en el índice nonclustered (columna id)

Conclusión

• Este efecto “no deseado”, no sucederá si se usas un índice que no sufre movimientos de páginas (como el NCI de Conexión 3).
• Tampoco sucederá si no utilizas el hint NOLOCK y trabajas en modo de aislamiento LECTURA CONFIRMADA.
• SI sucederá si tienes configurada la base de datos en modo READ_COMMITTED_SNAPSHOT y realizas lecturas SUCIAS (NOLOCK).
• Tampoco sucederá si tienes configurada la base de datos en modo READ_COMMITTED_SNAPSHOT y trabajas en modo de aislamiento LECTURA CONFIRMADA.