Resolver problemas de bloqueo causados por la escalación de bloqueo en SQL Server

Resumen

La escalación de bloqueo es el proceso de convertir muchos bloqueos de gran tamaño (como bloqueos de fila o página) en bloqueos de tabla. Microsoft SQL Server determina dinámicamente cuándo se debe realizar la escalación de bloqueo. Cuando toma esta decisión, SQL Server tiene en cuenta el número de bloqueos que se mantienen en un examen determinado, el número de bloqueos que mantiene toda la transacción y la memoria que se usa para los bloqueos en el sistema en su conjunto. Por lo general, el comportamiento predeterminado de SQL Server hace que la escalación de bloqueo se produzca solo en aquellos momentos en los que mejoraría el rendimiento o cuando se debe reducir el exceso de memoria de bloqueo del sistema a un nivel más razonable. Sin embargo, algunos diseños de aplicación o consulta pueden desencadenar la progresividad del bloqueo en un momento en que esta acción no es deseable y el bloqueo de tabla escalado podría bloquear a otros usuarios. En este artículo se describe cómo determinar si la escalación de bloqueo está provocando bloqueos y cómo tratar la escalada de bloqueo no deseada.

Versión del producto original:   SQL Server
Número KB original:   323630

Determinar si la escalación de bloqueo está provocando bloqueos

La escalación de bloqueo no causa la mayoría de los problemas de bloqueo. Para determinar si la escalación de bloqueo se está produciendo en el momento en que se producen problemas de bloqueo, inicie una sesión de eventos extendidos que incluya el lock_escalation evento. Si no ve ningún evento, la escalación de bloqueo no se produce en el servidor y la información de este artículo no se aplica lock_escalation a su situación.

Si se produce una escalada de bloqueo, compruebe que el bloqueo de tabla escalada está bloqueando a otros usuarios.

Para obtener más información acerca de cómo identificar el bloqueador de cabeza y el recurso de bloqueo que mantiene el bloqueador de cabeza y que bloquea otros identificadores de proceso de servidor (SPID), vea INF: Understanding and resolving SQL Server blocking problems.

Si el bloqueo que bloquea a otros usuarios no es otro que un bloqueo TAB (nivel de tabla) que tiene un modo de bloqueo S (compartido) o X (exclusivo), la escalada de bloqueo no es el problema. En particular, si el bloqueo TAB es un bloqueo de intención (como un modo de bloqueo de IS, IU o IX), esto no se debe a la escalada de bloqueo. Si los problemas de bloqueo no son causados por la escalación de bloqueos, consulte INF: Understanding and resolving SQL Server blocking problems troubleshooting steps.

Impedir la progresividad de bloqueo

