miércoles, 11 de septiembre de 2013

SQL Server: Hint NOLOCK

En el artículo de hoy explicaremos el

hint NOLOCK

. Si recordáis el artículo

SQL Server: Hint Maxdop

os comentaba que existen tres tipos de hints,

Join Hints

,

Query Hints

y

Table Hints

; bien, el

hint NOLOCK

es del tipo Table (también conocidos como

Lock Hints

) por lo que se aplica en consultas de tipo SELECT, INSERT (sólo cuando contiene una cláusula SELECT), DELETE, UPDATE y MERGE, invalidando el comportamiento predeterminado del optimizador de consultas mientras dura la instrucción DML (Data Manipulation Language) especificando un tipo de bloqueo, uno o más índices,... Estos

hints

se especifican en la cláusula FROM de la instrucción mediante la cláusula WITH y sólo afectan a la tabla o vista a la que se hace referencia en esa cláusula. Podemos encontrar más información al respecto en el siguiente enlace del MSDN: Table Hints.

El

hint NOLOCK

es equivalente al

READUNCOMITTED

y básicamente lo que permite son

'dirty reads'

(aunque también podrían producirse lecturas no repetibles y lecturas fantasma), lo podemos ver fácilmente con un ejemplo en el que utilizaremos la base de datos AdventureWorks:

  1. Abrimos una nueva consulta en SSMS y ejecutamos una sentencia SELECT:
    -- Buscamos todos los empleados solteros
    SELECT EmployeeID, LoginID, Title, MaritalStatus 
    FROM HumanResources.Employee 
    WHERE MaritalStatus = 'S'
    
    Obtenemos el siguiente resultado (podemos ver que el empleado con ID 2, Kevin, está soltero):


  2. Abrimos otra nueva consulta en SSMS y ejecutamos una sentencia UPDATE dentro de una transacción:
    -- Actualizamos el estado civil del empleado con ID 2
    BEGIN TRANSACTION
    UPDATE HumanResources.Employee
    SET MaritalStatus = 'M'
    WHERE EmployeeID = 2
    -- No finalizamos la transacción, podrá acabar con COMMIT o ROLLBACK
    
  3. Volvemos a lanzar la consulta del punto 1, ahora la consulta queda pendiente de ejecución ya que hay una transacción sobre los datos a los que se refiere.


    Esto es así porque el nivel de aislamiento (

    ISOLATION LEVEL

    ) establecido es

    'READ COMMITTED'

    (las instrucciones no pueden leer datos que hayan sido modificados pero no confirmados por otras transacciones); es el valor por defecto en

    SQL Server

    .

  4. Detenemos la consulta en ejecución del apartado anterior y modificamos la sentencia SELECT del punto 1 añadiendo el

    hint NOLOCK

    :
    -- Buscamos todos los empleados solteros
    SELECT EmployeeID, LoginID, Title, MaritalStatus 
    FROM HumanResources.Employee WITH (NOLOCK)
    WHERE MaritalStatus = 'S'
    
    Obtenemos el siguiente resultado (podemos ver que el empleado con ID 2, Kevin, no aparece, a pesar de no haber confirmado la transacción):


  5. Si ahora hiciéramos ROLLBACK en la sentencia UPDATE del paso 2 los resultados de la sentencia SELECT del paso 4 serían erróneos, tendríamos una 'lectura sucia' (

    dirty read

    ) originada por el uso del

    hint NOLOCK

    .

