Sifiso W. Ndlovu

Entendiendo el Impacto de las sugerencias NOLOCK y WITH NOLOCK en SQL Server

May 28, 2018 by

De vez en cuando, los administradores de bases de datos SQL Server se tienen desacuerdos con sus contrapartes de desarrollo de aplicaciones – particularmente cuando se trata de algunas de las prácticas de desarrollo Transact SQL (T-SQL) de estos últimos. Una de mis primeras observaciones cuando me uní a mi empleador actual es que casi todos los scripts T-SQL escritos por desarrolladores de aplicaciones usan la sugerencia NOLOCK. De todos modos, de las interacciones que he tenido con estos estimados desarrolladores, no parece que entiendan cómo funciona la sugerencia NOLOCK. Además, aunque parecen estar al tanto de la distinción entre NOLOCK y WITH NOLOCK, tampoco parecen entender cómo los dos difieren uno del otro. En este artículo exploro los detalles internos de la sugerencia NOLOCK y examino las implicaciones de omitir la palabra reservada WITH.

Entendiendo la sugerencia NOLOCK

EL comportamiento por defecto en SQL Server es que cada consulta adquiera su propio bloqueo previo a leer datos desde una tabla dada. Este comportamiento asegura que usted sólo está leyendo datos consolidados. De todos modos, la sugerencia NOLOCK le permite instruir al optimizador de consultas para leer una tabla dada sin obtener un bloqueo exclusivo o compartido. Los beneficios de consultar datos usando la sugerencia NOLOCK es que requiere menos memoria y previene que ocurran bloqueos con otras consultas que puedan estar leyendo datos similares. La única desventaja es que usar la sugerencia NOLOCK puede accidentalmente resultar en la lectura de datos no consolidados “sucios”. En las siguientes secciones, proveeré ejemplos prácticos para usar las sugerencias NOLOCK y WITH NOLOCK.

Para propósitos de esta discusión, usaré a tabla de SQL Server 2016_FIFABallonDOr que contiene a los principales 3 futbolistas nominados para el Balón de Oro de la FIFA en 2016. La vista previa de esta tabla de SQL Server es mostrada en la Tabla 1.

Nominado Club Número de camiseta Votos Fecha de nacimiento Lugar de nacimiento Nacionalidad Altura
Antoine Griezmann Atletico Madrid 7 198 21 de Marzo de 1991 Mâcon Francesa 1.75 m
Lionel Messi FC Barcelona 10 316 24 de Junio de 1987 Rosario Argentina 1.70 m
Cristiano Ronaldo Real Madrid 7 745 5 de Febrero de 1985 Funchal Portugal 1.85 m

Tabla 1

Ahora asumamos que, habiendo visto el número de camiseta de sus oponentes, Lionel Messi luego decide cambiar su número de camiseta de 10 a 7. Para reflejar este cambio, necesitaríamos correr una sentencia UPDATE contra nuestra tabla de SQL Server. El script UPDATE de muestra que necesitaríamos correr es mostrado en el Script 1.

Script 1

Usted notará que el Script 1 no tiene un correspondiente COMMIT TRANSACTION updateJerseyNr, lo que significa que los cambios que hemos hecho no han sido consolidados al disco aún. Por tanto, si otro usuario de base de datos fuera a intentar leer el valor del número de camiseta de Lionel Messi como se muestra en la Figura 1, recibiría un mensaje Executing query … sin fin.

Figura 1

Bueno, lo que está pasando aquí es que, como parte de recuperar el número de camiseta de Lionel Messi, el script en la Figura 1 necesita primeramente adquirir un bloqueo compartido contra la tabla 2016_FIFABallonDOr, pero termina teniendo que esperar a que el Script 1 complete sus cambios primero.

Figura 2

Aquí es donde la sugerencia NOLOCK puede ser útil, ya que puede permitir la recuperación del número de camiseta de Lionel Messi sin tener que esperar por ningún bloqueo compartido, como se muestra en Script 2 y Figura 3, respectivamente.

