Cómo resolver problemas de bloqueo causados por la extensión de bloqueo en SQL Server

Resumen

Extensión de bloqueo es el proceso de convertir muchos bloqueos más específicos (por ejemplo, los bloqueos de filas o páginas) en bloqueos de tabla. Microsoft SQL Server determina dinámicamente cuándo realizar la extensión de bloqueo. Al tomar esta decisión, SQL Server tiene en cuenta el número de bloqueos que se mantienen en un análisis determinado, el número de bloqueos mantenidos por toda la transacción y la memoria que se utiliza para bloqueos en el sistema como un todo. Normalmente, predeterminada de SQL Server da como resultado la extensión de bloqueo que se producen sólo en los puntos donde podría mejorar el rendimiento o cuando debe reducir la memoria de bloqueo excesivo del sistema a un nivel más razonable. No obstante, algunos diseños de aplicación o consulta pueden desencadenar la extensión de bloqueo en un momento cuando no es conveniente, y el bloqueo de tabla escalados puede bloquear a otros usuarios. Este artículo describe cómo determinar si la extensión de bloqueo está produciendo bloqueo y cómo lidiar con la extensión de bloqueo no deseados.

Más información

Cómo determinar si la extensión de bloqueo está causando el bloqueo

Extensión de bloqueo no causa más problemas de bloqueo. Para determinar si la extensión de bloqueo está ocurriendo alrededor del tiempo cuando experimenta problemas de bloqueo, iniciar una traza del analizador de SQL que incluya el evento : Extensión de bloqueo . Si no ve los eventos : Extensión de bloqueo , no se produce la extensión de bloqueo en el servidor y la información de este artículo no se aplica a su situación.

Si se está produciendo escalada del bloqueo, compruebe que el bloqueo de tabla escalados está bloqueando otros usuarios.

Para obtener más información acerca de cómo identificar el bloqueador de cabeza y cómo identificar el recurso de bloqueo mantenido por el bloqueador de cabeza que está bloqueando otro SPID (ID) de proceso de servidor, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

224453 entender y resolver problemas de bloqueo de 2000 o de SQL Server 7.0

Si el bloqueo que está bloqueando otros usuarios es distinto de un ficha () bloqueo de tabla con un modo de bloqueo de S (compartido) o X (exclusivo), extensión de bloqueo no es el problema. En particular, si el bloqueo de la ficha es un bloqueo de intención (como un modo de bloqueo de IS, IU o IX), esto no es el resultado de la extensión de bloqueo. Si los problemas de bloqueo no se son causados por la extensión de bloqueo, consulte el artículo Q224453 para pasos para solucionar problemas.

Cómo evitar la extensión de bloqueo

La forma más sencilla y segura para evitar la extensión de bloqueo es mantener transacciones cortas y para reducir la huella de bloqueo de consultas costosas por lo que no se superen los umbrales de escalado del bloqueo. Hay varias maneras de obtener este objetivo, muchos de los cuales se enumeran:
  • Interrumpir las operaciones por lotes de gran tamaño en varias operaciones más pequeñas. Por ejemplo, supongamos que ejecutó la consulta siguiente para quitar registros antiguos de varios cientos de miles de una tabla de auditoría y, a continuación, se encuentra que provocó una extensión de bloqueo que bloquea a otros usuarios:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    Al quitar estos registros de unos pocos cientos a la vez, puede reducir considerablemente el número de bloqueos que se acumulan por transacción y evitar la extensión de bloqueo. Por ejemplo:
    SET ROWCOUNT 500delete_more:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • Reducir la huella de bloqueo de la consulta realizando la consulta tan eficaz como sea posible. Las exploraciones grandes o un gran número de búsquedas de marcador puede aumentar las posibilidades de extensión de bloqueo; Además, aumenta la posibilidad de interbloqueos y generalmente afecta negativamente al rendimiento y simultaneidad. Después de encontrar la consulta que hace que la extensión de bloqueo, busca buscar oportunidades para crear nuevos índices o para agregar columnas a un índice existente para quitar recorridos de índice o tabla y para maximizar la eficacia del índice. Considere la posibilidad de pegar la consulta en una ventana de consulta del analizador de consultas para realizar un análisis automático de índice en ella. Para ello, en el menú consulta , haga clic en Asistente para optimización de índices en SQL Server 2000 o haga clic en Realizar análisis de índices en SQL Server 7.0.

    Uno de los objetivos de esta optimización es hacer índice búsquedas devuelven filas tan pocos como sea posible para minimizar el costo de las búsquedas de marcador (maximizar la selectividad del índice para la consulta en particular). Si SQL Server estima que un operador lógico Bookmark Lookup puede devolver varias filas, puede utilizar una búsqueda para realizar la búsqueda de marcador. Si SQL Server utiliza la búsqueda para una búsqueda de marcador, debe aumentar el nivel de aislamiento de una parte de la consulta de lectura repetible para una parte de la consulta. Esto significa que lo que puede ser similar a una instrucción SELECT en un nivel de aislamiento de lectura confirmada puede adquirir varios miles de bloqueos de clave (en el índice agrupado y un índice no agrupado), que pueden hacer que la consulta a que se superen los umbrales de escalado del bloqueo. Esto es especialmente importante si piensa que el bloqueo escalado es un bloqueo de tabla compartido, que, sin embargo, no se ve normalmente en el nivel de aislamiento de lectura confirmada por defecto. Si una cláusula de consulta con búsqueda de marcador está causando la escalada, considere la posibilidad de agregar columnas adicionales en el índice no agrupado que aparece en la búsqueda de índice o el operador lógico a continuación el operador lógico Bookmark Lookup Index Scan en el plan de consulta. Es posible crear un índice de cobertura (un índice que incluye todas las columnas de una tabla que se han utilizado en la consulta), o al menos un índice que cubre las columnas que se utilizaron para criterios de unión o en la cláusula WHERE si con todo incluido en la lista Seleccionar columna es poco práctico.

    Una combinación de bucle anidado también puede utilizar la búsqueda, y esto hace que el mismo comportamiento de bloqueo.

    Para obtener más información, haga clic en el siguiente número de artículo para verlo en Microsoft Knowledge Base:

    Combinación de bucle de Nested 260652 que utiliza una búsqueda de marcador "... CON captura previa"puede retener bloqueos más

  • Extensión de bloqueo no puede ocurrir si un SPID diferente tiene un bloqueo de tabla incompatible. Extensión de bloqueo siempre se escala a un bloqueo de tabla y nunca bloqueos de página. Además, si se produce un error en un intento de escalamiento de bloqueo porque SPID otra mantiene un bloqueo incompatible de ficha, la consulta que ha intentado escalamiento no se bloquea mientras espera un bloqueo de la ficha. En su lugar, continúa adquirir bloqueos en su nivel más granular, original (fila, página o clave), intenta realizar periódicamente escalamiento adicional. Por lo tanto, un método para evitar la extensión de bloqueo en una tabla determinada es adquirir y mantenga un bloqueo en una conexión diferente que no es compatible con el tipo de bloqueo escalados. Un bloqueo IX (intención exclusivo) en el nivel de tabla no bloquea las filas o las páginas, pero es aún no es compatible con escalados S (compartido) o X (exclusivo) bloqueo de ficha. Por ejemplo, supongamos que debe ejecutar un trabajo por lotes que modifica un gran número de filas de la tabla mytable y que ha causado el bloqueo que se produce debido a la extensión de bloqueo. Si este trabajo siempre se realiza en menos de una hora, podría crear un trabajo Transact-SQL que contenga el siguiente código y programar el trabajo de nuevo para iniciar varios minutos antes de la hora de inicio del trabajo por lotes:
    BEGIN TRANSELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN
    Esta consulta se adquiere y mantiene un bloqueo IX en MiTabla durante una hora, lo que impide que la extensión de bloqueo en la tabla durante ese tiempo. Este lote no modifica ningún dato o bloquear otras consultas (a menos que la otra consulta obliga a un bloqueo de tabla con la sugerencia TABLOCK o si un administrador ha deshabilitado los bloqueos de página o fila utilizando un sp_indexoption procedimiento almacenado).