El método más sencillo y seguro para evitar la escalada de bloqueo es mantener las transacciones cortas y reducir la superficie de bloqueo de las consultas costosas para que no se superen los umbrales de escalación de bloqueo. Hay varios métodos para lograr este objetivo, incluidas las siguientes estrategias:

  • Dividir las operaciones por lotes grandes en varias operaciones más pequeñas. Por ejemplo, ejecute la siguiente consulta para quitar más de 100.000 registros antiguos de una tabla de auditoría y, a continuación, determine que la consulta provocó una escalada de bloqueo que bloqueó a otros usuarios:

    DELETE FROM LogMessages WHERE LogDate < '20020102';
    

    Al quitar estos registros unos cientos cada vez, puede reducir considerablemente el número de bloqueos que se acumulan por transacción. Esto evitará la escalada de bloqueo. Por ejemplo, ejecute la siguiente consulta:

    DECLARE @done bit = 0;
    WHILE (@done = 0)
    BEGIN
        DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
        IF @@rowcount < 1000 SET @done = 1;
    END;
    
  • Reduzca la superficie de bloqueo de la consulta haciendo que la consulta sea lo más eficiente posible. Los exámenes grandes o muchas búsquedas de marcadores pueden aumentar la probabilidad de escalamiento de bloqueo. Además, aumentan la probabilidad de interbloqueos y afectan negativamente a la concurrencia y el rendimiento. Después de identificar que la consulta que provoca la progresividad del bloqueo, busque oportunidades para crear nuevos índices o para agregar columnas a un índice existente para quitar los exámenes de índice o tabla y para maximizar la eficacia de las búsquedas de índice. Revise el plan de ejecución y cree posiblemente nuevos índices no clúster para mejorar el rendimiento de las consultas. Para obtener más información, vea SQL Server Index Architecture and Design Guide.

    Un objetivo de esta optimización es hacer que las búsquedas de índice devuelvan el menor número posible de filas para minimizar el costo de las búsquedas de marcadores (maximizar la selectividad del índice para la consulta). Si SQL Server estima que un operador lógico búsqueda de marcadores devolverá muchas filas, puede usar una cláusula PREFETCH para realizar la búsqueda de marcadores. Si SQL Server usa para una búsqueda de marcadores, debe aumentar el nivel de aislamiento de transacción de una parte de la consulta a "lectura repetible" para una parte PREFETCH de la consulta. Esto significa que lo que puede parecer a una instrucción en un nivel de aislamiento "confirmado por lectura" puede adquirir muchos miles de bloqueos de clave (tanto en el índice agrupado como en un índice no SELECT clúster). Esto puede hacer que dicha consulta supere los umbrales de escalación de bloqueo. Esto es especialmente importante si encuentra que el bloqueo escalado es un bloqueo de tabla compartido, aunque estos no se ven comúnmente en el nivel de aislamiento predeterminado "confirmado por lectura". Si una cláusula Bookmark Lookup WITH está provocando la escalada, considere la posibilidad de agregar columnas al índice no clúster que aparece en la búsqueda de índice o al operador lógico Detección de índices debajo del operador lógico Búsqueda de marcadores en el plan de PREFETCH consulta. Es posible crear un índice de cobertura (un índice que incluya todas las columnas de una tabla que se usaron en la consulta) o al menos un índice que cubra las columnas que se usaron para criterios de combinación o en la cláusula WHERE si no es práctico incluir todo en la lista "seleccionar columna".

    Una combinación de bucle anidado también puede usar PREFETCH y esto provoca el mismo comportamiento de bloqueo.

  • La escalación de bloqueo no se puede producir si un SPID diferente contiene actualmente un bloqueo de tabla incompatible. La escalación de bloqueo siempre se escala a un bloqueo de tabla y nunca a un bloqueo de página. Además, si se produce un error en un intento de escalamiento de bloqueo porque otro SPID contiene un bloqueo TAB incompatible, la consulta que intentó la escalación no se bloquea mientras espera un bloqueo TAB. En su lugar, sigue adquiriendo bloqueos en su nivel original, más granular (fila, clave o página), realizando periódicamente intentos de escalado adicionales. Por lo tanto, un método para evitar la escalada de bloqueo en una tabla determinada es adquirir y mantener un bloqueo en una conexión diferente que no sea compatible con el tipo de bloqueo escalado. Un bloqueo IX (exclusivo de propósito) en el nivel de tabla no bloquea ninguna fila o página, pero aún no es compatible con un bloqueo TAB S (compartido) o X (exclusivo) escalado. Por ejemplo, supongamos que debe ejecutar un trabajo por lotes que modifica muchas filas de la tabla mytable y que provocó bloqueos debido a la escalación de bloqueo. Si este trabajo siempre finaliza en menos de una hora, puede crear un trabajo de Transact-SQL que contenga el código siguiente y programar el nuevo trabajo para que se inicie varios minutos antes de la hora de inicio del trabajo por lotes:

    BEGIN TRAN;
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
    WAITFOR DELAY '1:00:00';
    COMMIT TRAN;
    

    Esta consulta adquiere y mantiene un bloqueo IX en mytable durante una hora. Esto evita la progresividad de bloqueo en la tabla durante ese tiempo. Este lote no modifica datos ni bloquea otras consultas (a menos que la otra consulta fuerza un bloqueo de tabla mediante la sugerencia TABLOCK o si un administrador ha deshabilitado bloqueos de página o fila mediante ALTER INDEX).

  • Elimine la progresividad de bloqueo causada por la falta de SARGability, un término de base de datos relacional que se usa para describir si una consulta puede usar índices para predicados y columnas de combinación. Para obtener más información sobre la SARGability, vea Inside Design Guide Query Considerations. Por ejemplo, una consulta bastante sencilla que no parece solicitar muchas filas (o quizás una sola fila) puede terminar analizando toda una tabla o índice. Esto puede ocurrir si hay una función o cálculo en el lado izquierdo de una cláusula WHERE. Estos ejemplos que carecen de SARGability incluyen conversiones implícitas o explícitas de tipos de datos, la función del sistema ISNULL(), una función definida por el usuario con la columna pasada como parámetro o un cálculo en la columna, como WHERE CONVERT(INT, column1) = @a o WHERE Column1*Column2 = 5 . En tales casos, la consulta no puede BUSCAR el índice existente, incluso si contiene las columnas adecuadas, porque todos los valores de columna deben recuperarse primero y pasarse a la función. Esto lleva a un examen de toda la tabla o índice y da como resultado la adquisición de un gran número de bloqueos. En tales circunstancias SQL Server puede alcanzar el umbral de escalación de recuento de bloqueos. La solución es evitar el uso de funciones en las columnas de la cláusula WHERE, lo que garantiza condiciones de SARGable.

Deshabilitar la escalación de bloqueo

