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

Artículo
04/03/2023

Resumen

La escalación de bloqueos es el proceso de convertir muchos bloqueos específicos (como bloqueos de fila o de página) en bloqueos de tabla. Microsoft SQL Server determina dinámicamente cuándo se debe realizar la escalación de bloqueos. Cuando toma esta decisión, SQL Server considera 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 bloqueos en el sistema en su conjunto. Normalmente, el comportamiento predeterminado de SQL Server hace que la escalación de bloqueos se produzca solo en aquellos momentos en los que mejoraría el rendimiento o cuando se debe reducir la memoria de bloqueo del sistema excesiva a un nivel más razonable. Sin embargo, algunos diseños de aplicaciones o consultas pueden desencadenar la escalación de bloqueos en un momento en que esta acción no es deseable y el bloqueo de tabla escalada podría bloquear a otros usuarios. En este artículo se describe cómo determinar si la escalación de bloqueos está causando el bloqueo y cómo tratar la escalación de bloqueo no deseada.

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

Determinación de si la escalación de bloqueos está causando el bloqueo

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

Si se está produciendo una escalación de bloqueo, compruebe que el bloqueo de tabla escalada está bloqueando a otros usuarios.

Para obtener más información sobre cómo identificar el bloqueador principal y el recurso de bloqueo que contiene el bloqueador de encabezados y que bloquea otros identificadores de proceso de servidor (SPID), consulte INF: Descripción y resolución de problemas de bloqueo SQL Server.

Si el bloqueo que bloquea a otros usuarios no es otro que un bloqueo TAB (nivel de tabla) que tiene un modo de bloqueo de S (compartido) o X (exclusivo), la escalación de bloqueos no es el problema. En concreto, 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 elevación del bloqueo. Si los problemas de bloqueo no se deben a la escalación de bloqueos, consulte los pasos de solución de problemas inf: descripción y resolución de problemas de bloqueo de SQL Server.

Evitar la escalación de bloqueo

El método más sencillo y seguro para evitar la escalación de bloqueos es mantener las transacciones cortas y reducir la superficie de bloqueo de 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:

