Las sugerencias de la utilización de la tabla de SQL Server nos muestran que son un tipo especial de comando explícito que se utiliza fundamentalmente con el propósito de anular el comportamiento predeterminado del optimizador de consultas de SQL Server durante el proceso de la ejecución de consultas T-SQL. Esto se logra cuando se utiliza y se aplica un método de bloqueo específico, un índice específico o una operación de procesamiento de consultas, al efectuar dicha búsqueda de índice o exploración de tabla, para ello debe ser utilizada por el optimizador de consultas de SQL Server para construir el plan de ejecución de consultas. Es importante indicar que las sugerencias de tabla se pueden agregar a la cláusula FROM de la consulta T-SQL, afectando la tabla o la vista a la que se hace referencia en la cláusula FROM solamente.

Una de las sugerencias de tabla que permanentemente es más utilizada en las instrucciones SELECT T-SQL es la sugerencia WITH (NOLOCK) Esto nos indica que el nivel de aislamiento de transacción predeterminado en SQL Server es el nivel de aislamiento READ COMMITTED, en el que la recuperación de los datos cambiantes se bloqueará hasta que se confirmen estos cambios. Por esta razón es que kla sugerencia de tabla WITH (NOLOCK) es permanentemente utilizada para anular el nivel de aislamiento de transacción predeterminado de la tabla o las tablas dentro de la vista en una consulta específica, por ello vale considerar que al permitir que el usuario podrá recuperar los datos sin verse afectado por los bloqueos, en los datos solicitados, e razón y a causa de otro proceso que lo está cambiando. De esta manera, la consulta consumirá menos memoria al mantener bloqueos contra esos datos. Es importante mencionar que además de eso, no se producirá ningún punto muerto en las consultas que solicitan los mismos datos de esa tabla, lo que permite un mayor nivel de mayor concurrencia debido a una menor huella. En otras palabras, la sugerencia de tabla WITH (NOLOCK) permite que se recupere las filas sin esperar a las otras consultas, que están leyendo o modificando los mismos datos, para finalizar su procesamiento. Vale la pena mencionar que esto es similar al nivel de aislamiento de transacción READ UNCOMMITTED, lo que permite que la consulta vea los cambios de datos antes y previamente de usted pueda confirmar la transacción que lo está cambiando. El nivel de aislamiento de la transacción se puede establecer globalmente en el nivel de conexión mediante el comando SET TRANSACTION ISOLATION LEVEL de T-SQL, como se verá más adelante en este artículo.

Es importante mencionar que, aunque la sugerencia de tabla NOLOCK, que es similar a todas las demás sugerencias de tabla, la misma se puede usar sin usar la palabra clave WITH, Microsoft anunció que omitir la palabra clave WITH es una característica obsoleta y se eliminará de las futuras versiones de Microsoft SQL Server. Consiguientemente, dicho esto, es mejor incluir la palabra clave WITH al especificar las sugerencias de la tabla. Esta situación nos permitirá obtener una ventaja de utilizar la palabra clave WITH es que puede especificar varias sugerencias de tabla utilizando la palabra clave WITH en la misma tabla.

En general, usted debe recordar que al usar sugerencias de tabla explícitas con frecuencia se considera una mala práctica que generalmente debe evitar. Recuerde que para la sugerencia de la tabla NOLOCK específicamente, leer datos no confirmados que podrían revertirse después de haberlos leído puede conducir a una Lectura sucia, que puede ocurrir al leer los datos que se están modificando o eliminando durante la lectura de datos no confirmados, de modo que el riesgo severo es que los datos que lees podrían resultar ser diferentes, o nunca haber existido.

La sugerencia de tabla WITH (NOLOCK) también conduce a Nonrepeatable reads; En esta ocasión podemos indicar que esta lectura se produce cuando es necesario leer los mismos datos varias veces debido a la circunstancia de que los mismos datos cambian durante estas lecturas. En este caso, leerá varias versiones de la misma fila.

