foto de James Sutton en Unsplash
Nota: El problema descrito a continuación se aplica a todas las consultas seleccionadas, no solo a las adornadas con sugerencias de NOLOCK. Sin embargo, el hecho de que se aplique a las consultas de NOLOCK fue una gran sorpresa para mí, de ahí el título.
a muchas personas no les gusta NOLOCK (es decir, el nivel de aislamiento no comprometido de lectura) porque puede devolver datos inexactos., He visto muchos argumentos advirtiendo a los desarrolladores de recuperar lecturas no comprometidas debido a cómo pueden devolver datos sucios, lecturas fantasma y lecturas no repetibles .
he conocido todos los problemas anteriores, pero hay un problema del que nunca había oído hablar hasta hace poco: NOLOCK puede bloquear la ejecución de otras consultas.
Ver el vídeo de esta semana en YouTube
vamos a dar un paso atrás y entender por qué he utilizado tan a menudo NOLOCK en el pasado. Una instancia bastante típica de cuando uso NOLOCK es cuando quiero dejar que una consulta se ejecute durante la noche para devolver un gran conjunto de datos., Estoy de acuerdo con algunas inconsistencias en los datos (de lecturas sucias, etc…). Mi principal preocupación es que no quiero que la consulta de larga duración se interponga en el camino de otros procesos.
siempre pensé que NOLOCK era una solución perfecta para este escenario porque nunca bloquea los datos que lee: los resultados pueden no ser perfectos, pero al menos la consulta no afectará negativamente a ningún otro proceso en el servidor.
Aquí es donde mi comprensión de NOLOCK estaba equivocada: mientras que NOLOCK no bloqueará los datos de nivel de fila, eliminará un bloqueo de estabilidad de esquema.,
un bloqueo de estabilidad de esquema (SCH-S) evita que la estructura de una tabla cambie mientras se ejecuta la consulta. Todas las instrucciones SELECT, incluidas las del nivel de aislamiento read uncommitted/NOLOCK, obtienen un bloqueo Sch-S. Esto tiene sentido porque no queremos comenzar a leer datos de una tabla y luego tener el cambio de estructura de columna a mitad de camino a través de la recuperación de datos.
sin embargo, esto también significa que puede haber algunas operaciones que se bloquean por un bloqueo Sch-S. Por ejemplo, cualquier comando que solicite un bloqueo de modificación de esquema (SCH-M) Se bloquea en este escenario.,
¿qué comandos solicitan bloqueos Sch-M?
cosas como una reconstrucción de índice o una tabla sp_recompile. Estos son los tipos de comandos que se ejecutan en mis trabajos de mantenimiento nocturno que estaba tratando de evitar dañar mediante el uso de NOLOCK en primer lugar!
para reiterar, solía pensar que usar la sugerencia NOLOCK era una gran manera de evitar el bloqueo durante consultas largas., Sin embargo, resulta que mis consultas NOLOCK en realidad estaban bloqueando mis trabajos nightly index (todas las consultas SELECT bloquean en este ejemplo, pero encuentro que el NOLOCK es particularmente engañoso), ¡lo que causó que otras declaraciones SELECT también se bloquearan!
echemos un vistazo a esto en acción. Aquí tengo una consulta que crea una base de datos, una tabla, y luego ejecuta una consulta de larga duración con NOLOCK:
Ahora, mientras se está produciendo esa lectura de mil millones de filas, podemos verificar que la consulta sacó un bloqueo Sch-S mirando sys.,dm_tran_locks:
SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'
mientras se está ejecutando, si intentamos reconstruir un índice, esa reconstrucción se bloquea (se muestra como una espera):
USE GOALTER INDEX IX_Id ON dbo.Test REBUILD
nuestra consulta de reconstrucción de índice permanecerá bloqueada hasta que nuestra consulta de selección de miles de millones de filas termine de ejecutarse (o sea eliminada). Esto significa que la consulta que pretendía ser completamente discreta ahora bloquea mi trabajo de mantenimiento de nightly index.,
aún peor, cualquier otra consulta que intente ejecutarse después de la consulta de reconstrucción (o cualquier otro comando que solicite un bloqueo Sch-M) también se bloqueará! Si intento ejecutar un simple COUNT(*) consulta:
USE GOSELECT COUNT(*) FROM dbo.Test
Bloqueado! Esto significa que no solo mi consulta inicial de NOLOCK está causando que mis trabajos de mantenimiento de reconstrucción de índice esperen, sino que el bloqueo SCH-M colocado por el trabajo de mantenimiento de reconstrucción está causando que cualquier consulta posterior en esa tabla se bloquee y se vea obligada a esperar también., Acabo de descarrilar la puntualidad de mi trabajo de mantenimiento y las consultas posteriores con una declaración de bloqueo NOLOCK!
soluciones
desafortunadamente, este es un problema difícil y no hay un único remedio para todos.
solución #1: no ejecute consultas largas
podría evitar ejecutar consultas largas por la noche cuando podrían encontrarse con mis trabajos de mantenimiento de índice., Esto evitaría que los trabajos de mantenimiento del índice y las consultas posteriores se retrasen, pero significa que mi consulta inicial de selección de mil millones de filas tendría que ejecutarse antes, lo que afectaría negativamente al rendimiento del servidor durante una hora del día potencialmente más ocupada.,
Solución #2: Use WAIT_AT_LOW_PRIORITY
a partir de 2014, podría hacer una reconstrucción de índice en línea con el conjunto de opciones WAIT_AT_LOW_PRIORITY:
ALTER INDEX IX_Id ON dbo.Test REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = BLOCKERS)))
esta consulta básicamente le da a cualquier consulta de selección de bloqueo que se esté ejecutando actualmente 1 minuto para terminar de ejecutarse o de lo contrario esta consulta las matará y luego ejecutará la reconstrucción de índice. Alternativamente, también podríamos haber establecido ABORT_AFTER_WAIT = SELF y la consulta rebuild se mataría a sí misma, permitiendo que NOLOCK billion row SELECT termine de ejecutarse y no impidiendo que se ejecuten otras consultas.,
Esta no es una gran solución porque significa que la consulta de larga duración se elimina o la reconstrucción del índice se elimina.
Solución # 3: reconstruir si no hay Sch-S, reorganizar de lo contrario
Se puede escribir una solución programática que intente reconstruir el índice, pero retroceda para reorganizar si sabe que tendrá que esperar un bloqueo Sch-M.
he creado la placa de caldera a continuación como punto de partida, pero el cielo es el límite con lo que puede hacer con él (p. ej., cree un bucle WHILE para verificar el bloqueo cada x segundos, cree un tiempo de espera para cuando el script deje de intentar reconstruir y simplemente reorganice, etc…)
Esta solución es mi favorita porque:
- Las consultas Ad hoc de larga duración no se matan (todo ese tiempo de procesamiento no se desperdicia)
- Otras consultas select no están bloqueadas por el intento de bloqueo Sch-M por REBUILD
- El mantenimiento del índice aún se produce, incluso si termina siendo una reorganización en lugar de una reconstrucción