Aunque es posible deshabilitar la escalación de bloqueo SQL Server, no se recomienda. En su lugar, use las estrategias de prevención que se describen en la sección Impedir escalamiento de bloqueo.

  • Nivel de tabla: Puede deshabilitar la escalación de bloqueo en el nivel de tabla. Consulte ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Para determinar la tabla de destino, examine las consultas SQL T. Si no es posible, use Eventosextendidos , habilite el evento lock_escalation y examine la object_id columna. Como alternativa, use el evento Lock:Escalation y examine la ObjectID2 columna mediante SQL Profiler.
  • Nivel de instancia: Puede deshabilitar la escalación de bloqueo habilitando la marca de seguimiento 1211 para la instancia. Sin embargo, esta marca de seguimiento deshabilita todas las escalaciones de bloqueo globalmente en la instancia de SQL Server. La escalación de bloqueos sirve de utilidad en SQL Server maximizando la eficacia de las consultas que, de lo contrario, se ralentizan por la sobrecarga de adquirir y liberar varios miles de bloqueos. La escalación de bloqueo también ayuda a minimizar la memoria necesaria para realizar un seguimiento de los bloqueos. La memoria que SQL Server asignar dinámicamente para las estructuras de bloqueo es finita. Por lo tanto, si deshabilita la escalación de bloqueo y la memoria de bloqueo crece lo suficiente, cualquier intento de asignar bloqueos adicionales para cualquier consulta podría producir un error y generar la siguiente entrada de error:

Error: 1204, Gravedad: 19, Estado: 1
El SQL Server no puede obtener un recurso LOCK en este momento. Vuelva a ejecutar la instrucción cuando haya menos usuarios activos o pida al administrador del sistema que compruebe la configuración SQL Server bloqueo y memoria.

Nota

Cuando se produce un error 1204, detiene el procesamiento de la instrucción actual y provoca una reversión de la transacción activa. La reversión en sí puede bloquear a los usuarios o provocar un largo tiempo de recuperación de la base de datos si reinicia el servicio SQL Server base de datos.

Puede agregar esta marca de seguimiento (-T1211) mediante Administrador de configuración de SQL Server. Debe reiniciar el servicio SQL Server para que un nuevo parámetro de inicio suba a efecto. Si ejecuta la DBCC TRACEON (1211, -1) consulta, la marca de seguimiento tendrá efecto inmediatamente.
Sin embargo, si no agrega el parámetro de inicio -T1211, el efecto de un comando se pierde cuando se reinicia SQL Server DBCC TRACEON servicio. Al activar la marca de seguimiento se evitan las futuras escalaciones de bloqueo, pero no se invierten las escalaciones de bloqueo que ya se han producido en una transacción activa.

Si usa una sugerencia de bloqueo, como ROWLOCK, esto solo modifica el plan de bloqueo inicial. Las sugerencias de bloqueo no impiden la progresividad del bloqueo.

Umbrales de escalación de bloqueo

La escalación de bloqueos puede producirse en una de las siguientes condiciones:

  • Umbral de memoria: se alcanza un umbral de memoria del 40 por ciento de la memoria de bloqueo. Cuando la memoria de bloqueo supera el 24 por ciento del grupo de búferes, se puede desencadenar una escalada de bloqueo. La memoria de bloqueo está limitada al 60 por ciento del grupo de búferes visible. El umbral de escalación de bloqueo se establece en el 40 por ciento de la memoria de bloqueo. Esto es el 40 por ciento del 60 por ciento del grupo de búferes o el 24 por ciento. Si la memoria de bloqueo supera el límite del 60 por ciento (esto es mucho más probable si la escalación de bloqueo está deshabilitada), todos los intentos de asignar bloqueos adicionales fallan y se generan 1204 errores.

  • Se alcanza un umbral de bloqueo: después de comprobar el umbral de memoria, se evalúa el número de bloqueos adquiridos en la tabla o índice actual. Si el número supera los 5.000, se desencadena una escalada de bloqueo.

Para comprender qué umbral se alcanzó, use eventos extendidos, habilite el evento lock_escalation y examine las escalated_lock_count y escalation_cause columnas. Como alternativa, use el evento Lock:Escalationy examine el valor, donde "0 - LOCK_THRESHOLD" indica que la instrucción superó el umbral de bloqueo y "1 - MEMORY_THRESHOLD" indica que la instrucción superó el umbral de EventSubClass memoria. Además, examine las IntegerData columnas IntegerData2 y.

Recomendaciones

Los métodos que se debaten en la sección Impedir la escalación de bloqueo son mejores opciones que deshabilitar la escalación en el nivel de tabla o instancia. Además, los métodos preventivos generalmente producen un mejor rendimiento para la consulta que deshabilitar la escalación de bloqueo. Microsoft recomienda habilitar esta marca de seguimiento solo para mitigar el bloqueo grave causado por la escalada de bloqueo mientras se investigan otras opciones, como las que se debaten en este artículo.

Consulte también