las sugerencias de tabla de SQL Server son un tipo especial de comando explícito que se usa para anular el comportamiento predeterminado del optimizador de consultas de SQL Server durante la ejecución de la consulta de T-SQL.por el optimizador de consultas de SQL Server para construir el plan de ejecución de consultas., Las sugerencias de tabla se pueden agregar a la cláusula FROM de la consulta T-SQL, afectando a la tabla o a la vista a la que se hace referencia en la cláusula FROM solamente.
una de las sugerencias de tabla más utilizadas en las instrucciones SELECT T-SQL es la sugerencia WITH (NOLOCK). El nivel de aislamiento de transacción predeterminado en SQL Server es el nivel de aislamiento de confirmación de lectura, en el que la recuperación de los datos cambiantes se bloqueará hasta que se confirmen estos cambios., La sugerencia de tabla WITH (NOLOCK) se usa 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, permitiendo al usuario recuperar los datos sin verse afectado por los bloqueos, en los datos solicitados, debido a otro proceso que los está cambiando. De esta manera, la consulta consumirá menos memoria al mantener bloqueos contra esos datos. Además de eso, no se producirá ningún bloqueo contra las consultas, que están solicitando los mismos datos de esa tabla, lo que permite un mayor nivel de concurrencia debido a una huella más baja., En otras palabras, la sugerencia de tabla WITH (NOLOCK) recupera las filas sin esperar a que las otras consultas, que están leyendo o modificando los mismos datos, terminen su procesamiento. Esto es similar al nivel de aislamiento de transacciones no comprometidas de lectura, que permite a la consulta ver los cambios de datos antes de confirmar la transacción que la está cambiando. El nivel de aislamiento de transacción se puede establecer globalmente en el nivel de conexión mediante el comando T-SQL SET TRANSACTION ISOLATION LEVEL, como se verá más adelante en este artículo.,
aunque la sugerencia de tabla NOLOCK, similar a todas las demás sugerencias de tabla, 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 futuras versiones de Microsoft SQL Server. Dicho esto, es mejor incluir la palabra clave WITH al especificar las sugerencias de la tabla. Una ventaja de usar la palabra clave WITH es que puede especificar varias sugerencias de tabla usando la palabra clave WITH contra la misma tabla.
en general, usar consejos explícitos de tabla con frecuencia se considera una mala práctica que generalmente debe evitar., Para la sugerencia de tabla NOLOCK específicamente, leer datos no comprometidos que podrían revertirse después de haberlo leído puede conducir a una lectura sucia, que puede ocurrir al leer los datos que se están modificando o borrando durante la lectura de datos no comprometidos, de modo que los datos que lee podrían ser diferentes, o ni siquiera haber existido.
la sugerencia de tabla WITH (NOLOCK) también conduce a lecturas no repetibles; esta lectura ocurre cuando se requiere leer los mismos datos varias veces y los datos cambian durante estas lecturas. En este caso, leerá varias versiones de la misma fila.,
Las lecturas Phantom también pueden ser el resultado del uso de la sugerencia de tabla WITH(NOLOCK), en la 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 escaneado, o han agregado nuevas páginas a la ubicación que ya escaneó. En este caso, perderá 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, leerá los datos dos veces. Además, como los datos solicitados podrían moverse o eliminarse durante su proceso de lectura, se podría enfrentar el siguiente error:
Msg 601, Nivel 12, Estado 1
no 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 bloquea 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) prácticamente, creemos una nueva tabla utilizando la instrucción CREATE TABLE T-SQL a continuación:
Después de crear la tabla, la llenaremos con filas de 100k para fines de prueba, utilizando ApexSQL Generate, SQL test data generator, como se muestra en la instantánea a continuación:
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á ni revertirá.,
Con los datos de la tabla bloqueada por la transacción, vamos a ejecutar otra instrucción SELECT, en virtud de sesión SQL número 54, que recupera los datos de la LockTestDemo tabla, usando la instrucción SELECT a continuación:
|
1
|
SELECT * FROM LockTestDemo
|
Usted verá que la anterior instrucción SELECT tomará un largo tiempo sin recuperar los registros.,bandadas de que SELECCIONE consulta utilizando el comando sp_who2 con el número de sesión tanto para la selección y la ACTUALIZACIÓN de las declaraciones:
|
1
2
3
|
sp_who2 53
IR
sp_who2 54
|
El resultado mostrará que, previamente abierto de la transacción no es de realizar cualquier acción, como la ACTUALIZACIÓN de la instrucción ejecutada con éxito., Pero debido al hecho de que la transacción no se ha confirmado o revertido todavía, sigue bloqueando otras consultas que están tratando de 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 siguiente resultado:
la instrucción SELECT anterior seguirá esperando que la transacción se elimine, confirme o revierta para obtener las filas solicitadas de esa tabla., el comando COMMIT o ROLLBACK bajo la misma sesión de la transacción, si corresponde, como se muestra a continuación:
Una vez que se libera el bloqueo, verá que las filas solicitadas se recuperarán de la instrucción SELECT directamente como se muestra en los resultados a continuación:
no siempre es preferible o aplicable, por ejemplo, cuando la transacción que está bloqueando nuestras consultas es crítica y no es fácil de eliminar o revertir, o cuando no tiene control sobre las transacciones de otros dentro de la base de datos., 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, la sugerencia de tabla WITH (NOLOCK) le permite leer los datos que se han cambiado, pero que aún no se han enviado a la base de datos., Si ejecuta la misma instrucción SELECT sin matar, confirmar o deshacer la transacción de ACTUALIZACIÓN, pero esta vez añadiendo el WITH (NOLOCK) tabla sugerencia para el nombre de la tabla en la instrucción SELECT, como se muestra a continuación:
|
1
|
SELECT * FROM LockTestDemo WITH (NOLOCK)
|
Luego el control de la instrucción SELECT del estado mediante el comando sp_who2., Verá que la consulta se está ejecutando sin esperar a que la transacción de actualización se complete correctamente y libere el bloqueo en la tabla, como se muestra en la instantánea a continuación:
la sugerencia de tabla con (NOLOCK) funciona igual que la sugerencia de tabla READUNCOMMITTED, lo que nos permite recuperar los datos que se han cambiado pero aún no se han confirmado.,iv>
|
1
|
seleccione * de LockTestDemo con (READUNCOMMITTED)
|
recuperar los datos solicitados directamente, sin esperar a que la instrucción de actualización libere el bloqueo que realizó en la tabla, devolviendo el mismo resultado que se muestra en el conjunto de resultados a continuación:
las sugerencias de tabla with (NOLOCK) y READUNCOMMITTED solo se pueden usar con las instrucciones SELECT.,
Esta consulta también recuperará los mismos datos directamente, sin usar ninguna sugerencia de tabla y sin esperar a que la instrucción UPDATE libere el bloqueo que realizó en la tabla, como se muestra en el conjunto de resultados a continuación:
de los resultados anteriores, 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, arriesgándose a tener datos no precisos., Pero, ¿la consulta SELECT que utiliza la sugerencia de tabla WITH (NOLOCK) afectará negativamente a otros procesos en SQL Server? Para obtener la respuesta, Primero comprobemos qué tipo de bloqueos se otorgará la sugerencia de tabla WITH (NOLOCK) durante su ejecución.,=»26bd69ce5a»>
|
1
|
sp_lock 54
|
Usted verá en el resultado de que la consulta que utiliza el WITH (NOLOCK) sugerencia de tabla será concedida S y Sch-S bloqueo de tipos, como se muestra en el resultado siguiente:
a Partir del resultado anterior, se verá que el WITH (NOLOCK) sugerencia de tabla se concederá el acceso compartido (S) de bloqueo en el nivel de base de datos., El bloqueo de acceso compartido (S) Se utiliza para la operación de lectura, lo que permite a las transacciones simultáneas leer datos bajo control de concurrencia pesimista, evitando que otras transacciones modifiquen el recurso bloqueado mientras existan bloqueos compartidos (s) en ese recurso, hasta que se libere ese bloqueo tan pronto como se complete la operación de lectura.
el segundo tipo de bloqueo que se concede a la consulta mediante la sugerencia de tabla WITH (NOLOCK) es el bloqueo de estabilidad del esquema (SCH-S)., Este bloqueo no impedirá que ninguna otra transacción acceda 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 se ejecuta la consulta. Esto realmente tiene sentido, ya que no necesita comenzar a leer datos de la tabla, luego otra transacción cambia la estructura de esa tabla durante el proceso de recuperación de datos., SQL Server Database Engine utiliza los bloqueos de modificación de esquema (SCH-M) mientras procesa los comandos del lenguaje de definición de datos (DDL), como agregar una columna nueva, soltar una columna existente, soltar o reconstruir índices, para evitar el acceso simultáneo a la tabla hasta que se libere el bloqueo.
mi consulta NOLOCK está bloqueando!
esto significa que el nombre de 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 intenten adquirir un bloqueo de modificación de esquema (Sch-M) en esa tabla., Es un problema crítico que debe tener en cuenta si hay muchos usuarios que ejecutan sus consultas SELECT utilizando la sugerencia de tabla WITH (NOLOCK), lo que le impide realizar cambios en el esquema de tabla o mantenimientos en los índices de tabla, siendo bloqueados por el bloqueo de estabilidad del esquema (Sch_S).,>
al mismo tiempo, ejecutaremos la siguiente consulta, que está soltando un índice en la misma tabla y crearlo de nuevo, bajo el número de sesión 58:
luego verificando el estado de ambas consultas usando el comando sp_who2, verá en el resultado que, la instrucción SELECT que está utilizando la sugerencia de tabla WITH (NOLOCK) y ejecutando el número de sesión 53, está bloqueando la/CREATE INDEX process running under session number 58, as shown clearly below:
si comprobamos los bloqueos que realiza cada consulta, utilizando el sys.,dm_tran_locks objeto del sistema como en la consulta siguiente:
|
1
2
3
|
SELECT *
FROM sys.dm_tran_locks
DONDE resource_type = ‘OBJECT’
|
verás que, la GOTA/CREATE INDEX proceso que se ejecuta en la sesión número 58 se espera para adquirir modificación del esquema (Sch-M) tipo de bloqueo., Esto se debe al hecho de que el bloqueo de modificación de esquema (SCH-M) no se puede adquirir mientras el bloqueo de estabilidad de esquema (Sch_S) que ya está concedido a la instrucción SELECT que se ejecuta bajo el número de sesión 53, ya existe como se muestra en la instantánea siguiente:
¡mi consulta NOLOCK está bloqueada!
por el contrario, dado que la sugerencia de tabla WITH (NOLOCK) adquiere el tipo de bloqueo de estabilidad de esquema (SCH-S), la instrucción SELECT que utiliza la sugerencia de tabla WITH (NOLOCK) se bloqueará si se realiza una modificación de esquema en esa tabla.,
|
1
2
3
|
sp_who2 53
IR
sp_who2 54
|
Usted verá que la instrucción SELECT que se ejecuta en la sesión 54 es bloqueado por la instrucción ALTER TABLE se ejecuta en la sesión 54, como se muestra a continuación:
a Continuación, comprobar los bloqueos que se realizan por cada consulta, mediante el sys.,dm_tran_locks system object as in the query below:
|
1
2
3
4
|
SELECT *
FROM sys.,eso, la instrucción SELECT que usa la pista de tabla WITH (NOLOCK) y se ejecuta bajo el número de sesión 54, estará esperando para adquirir el bloqueo de estabilidad de esquema (Sch_S), debido al hecho de que el bloqueo de estabilidad de esquema (Sch-S) no se puede adquirir mientras el bloqueo de modificación de esquema (Sch_M), que ya está otorgado a la instrucción ALTER que se ejecuta bajo el número de sesión 53, ya existe como se muestra en la instantánea a continuación:
puede imaginar la situación cuando está programando un gran número de informes por la noche, que están utilizando la sugerencia de tabla con (NOLOCK) solo para estar seguros., 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 fin de evitar los problemas que puede enfrentar al usar con (NOLOCK) table hint. Estas sugerencias incluyen:
|
Deja una respuesta