Además, puede deshabilitar la extensión de bloqueo habilitando el indicador de traza 1211. Sin embargo, este indicador de traza deshabilita toda la extensión de bloqueo globalmente en la instancia de SQL Server. Extensión de bloqueo tiene una finalidad muy útil en SQL Server mediante la maximización de la eficacia de las consultas que de lo contrario se ralentizaban por la sobrecarga de adquirir y liberar varios miles de bloqueos. Extensión de bloqueo 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 estructuras de bloqueo es finita, por lo que si deshabilita escalada del bloqueo y el bloqueo de memoria crece lo suficientemente grande, puede producir un error si se intenta asignar bloqueos adicionales para cualquier consulta y se produce el siguiente 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 para comprobar la configuración de bloqueos y memoria de SQL Server.
Nota: Cuando se produce un error de "1204", detiene el procesamiento de la instrucción actual y hace que una reversión de la transacción activa. La operación de deshacer puede impedir que los usuarios o provocar un tiempo de recuperación de base de datos long si reinicia el servicio de SQL Server.

Mediante una sugerencia de bloqueo como ROWLOCK sólo modifica el plan de bloqueo inicial. Sugerencias de bloqueo no impiden la extensión de bloqueos.


Los otros métodos para evitar la extensión de bloqueo que se describen anteriormente en este artículo son las mejores opciones de habilitar el indicador de traza. Además, los demás métodos suelen producen mejor rendimiento de la consulta que deshabilitar escalada del bloqueo para toda la instancia. Microsoft recomienda habilitar este indicador de traza sólo mitigar bloqueo grave causado por la extensión de bloqueo mientras otras opciones, como los indicados anteriormente en este artículo, se está investigando. Habilitar una marca de seguimiento para que se activa siempre que se inicia SQL Server, agréguelo como un parámetro de inicio del servidor.

Para agregar un parámetro de inicio del servidor, haga clic derecho en el Administrador corporativo de SQL server, haga clic en Propiedades, en la ficha General , haga clic en Parámetros de inicioy, a continuación, agregue el parámetro siguiente (exactamente como se muestra):
-T1211
Debe recorrer el servicio SQL Server para un nuevo parámetro de inicio surtan efecto. Si ejecuta la siguiente consulta en el analizador de consultas el indicador de traza surte efecto inmediatamente:
DBCC TRACEON (1211, -1)
Sin embargo, si no agrega el -T1211 parámetro de inicio, se pierde el efecto de un comando traceon cuando se reanude el ciclo de servicio de SQL Server. Activar el indicador de traza impide que las extensiones de bloqueo futuras, pero no invierte las extensiones de bloqueo que ya se han producido en una transacción activa.
Propiedades

Id. de artículo: 323630 - Última revisión: 17 ene. 2017 - Revisión: 1

Comentarios