Divida las operaciones por lotes grandes en varias operaciones más pequeñas. Por ejemplo, ejecute la consulta siguiente para quitar más de 100 000 registros antiguos de una tabla de auditoría y, a continuación, determine que la consulta produjo una escalación de bloqueo que bloqueó a otros usuarios:
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
Al quitar estos registros unos cientos a la vez, puede reducir drásticamente el número de bloqueos que se acumulan por transacción. Esto evitará la escalada de bloqueos. Por ejemplo, ejecute la consulta siguiente:
```
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 eficaz posible. Los exámenes grandes o muchas búsquedas de marcadores pueden aumentar la posibilidad de escalación de bloqueo. Además, aumentan la posibilidad de interbloqueos y afectan negativamente a la simultaneidad y al rendimiento. Después de identificar que la consulta que provoca la escalación de bloqueos, busque oportunidades para crear nuevos índices o 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 potencialmente nuevos índices no clúster para mejorar el rendimiento de las consultas. Para obtener más información, consulte SQL Server Guía de diseño y arquitectura de índices.

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 Bookmark Lookup devolverá muchas filas, podría usar una PREFETCH cláusula para realizar la búsqueda de marcadores. Si SQL Server usa PREFETCH 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 de la consulta. Esto significa que lo que puede parecerse a una SELECT instrucción en un nivel de aislamiento "confirmado por lectura" podría adquirir muchos miles de bloqueos de clave (tanto en el índice clúster como en un índice no clúster). Esto puede hacer que dicha consulta supere los umbrales de escalación de bloqueo. Esto es especialmente importante si observa que el bloqueo escalado es un bloqueo de tabla compartida, aunque no se ven normalmente en el nivel de aislamiento predeterminado "read-committed". Si una cláusula Bookmark Lookup WITH PREFETCH está causando la escalación, considere la posibilidad de agregar columnas al índice no clúster que aparece en Index Seek o al operador lógico Index Scan debajo del operador lógico Bookmark Lookup en el plan de 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 los criterios de combinación o en la cláusula WHERE si es poco práctico incluir todo en la lista "seleccionar columna".

Una combinación de bucle anidado también puede usar PREFETCHy esto provoca el mismo comportamiento de bloqueo.
La escalación de bloqueos no se puede producir si un SPID diferente contiene actualmente un bloqueo de tabla incompatible. La escalación de bloqueos 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 escalación de bloqueo porque otro SPID contiene un bloqueo TAB incompatible, la consulta que intentó la escalación no se bloquea mientras se 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 escalación de bloqueos en una tabla determinada consiste en 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 intención) en el nivel de tabla no bloquea ninguna fila o página, pero sigue sin ser compatible con un bloqueo TAB S (compartido) o X (exclusivo). Por ejemplo, suponga que debe ejecutar un trabajo por lotes que modifique muchas filas de la tabla mytable y que haya causado bloqueos debido a la escalación de bloqueos. 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 que el nuevo trabajo 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 escalación de bloqueos en la tabla durante ese tiempo. Este lote no modifica ningún dato 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 los bloqueos de página o fila mediante ALTER INDEX).
Elimine la escalación de bloqueos causada por la falta de SARGabilidad, 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 SARGability, consulte Consideraciones de consulta de la Guía de diseño interno. Por ejemplo, una consulta bastante sencilla que no parece estar solicitando muchas filas (o quizás una sola fila) puede terminar examinando 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 de tipos de datos implícitas o explícitas, 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 conduce 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 del recuento de bloqueos. La solución consiste en evitar el uso de funciones en las columnas de la cláusula WHERE, lo que garantiza condiciones SARGable.

Deshabilitación de la escalación de bloqueo

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

Nivel de tabla: Puede deshabilitar la escalación de bloqueos en el nivel de tabla. Consulte ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Para determinar la tabla de destino, examine las consultas de T-SQL. Si no es posible, use eventos extendidos, habilite el evento lock_escalation y examine la columna object_id . 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 cualquiera de las marcas de seguimiento 1211 o 1224 o ambas para la instancia. Sin embargo, estas marcas de seguimiento deshabilitan toda la escalación de bloqueos globalmente en la instancia de SQL Server. La escalación de bloqueos tiene un propósito útil en SQL Server maximizando la eficacia de las consultas que, de lo contrario, se ralentizan debido a la sobrecarga de adquirir y liberar varios miles de bloqueos. La escalación de bloqueos también ayuda a minimizar la memoria necesaria para realizar un seguimiento de los bloqueos. La memoria que SQL Server puede asignar dinámicamente para las estructuras de bloqueo es finita. Por lo tanto, si deshabilita la escalación de bloqueos y la memoria de bloqueo crece lo suficientemente grande, 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 de bloqueo y memoria de SQL Server.

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 puede bloquear a los usuarios o provocar un largo tiempo de recuperación de la base de datos si reinicia el servicio de SQL Server.

Puede agregar estas marcas de seguimiento (-T1211 o -T1224) mediante Administrador de configuración de SQL Server. Debe reiniciar el servicio SQL Server para que se aplique un nuevo parámetro de inicio. Si ejecuta la DBCC TRACEON (1211, -1) consulta o DBCC TRACEON (1224, -1) , la marca de seguimiento surte efecto inmediatamente.
Sin embargo, si no agrega -T1211 o -T1224 como parámetro de inicio, el efecto de un DBCC TRACEON comando se pierde cuando se reinicia el servicio de SQL Server. Al activar la marca de seguimiento, se evitan 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 escalación de bloqueos.

Umbrales de escalación de bloqueo

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

Se alcanza el 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 escalación de bloqueo. La memoria de bloqueo está limitada al 60 % 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 (es mucho más probable si la escalación de bloqueo está deshabilitada), se producirán errores en todos los intentos de asignar bloqueos adicionales y 1204 se generarán errores.
Se alcanza un umbral de bloqueo : una vez comprobado 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 escalación de bloqueo.

Para comprender qué umbral se alcanzó, use eventos extendidos, habilite el evento lock_escalation y examine las columnas escalated_lock_count y escalation_cause . Como alternativa, use el evento Lock:Escalation y examine el EventSubClass 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 memoria. Además, examine las IntegerData columnas y IntegerData2 .

Recomendaciones

Los métodos que se describen en la sección Impedir 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 suelen producir un mejor rendimiento para la consulta que deshabilitar la escalación de bloqueos. Microsoft recomienda habilitar esta marca de seguimiento solo para mitigar el bloqueo grave causado por la escalación de bloqueos mientras se investigan otras opciones, como las que se describen en este artículo.