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

Se aplica a: SQL Server

Número de KB original: 323630

Resumen

La extensión de bloqueo convierte muchos bloqueos específicos (como bloqueos de fila o página) en un solo bloqueo de tabla. SQL Server desencadena la escalación dinámicamente en función del recuento de bloqueos o la memoria de bloqueo, y su comportamiento predeterminado mejora el rendimiento en la mayoría de las cargas de trabajo. Sin embargo, algunos diseños de consulta o transacción provocan una escalación a veces cuando el bloqueo de tabla resultante bloquea a otros usuarios. En este artículo se explica cómo determinar si la extensión de bloqueo está causando bloqueos en SQL Server, cómo evitarlo y cómo (y cuándo) deshabilitarlo.

Determinar si la extensión de bloqueo está causando bloqueos

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

Si se está produciendo una extensió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 de cabeza y el recurso de bloqueo que mantiene el bloqueador de encabezados y que bloquea otros identificadores de proceso de servidor (SPID), consulte Descripción y resolución de problemas de bloqueo de SQL Server.

Si el bloqueo que está bloqueando a otros usuarios es algo distinto de un bloqueo TAB (nivel de tabla) que tiene un modo de bloqueo de S (compartido) o X (exclusivo), la extensión de bloqueo 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), no se debe a la extensión del bloqueo. Si los problemas de bloqueo no se deben a la extensión de bloqueos, consulte los pasos de solución de problemas de solución de problemas de bloqueo y resolución de SQL Server.

Evitar la extensión de bloqueo

El método más sencillo y seguro para evitar la extensión 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. Use una o varias de las estrategias siguientes.

Dividir las operaciones grandes en transacciones más pequeñas

Divida las operaciones en lote 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 provocó una extensión de bloqueo que bloqueó a otros usuarios:

DELETE FROM LogMessages WHERE LogDate < '20020102';

Al quitar estos registros unos cientos de veces, puede reducir drásticamente el número de bloqueos que se acumulan por transacción. Este enfoque impide la extensión de bloqueo. 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;

Optimización de consultas e índices para reducir bloqueos

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 la extensión de bloqueo. Además, aumentan la posibilidad de interbloqueos y afectan negativamente a la simultaneidad y el rendimiento. Después de identificar la consulta que provoca la extensión de bloqueo, busque oportunidades para crear nuevos índices o agregar columnas a un índice existente para quitar los exámenes de índice o tabla y maximizar la eficacia de las búsquedas de índice. Revise el plan de ejecución y cree nuevos índices no agrupados para mejorar el rendimiento de las consultas. Para más información, vea la Guía de diseño y de arquitectura de índices de SQL Server.

Un objetivo de esta optimización es hacer que las búsquedas de índice devuelvan lo menos filas posibles para minimizar el costo de las búsquedas de marcadores (maximizar la selectividad del índice de la consulta). Si SQL Server calcula 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 parecer una SELECT instrucción en un nivel de aislamiento de "lectura confirmada" podría adquirir muchos miles de bloqueos de clave (tanto en el índice agrupado como en un índice no clúster). Esto puede hacer que esta consulta supere los umbrales de escalación de bloqueos. Esto es especialmente importante si encuentra que el bloqueo escalado es un bloqueo de tabla compartido, aunque normalmente no se ven 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 el operador lógico Index Scan debajo del operador lógico Bookmark Lookup en el plan de consulta. Puede ser 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 abarque 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 PREFETCHy esto provoca el mismo comportamiento de bloqueo.

Suspensión de un bloqueo de nivel de tabla compatible en una conexión independiente

La extensión de bloqueo no se puede producir si un SPID diferente contiene actualmente un bloqueo de tabla incompatible. La extensió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 extensión de bloqueo porque otro SPID contiene un bloqueo TAB incompatible, la consulta que intentó la extensión no se bloquea mientras espera un bloqueo TAB. En su lugar, continúa con la adquisición de bloqueos en su nivel original y más granular (fila, clave o página), realizando periódicamente más intentos de extensión.

Por tanto, un método para evitar la extensión de bloqueo en una tabla concreta consiste en adquirir y mantener un bloqueo en otra conexión 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, supongamos que debe ejecutar un trabajo por lotes que modifique muchas filas de la tabla mytable y que provocara el bloqueo debido a la extensió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 comience varios minutos antes de la hora de inicio del trabajo por lotes:

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

Esta consulta adquiere y contiene un bloqueo IX en mytable durante una hora. Este bloqueo impide la extensión de bloqueo en la tabla durante ese tiempo. Este lote no modifica ningún dato ni bloquea otras consultas (a menos que la otra consulta forza un bloqueo de tabla mediante la sugerencia TABLOCK o si un administrador ha deshabilitado bloqueos de página o fila mediante ALTER INDEX).

Asegurarse de que los predicados son sargables

Elimine la extensión 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( Consideraciones de consulta de la Guía de diseño). Por ejemplo, una consulta bastante sencilla que no parece solicitar muchas filas (o quizás una sola fila) puede terminar examinando toda una tabla o índice. Esta situación puede producirse si hay una función o un 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 ISNULL() sistema, 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, aunque contenga las columnas adecuadas, ya que 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 columnas de la cláusula WHERE, lo que garantiza condiciones sargables.

Deshabilitar la extensión de bloqueo

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

• Nivel de tabla: puede deshabilitar la extensión de bloqueo en el nivel de tabla. Vea ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Para determinar qué 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 extensió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 extensión de bloqueo globalmente en la instancia de SQL Server. La extensión de bloqueo sirve para un propósito útil 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 extensión de bloqueo también ayuda a minimizar la memoria necesaria para realizar el seguimiento de los bloqueos. La memoria que SQL Server puede asignar dinámicamente para estructuras de bloqueo es finita. Por lo tanto, si deshabilita la extensión de bloqueo 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
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 memoria y bloqueo 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 un nuevo parámetro de inicio surta efecto. 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 SQL Server. Al activar la marca de seguimiento se evita cualquier escalación de bloqueo futura, pero no se invierte ninguna extensión de bloqueo que ya se haya 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 extensión del bloqueo.

Umbrales de extensión de bloqueo

La extensión de bloqueo puede producirse en una de las condiciones siguientes:

• Umbral de memoria alcanzado : se alcanza un umbral de memoria del 40 % de la memoria de bloqueo. Cuando la memoria de bloqueo supera el 24 % del grupo de búferes, se puede desencadenar una extensió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 % de la memoria de bloqueo. Este es el 40 % del 60 % del grupo de búferes o el 24 %. Si la memoria de bloqueo supera el límite del 60 por ciento (es mucho más probable si la extensión de bloqueo está deshabilitada), se producirán 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 5000, se desencadena una extensió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 descritos en la sección Impedir la extensió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 extensión de bloqueo. Microsoft recomienda habilitar esta marca de seguimiento solo para mitigar el bloqueo grave que provoca la extensión de bloqueo mientras se investigan otras opciones, como las descritas en este artículo.

Contenido relacionado

• Descripción y resolución de problemas de bloqueo en SQL Server
• Solución de problemas de consultas de ejecución lenta en SQL Server
• Guía sobre bloqueo de transacciones y control de versiones de filas
• ALTER TABLE (Transact-SQL)