Las lecturas fantasmas también pueden ser el resultado del uso de la sugerencia de tabla WITH (NOLOCK), debido a que en este caso se tiene que obtendrá más registros cuando se revierta la transacción que está insertando nuevos registros, o menos registros cuando se revierta la transacción que está eliminando datos existentes. Otro problema que puede ocurrir cuando otras transacciones mueven datos que aún no ha leído a una ubicación que ya ha sido previamente escaneada, o ha agregado nuevas páginas a la ubicación que ya se escaneó. En este caso, la situación derivará en que se perderán estos registros y no los verá en el resultado devuelto. Si otra transacción mueve los datos que ya ha escaneado a una nueva ubicación que aún no ha leído, los leerá dos veces Considerando además, que como los datos solicitados se pueden mover o eliminar durante el proceso de lectura, en la misma se puede enfrentar el siguiente error:

Mensaje 601, Nivel 12, Estado 1
No se pudo continuar la exploración con NOLOCK debido al movimiento de datos.

La sugerencia de tabla WITH (NOLOCK) es una buena idea cuando el sistema usa transacciones explícitas en gran medida, lo que además permite observar el bloqueo de la lectura de datos con mucha frecuencia. La sugerencia de tabla WITH (NOLOCK) se usa cuando se trabaja con sistemas que aceptan datos no sincronizados, como los sistemas de informes.

Para comprender el uso de la sugerencia de tabla WITH (NOLOCK) en la práctica, deberemos crear una nueva tabla utilizando la siguiente declaración CREATE TABLE T-SQL:

Después de crear la tabla, esta misma la llenaremos con 100 mil filas para fines de prueba, utilizando ApexSQL Generate, generador de datos de prueba SQL, tal como se muestra en la siguiente instantánea:

En esta ocasión podremos observar que una vez que la tabla esté lista, simularemos un escenario de bloqueo, en el que se ejecutará una transacción de actualización dentro de una transacción que comenzará y no se confirmará o revertirá. Por esta razón es que la siguiente instrucción BEGIN TRAN T-SQL iniciará la transacción que ejecutará la siguiente instrucción UPDATE en la tabla LockTestDemo en la sesión SQL número 53, sin cerrar la transacción al confirmarla o revertirla

Con los datos de la tabla bloqueados por la transacción, vamos a ejecutar otra instrucción SELECT, bajo la sesión SQL número 54, que va a recuperar datos de la tabla LockTestDemo, usando la instrucción SELECT a continuación:

Verá que la instrucción SELECT anterior va a tardar mucho más tiempo sin recuperar ningún registro. Verificando qué está bloqueando esa consulta SELECT usando el comando sp_who2 con el número de sesión para las declaraciones SELECT y UPDATE:

El resultado le va a mostrar que la transacción abierta anteriormente no está realizando ninguna acción, ya que la instrucción UPDATE se ejecutó correctamente. Pero debido al hecho de que la transacción aún no se ha confirmado o revertido, sigue bloqueando otras consultas que intentan obtener datos de esa tabla. Y la instrucción SELECT que se ejecuta en la sesión 54 está bloqueada por esa transacción que se ejecuta en la sesión 53, como se muestra en el resultado a continuación:

Considere que la instrucción SELECT anterior seguirá esperando que la transacción se cancele, se confirme o se revierta para obtener las filas solicitadas de esa tabla. En esta situación usted puede evitar que la transacción que se ejecuta en la sesión 53 bloquee otras consultas eliminando esa sesión utilizando el comando KILL a continuación:

O de otra forma, simplemente confirme o revierta esa transacción, para ello usted deberá estar ejecutando el comando COMMIT o ROLLBACK en la misma sesión de la transacción, si corresponde, tal como se muestra a continuación:

Una vez que se libere el bloqueo, usted podrá ver que las filas solicitadas se recuperarán directamente de la instrucción SELECT como se muestra en los resultados a continuación:

Es importante mencionar que la solución anterior no siempre es preferible o aplicable, por ejemplo, en situaciones en las que la transacción que bloquea nuestras consultas es crítica y la misma no es fácil de eliminar o revertir, o fundamentalmente cuando no tiene control sobre las transacciones de otros dentro de la base de datos. Por estas razones, en este caso, la sugerencia de tabla WITH (NOLOCK) es útil aquí, si puede tolerar el riesgo de lecturas sucias o inconsistencia de datos. Como se mencionó anteriormente, es importante observar que la sugerencia de tabla WITH (NOLOCK) le permite leer los datos que se han cambiado, pero que aún los mismos no se han confirmado en la base de datos. Si usted ejecuta la misma instrucción SELECT sin matar, comprometer o revertir la transacción UPDATE, pero esta vez agregando la sugerencia de tabla WITH (NOLOCK) al nombre de la tabla en la instrucción SELECT como se muestra a continuación:

Luego, a continuación, usted deberá verificar el estado de la instrucción SELECT utilizando el comando sp_who2. En este momento usted verá que la consulta se está ejecutando sin esperar a que la transacción de ACTUALIZACIÓN se complete con éxito y libere el bloqueo en la tabla, tal como se muestra en la instantánea a continuación:

Recuerde que la sugerencia de tabla WITH (NOLOCK) funciona igual que la sugerencia de tabla READUNCOMMITTED , por esta razón es que nos permite recuperar los datos que se han cambiado pero que aún no se han confirmado. La misma instrucción SELECT se puede modificar para usar la sugerencia de tabla READUNCOMMITTED como se muestra a continuación:

Usted deberá observar que, recuperando los datos solicitados directamente, sin esperar a que la instrucción UPDATE libere el bloqueo que realizó en la tabla, se puede ver que está devolviendo el mismo resultado que se muestra en el conjunto de resultados a continuación:

Tenga en cuenta que las sugerencias de tabla WITH (NOLOCK) y READUNCOMMITTED solo se podrán usar con las instrucciones SELECT. Si intenta usar la sugerencia de tabla WITH (NOLOCK) en la instrucción DELETE, usted obtendrá un error, mostrando que tanto las sugerencias de tabla WITH (NOLOCK) como READUNCOMMITTED no les están permitidas con UPDATE, INSERT, DELETE o MERGE en sentencias TSQL, tal como se muestra a continuación:

Es importante mencionar que en lugar de permitir una lectura sucia en el nivel de consulta utilizando las sugerencias de tabla WITH (NOLOCK) y READUNCOMMITTED, usted puede cambiar el nivel de aislamiento de la transacción en el nivel de conexión para que la misma en nivel READ UNCOMMITTED este siendo utilizado por la instrucción SET TRANSACTION ISOLATION LEVEL T-SQL en la instrucción que sigue a continuación:

Es importante observar que Esta consulta también recuperará los mismos datos directamente, sin que utilice ninguna sugerencia de tabla y sin esperar a que la instrucción UPDATE libere el bloqueo que realizó en la tabla, tal como se muestra en el conjunto de resultados a continuación:

A partir de los resultados anteriores, usted puede pensar que esta es la solución perfecta para tales escenarios, donde obtendrá los datos solicitados más rápido, sin esperar a que se comprometan otras operaciones, asumiendo el riesgo de no tener datos precisos. Pero, deberá preguntarse también si ¿la consulta SELECT que utiliza la sugerencia de tabla WITH (NOLOCK) afectará negativamente a otros procesos en el servidor SQL? Por este motivo es que, para obtener la respuesta, primero deberemos verificar qué tipo de bloqueos se otorgará a la sugerencia de tabla WITH (NOLOCK) durante su ejecución. Para este fin se puede lograr simplemente ejecutando el comando sp_lock con el número de sesión de la consulta en ejecución, mientras tanto se ejecuta la consulta, como se muestra a continuación:

Verá por el resultado que la consulta que utiliza la sugerencia de tabla WITH (NOLOCK) recibirá los tipos de bloqueo S y Sch-S , como se muestra en el resultado a continuación:

A partir del resultado anterior, se puede advertir que la sugerencia de tabla WITH (NOLOCK) le permitirá de manera importante el bloqueo de acceso compartido (S) en el nivel de la base de datos. El bloqueo de acceso compartido (S) se usa para la operación de lectura, lo que nos permitirá observar que las transacciones concurrentes lean datos bajo control de concurrencia pesimista, evitando que otras transacciones modifiquen el recurso bloqueado mientras existen bloqueos compartidos (S) en ese recurso, hasta que ese bloqueo se libere tan pronto a medida que se completa la operación de lectura.

El segundo tipo de bloqueo que se otorga y permite a la consulta utilizando la sugerencia de tabla WITH (NOLOCK) es el bloqueo de estabilidad del esquema (Sch-S). Este bloqueo no impedirá que ninguna otra transacción pueda de cualquier manera acceder a los recursos, excepto las operaciones DDL concurrentes y las operaciones DML concurrentes que adquieren bloqueos de modificación de esquema (Sch-M) en la misma tabla, que se bloquearán mientras. La consulta está en el proceso de estar siendo ejecutado. Esto realmente tiene sentido, ya que no necesita comenzar a leer datos de la tabla, debido a que luego otra transacción cambia la estructura de esa tabla durante el proceso de recuperación de datos. ES muy relevante mencionar que el Motor de base de datos de SQL Server usa los bloqueos de modificación de esquema (Sch-M) mientras procesa los comandos del lenguaje de definición de datos (DDL), tales como agregar una nueva columna, descartar una columna existente, descartar o reconstruir índices, para evitar el acceso concurrente a la tabla, hasta que se libere el bloqueo.

¡Mi consulta NOLOCK está bloqueando!

Esta situación es muy importante de mencionar ya que esto significa que el nombre NOLOCK no siempre es 100 % exacto. El uso de la sugerencia de tabla WITH (NOLOCK), que contiene el bloqueo de estabilidad de esquema (Sch_S), puede bloquear otras consultas que intentan adquirir un bloqueo de modificación de esquema (Sch-M) en esa tabla. Por consiguiente, es un tema crítico que debe tener en cuenta si hay muchos usuarios que ejecutan sus consultas SELECT utilizando la sugerencia de tabla WITH (NOLOCK), ya que de esta forma se tiene lo que le impide realizar cambios en el esquema o mantenimiento de la tabla en los índices de la tabla, bloqueados por el bloqueo de estabilidad del esquema (Sch_S).

Suponga que necesitamos ejecutar la siguiente instrucción SELECT, que está utilizando la sugerencia de tabla WITH (NOLOCK), en la sesión número 53:

Al mismo tiempo, nosotros deberemos ejecutar la consulta a continuación, lo que sigue es colocar un índice en la misma tabla y volver a crearlo, bajo la sesión número58:

Luego, al verificar el estado de ambas consultas utilizando el comando sp_who2, usted podrá observar del resultado que, la instrucción SELECT que está utilizando la sugerencia de tabla WITH (NOLOCK) y la sesión en ejecución número 53, está bloqueando el proceso DROP/CREATE INDEX que se ejecuta bajo sesión número 58, tal como se muestra claramente a continuación:

Si en este proceso verificamos los bloqueos que realiza cada consulta, utilizando el objeto del sistema sys.dm_tran_locks como en la consulta a continuación:

Usted verá que el proceso DROP/CREATE INDEX que se ejecuta bajo la sesión número 58 está esperando esperando adquirir el esquema modificatorio del l tipo de bloqueo (Sch-M). Esto ocurre debido al hecho de que el esquema modificatorio de bloqueo (Sch-M) no se puede adquirir mientras que el bloqueo de estabilidad de esquema (Sch_S) que ya se otorgó a la instrucción SELECT que se ejecuta bajo la sesión número 53, ya existe como se muestra en la instantánea a continuación:

¡Mi consulta NOLOCK está bloqueada!

Por el contrario, de manera inversa dado que la sugerencia de tabla WITH (NOLOCK) adquiere el tipo de bloqueo de estabilidad de esquema (Sch-S), la instrucción SELECT que está utilizando la sugerencia de tabla WITH (NOLOCK) se bloqueará en el caso de que si se realiza una modificación de esquema en esa tabla. Supongamos que ejecutamos la siguiente instrucción ALTER TABLE T-SQL para cambiar el tamaño de la columna EmpAddress en la tabla LockTestDemo, en la sesión número 53:

Al mismo tiempo, la siguiente instrucción SELECT que usa la sugerencia de tabla WITH (NOLOCK) se ejecutará con el número de sesión 54:

Es importante realizar la verificación del estado de ambas consultas utilizando los comandos sp_who2 a continuación:

Usted puede ver que la instrucción SELECT que se ejecuta en la sesión 54 está bloqueada por la instrucción ALTER TABLE la misma que se ejecuta en la sesión 54, como se muestra a continuación:

Luego, de forma posterior, verifique los bloqueos que realiza cada consulta, utilizando el objeto del sistema sys.dm_tran_locks como en la consulta a continuación:

Queda claro que del resultado devuelto que, la instrucción SELECT que utiliza la sugerencia de tabla WITH (NOLOCK) se ejecutara bajo la sesión número 54, estará esperando adquirir el esquema de bloqueo de estabilidad (Sch_S), debido al hecho de que la estabilidad del esquema, el bloqueo (Sch-S) no se puede adquirir mientras que el bloqueo de modificación de esquema (Sch_M), que ya se otorgó a la instrucción ALTER que se ejecuta bajo la sesión número 53, ya existe como se muestra en la siguiente instantánea

Usted de esta manera puede imaginar la situación cuando está programando una gran cantidad de informes por la noche, los mismos que están utilizando la sugerencia de tabla WITH (NOLOCK) solo para estar seguro. Al mismo tiempo, hay trabajos de mantenimiento que también están programados para reconstruir índices muy fragmentados en la misma tabla.

Hay una serie de mejores prácticas y sugerencias que puede seguir con el objeto de que pueda evitar los problemas que puede enfrentar al usar la sugerencia de tabla WITH (NOLOCK). Tales sugerencias incluyen:

• Incluya solo las columnas que realmente se requieren en su consulta SELECT
• Asegúrese de que su transacción sea corta, separando las diferentes operaciones entre sí. Por ejemplo, no incluya una enorme cantidad de la instrucción SELECT entre dos operaciones UPDATE
• Intenta encontrar una alternativa a los cursores
• Tenga cuidado de utilizar y beneficiarse de la opción WAIT_AT_LOW_PRIORITY recientemente definida para hacer una reconstrucción en línea de los índices
• Estudie bien los cronogramas de informes vs mantenimientos
• Tenga cuidado de utilizar y beneficiarse de las diferentes soluciones de alta disponibilidad de SQL Server para fines de informes, tales como:
  • Configure las réplicas secundarias de Grupos de disponibilidad siempre activados para que puedan leerse y úselas para generar informes
  • Cree instantáneas de la base de datos cuando use la duplicación de la base de datos de SQL Server y úsela para informar
  • Use la base de datos de suscriptores de SQL Server Replication para informar
  • Use la base de datos secundaria del envío de registros de SQL Server para informar

• Acerca de
• Últimas entradas

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Últimas entradas de Ahmad Yaseen (ver todo)

• Restricciones en SQL Server: SQL NOT NULL, UNIQUE y SQL PRIMARY KEY - December 16, 2019
• Operaciones de copia de seguridad, truncamiento y reducción de registros de transacciones de SQL Server - November 4, 2019
• Qué elegir al asignar valores a las variables de SQL Server: sentencias SET vs SELECT T-SQL - November 4, 2019