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

Resumen

La extensión de bloqueo es el proceso de convertir muchos bloqueos específicos (como bloqueos de fila o página) en bloqueos de tabla. Microsoft SQL Server determina dinámicamente cuándo se debe realizar la extensión de bloqueo. 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 extensión de bloqueo solo se produzca en esos momentos en que mejoraría el rendimiento o cuando se debe reducir la memoria excesiva de bloqueo del sistema a un nivel más razonable. Sin embargo, algunos diseños de aplicación o consulta podrían desencadenar la extensión de 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 extensión de bloqueo está causando bloqueos y cómo tratar la extensión de bloqueo no deseada.

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

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 la extensión de bloqueo se está produciendo en 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 encabezados y el recurso de bloqueo que mantiene 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 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 (por ejemplo, un modo de bloqueo de IS, IU o IX), esto no se debe a la extensión del bloqueo. Si los problemas de bloqueo no se deben a la extensión de bloqueo, consulte los pasos de solución de problemas de solución de problemas de bloqueo de SQL Server: Descripción y resolución de problemas de bloqueo 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. Hay varios métodos para lograr este objetivo, incluidas las siguientes estrategias:

• 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. Esto impedirá la extensión del 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;
  

• 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 que 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.

• 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 (preventivo exclusivo) en el nivel de tabla no bloquea ninguna fila o página, pero todavía no es 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. Esto evita 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).

• 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. Esto puede ocurrir 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 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, 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 de 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. También puede usar el evento Lock:Escalation y examinar 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.

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 tiempo de recuperación de base de datos largo si reinicia el servicio 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 que se describen 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 causado por la extensión de bloqueo, mientras que otras opciones, como las descritas en este artículo, se están investigando.

Consulte también

• 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