Visto el uso del hint NOLOCK en el ejemplo anterior deberíamos tener en cuenta algunas consideraciones:
  • Lo primero que nos preguntaremos es, si el

    hint NOLOCK

    puede producir

    'dirty reads'

    , ¿para qué sirve? Como ya he comentado en algún otro artículo se debe ir con especial cuidado en el uso de

    hints

    , sea cual sea, ya que alteran la normal ejecución del optimizador de consultas y puede producir resultados no deseados. En el caso del

    hint NOLOCK

    y tomando de partida el ejemplo anterior se me ocurren 2 casos en los que puede sernos útil:

    • Supongamos que tenemos un nº de empleados mayor que 300, obtenemos los datos sobre el estado civil de los empleados (solteros, casados,...) y se muestra un gráfico de tipo pastel con los porcentajes de cada tipo. El estado civil no es algo que cambie frecuentemente, podemos lanzar la consulta SELECT con el

      hint NOLOCK

      y aunque los datos no sean 100% exactos el gráfico apenas se verá afectado.

    • Necesitamos un listado donde figure ID de empleado, nombre y estado civil; lanzando la consulta con el

      hint NOLOCK

      obtendremos el listado ignorando los posibles bloqueos de la tabla. Id, nombre del empleado y estado civil son datos que no deberían variar con frecuencia y algún dato no del todo correcto quizás no suponga un problema en este caso.

    Normalmente para la construcción de gráficos y extracción de listados es donde puede tener sentido (pero no siempre) el uso del

    hint NOLOCK

    . Se debe ir con especial cuidado en NO utilizar el hint NOLOCK en una sentencia SELECT si luego con los datos obtenidos se realizan INSERTs / UPDATEs / DELETEs ya que podemos estar generando datos corruptos.

  • En uno de los puntos anteriores comentaba que la consulta quedaba en ejecución porque el

    nivel de aislamiento

    era

    'READ COMMITTED'

    ; si queremos saber el

    nivel de aislamiento

    de la conexión que estamos usando debemos ejecutar la consulta:
    -- Obtención nivel de aislamiento
    DBCC useroptions
    
    Obtendremos el siguiente resultado (podemos ver que el

    nivel de aislamiento

    es

    'READ COMMITED'

    ):


  • El uso del

    hint NOLOCK

    sólo está permitido en las sentencias de tipo SELECT. Si lo añadimos a sentencias de tipo INSERT, DELETE o UPDATE obtendremos el error correspondiente, veamos un ejemplo:
    -- Actualizamos estado civil del empleado usando el hint NOLOCK
    UPDATE HumanResources.Employee WITH (NOLOCK)
    SET MaritalStatus = 'M'
    WHERE EmployeeID = 2
    
    Al ejecutar la consulta anterior obtendremos el siguiente mensaje de error:
    Mens. 1018, Nivel 15, Estado 1, Línea 1
    Sintaxis incorrecta cerca de 'NOLOCK'. Si se va a usar como parte de una sugerencia de tabla, es necesario escribir una palabra clave WITH y paréntesis. Vea los Libros en pantalla de SQL Server para conocer la sintaxis correcta.
    

  • Cuando usamos el

    hint NOLOCK

    en una sentencia SELECT se produce sobre la tabla en cuestión un bloqueo en modo Sch-S (bloqueo de estabilidad del esquema); por tanto, si dentro de una transacción se modifica la estructura de la tabla y lanzamos una sentencia SELECT con el

    hint NOLOCK

    sin que haya finalizado la transacción la consulta SELECT se quedará en espera. Veamos un ejemplo:
    -- Modificamos estructura de la tabla
    BEGIN TRANSACTION
    ALTER TABLE HumanResources.Employee 
    ADD Columna_Test VARCHAR(20) NULL ;
    -- La transacción podrá acabar con COMMIT o ROLLBACK
    
    -- En otra consulta lanzamos el SELECT, quedará en ejecución 
    -- hasta que finalice la transacción de la consulta anterior
    SELECT EmployeeID, LoginID, Title, MaritalStatus 
    FROM HumanResources.Employee WITH (NOLOCK)
    WHERE MaritalStatus = 'S'
    

  • Tampoco deberíamos confundir el

    hint NOLOCK

    con los

    hints READPAST

    o

    NOWAIT

    . Probablemente en un próximo artículo explique estos dos últimos y las diferencias entre los tres.

  • Si queremos aplicar el

    hint NOLOCK

    a una consulta con varias tablas o a varias consultas SELECT consecutivas deberíamos hacerlo de la siguiente manera, modificando el nivel de aislamiento:
    -- Cambiamos nivel de aislamiento para permitir lecturas
    -- no confirmadas
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
    SELECT *
    FROM Table1
    INNER JOIN Table2 ON Table1.c1 = Table2.c2
    
    SELECT *
    FROM Table3
    
    -- Restauramos el nivel de aislamiento anterior
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    

Hasta aquí el artículo de hoy, recordad que dentro de

areaTIC

podéis encontrar otros artículos interesantes, no dudéis en consultar nuestro archivo, también podéis seguirnos por RSS o las principales redes sociales (twitter, facebook, linkedin...).


LECTURAS RELACIONADAS RECOMENDADAS POR AREATIC.NET

No hay comentarios:

Publicar un comentario en la entrada