Script 2

Figura 3

De todos modos, los peligros de usar la sugerencia NOLOCK es que si la transacción en Script 1 fuera a ser retrotraída, entonces el valor de la camiseta de Lionel Messi retornaría a 10, lo que significa que si corriera nuevamente el Script 2, usted obtendría un valor diferente del que obtuvo antes.

Beneficios de usar la palabra reservada WITH

En términos de consultar el número de camiseta de Lionel Messi añadiendo la palabra reservada WITH en frente de la sugerencia NOLOCK, usted aún recibiría resultados similares como se muestra en el Script 3 y la Figura 4, respectivamente. De hecho, incluso el plan de ejecución y las estadísticas de TIME/IO del Script 3 aún son similares a los del Script 1.

Script 3

Figura 4

Si la salida de los scripts usando las sugerencias NOLOCK y WITH NOLOCK es idéntica, ¿por qué preocuparse acerca de usar el uno en vez del otro? Bueno, resulta que realmente hay muchas diferencias entre los dos:

1. El soporte para sugerencias sin la palabra reservada WITH pronto será deprecado

Como muestra la documentación de Microsoft en la Figura 5, continuar excluyendo la palabra WITH en una sugerencia significa que usted está básicamente incrementando su deuda técnica, ya que tendrá que volver y refactorizar sus scripts una vez que esta funcionalidad es removida en futuras versiones de SQL Server.

Figura 5

2. Especifique múltiples sugerencias de tabla usando la palabra WITH

Aparte del hecho que los desarrolladores de SQL Server básicamente nos han instruido a usar la palabra WITH cuando especificamos sugerencias de tabla, otro beneficio de usar la palabra WITH es que usted puede incluir múltiples sugerencias de tabla contra la misma tabla, como se muestra en el Script 4.

Script 4

Si usted especifica múltiples sugerencias después de haber omitido la palabra reservada WITH, eso simplemente resultará en el error mostrado en el Script 5 y el Script 6, respectivamente.

Script 5

Figura 6

3. Sin la palabra reservada WITH, usted simplemente tiene un Alias de tabla

En este punto, usted debería ya inclinarse hacia siempre especificar la palabra WITH cuando use sugerencias de tabla, pero sólo para convencerle más, omitir la palabra WITH puede tener consecuencias indeseadas. Si usted olvida incluir NOLOCK en los paréntesis, entonces la sugerencia NOLOCK puede ser confundida con un alias de tabla por el optimizador de consultas, lo que significa que la consulta tendría entonces que esperar por el bloqueo compartido antes de comenzar a leer una tabla dada. Por ejemplo, yo olvidé incluir NOLOCK entre los paréntesis y esto llevó a que la consulta se ejecutara infinitamente mientras que esperaba por el bloqueo compartido.

Figura 7

Figura 8

Conclusión

Hay beneficios y desventajas de especificar la sugerencia NOLOCK, y como resultado, no debería ser simplemente incluida en cada script T-SQL sin un entendimiento claro de lo que hace. A pesar de eso, si toma la decisión de usar la sugerencia de tabla NOLOCK, es recomendado que incluya la palabra reservada WITH.

Vea más

Considere estas herramientas gratis para SQL Server que mejoran la productividad del desarrollador de bases de datos.

Referencias


Sifiso W. Ndlovu

Sifiso W. Ndlovu

Sifiso es un profesional basado en Johannesburgo dentro de un amplio rango de Competencias en Tecnología Microsoft como SQL Server y Visual Studio Application Lifecycle Management.
Él es un miembro del Grupo de Usuarios de SQL de Johannesburgo y tiene un grado de maestría en MCom IT Management de la Universidad de Johannesburgo.
Actualmente, él trabaja para Clientele Life como Asistente Administrativo de Soluciones de Software de Negocios.

Ver todas las publicaciones de Sifiso W. Ndlovu
Sifiso W. Ndlovu
7,778 Views