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

Seleccione idioma Seleccione idioma
Id. de artículo: 323630 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

En esta página

Resumen

Extensión de bloqueo es el proceso de convertir muchos bloqueos concretas (como los bloqueos de fila o página) 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 almacenan 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. Generalmente, el comportamiento predeterminado de SQL Server se produce en extensión de bloqueo que se producen sólo en aquellos puntos donde podría mejorar el rendimiento o cuando debe reducir la memoria de bloqueo excesivo del sistema a un nivel más razonable. Sin embargo, algunos diseños de aplicación o consulta pueden desencadenar una extensión de bloqueo en un tiempo cuando no es conveniente y el bloqueo de tabla escalado puede bloquear a otros usuarios. En este artículo analiza cómo determinar si escalada del bloqueo está causando el bloqueo y cómo tratar con la extensión de bloqueo no deseados.

Más información

Cómo determinar si es que causan la extensión de bloqueo bloqueo

Extensión de bloqueo no hace que la mayoría de los problemas de bloqueo. Para determinar si se está produciendo escalada del bloqueo alrededor de la hora cuando experimenta problemas de bloqueo, iniciar una traza del Analizador de SQL que incluye el evento : Extensión de bloqueo . Si no ve los eventos : Extensión de bloqueo , extensión de bloqueo 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 escalada del bloqueo, compruebe que el bloqueo de tabla escalado está bloqueando otros usuarios.

Para obtener más información acerca de cómo identificar el encabezado Bloqueador de elementos y cómo identificar el recurso de bloqueo mantenido por el Bloqueador de encabezado que está bloqueando otro proceso de servidor (SPID) de identificadores, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
224453Comprender y resolver SQL Server 7.0 o problemas de bloqueo de 2000
Si el bloqueo que está bloqueando otros usuarios es distinto de un bloqueo de (nivel de tabla) TAB con un modo de bloqueo de S (compartido) o X (exclusivo), extensión de bloqueo no es el problema. En concreto, si el bloqueo TAB es un bloqueo con intención (como un modo de bloqueo de IS, IU o IX), esto no es el resultado de escalada del bloqueo. Si los problemas de bloqueo no se causadas por la extensión de bloqueo, consulte el artículo Q224453 para solucionar problemas de pasos.

Cómo evitar la extensión de bloqueo

La manera más sencilla y segura de evitar la extensión de bloqueo es mantener transacciones cortas y para reducir la superficie de bloqueo de consultas costosas de manera que no se superan los umbrales de escalado del bloqueo. Hay varias formas para obtener este objetivo de muchos de los cuales se muestran:
  • Dividir las operaciones de gran por lotes 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 ha encontrado que produjo una extensión de bloqueo que bloquea a otros usuarios:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    por quitar estos registros unos cientos a la vez, puede reducir drásticamente el número de bloqueos que se acumulan por transacción y evitar escalada del bloqueo. Por ejemplo: delete_more
    SET ROWCOUNT 500
    delete_more:
         DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • Reducir la superficie de bloqueo de la consulta realizando la consulta tan eficaz como sea posible. Exploraciones grandes o grandes números de búsquedas de marcador pueden aumentar la posibilidad de escalada del 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 Busque oportunidades para crear nuevos índices o para agregar columnas a un índice existente para quitar los recorridos de índice o tabla y para maximizar la eficacia del índice. Considere pegar la consulta en una ventana de consulta de 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.

    Un objetivo de esta optimización es que el índice busca devolver como algunas filas como sea posible para minimizar el costo de búsquedas de marcador (maximizar la selectividad del índice de la consulta determinado). Si SQL Server estima que un operador lógico Bookmark Lookup puede devolver varias filas, puede utilizar un PREFETCH para realizar la búsqueda de marcador. Si SQL Server utilizar PREFETCH para una búsqueda de marcador, debe aumentar el nivel de aislamiento de transacción de una parte de la consulta para lectura repetible para una parte de la consulta. Esto significa que lo que puede ser similar a una instrucción SELECT con un nivel de aislamiento lectura confirmada puede adquirir muchos miles de bloqueos de clave (en el índice agrupado y un índice no agrupado), lo que pueden producir una consulta para que supere los umbrales de escalado del bloqueo. Esto es especialmente importante si encuentra que el bloqueo de escalado es un bloqueo de tabla compartidos, que, sin embargo, no se ve normalmente en el nivel de aislamiento de lectura confirmada predeterminado. Si una cláusula de búsqueda de marcador WITH PREFETCH está causando el escalado, considere agregar columnas adicionales en el índice no agrupado que aparece en la búsqueda de índice o el operador lógico debajo el operador lógico Bookmark Lookup Index Scan en el plan de consulta. Es posible crear un índice de cobertura (un índice incluye todas las columnas de una tabla que se utilizaron 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 incluir todo en la lista de selección de columna es poco práctico.

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

    Para obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
    260652Combinación de bucle anidado que utiliza un "BOOKMARK LOOKUP.. .WITH PREFETCH" puede contener más de bloqueos
  • Extensión de bloqueo no se puede producir si un SPID distinto actualmente tiene un bloqueo de tabla incompatible. Extensión de bloqueo siempre escala para un bloqueo de tabla y nunca bloqueos de página. Además, si un intento de escalado del bloqueo falla porque SPID otra mantiene un bloqueo incompatible de TAB, la consulta que intentó escalado no se bloquea mientras se espera un bloqueo TAB. En su lugar, continúa adquirir bloqueos en su nivel más granular, original (fila, clave o página), intenta realizar periódicamente escalado 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 escalado del bloqueo. Un bloqueo IX (intención exclusivo) en el nivel de tabla no bloquea las filas o páginas, pero es aún no es compatible con una escalado S (compartido) o X (exclusivo) bloqueo TAB. Por ejemplo, suponga que debe ejecutar un trabajo por lotes que modifica un gran número de filas en la tabla mytable y que ha causado el bloqueo que se produce debido de escalada del bloqueo. Si este trabajo termina siempre en menos de una hora, podría crear un trabajo Transact-SQL que contiene el código siguiente y programar el trabajo nuevo para iniciar varios minutos antes de 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 MiTabla durante una hora, que impide la extensión de bloqueo en la tabla durante ese tiempo. Este lote no se puede modificar los datos o bloquear otras consultas (a menos que la otra consulta fuerza 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 escalada del bloqueo habilitando el indicador de traza 1211. Sin embargo, este indicador de traza deshabilita todo escalada del bloqueo global en la instancia de SQL Server. Extensión de bloqueo tiene una finalidad muy útil en SQL Server bien maximizar la eficacia de las consultas que de lo contrario se demoró hacia abajo por la sobrecarga de adquirir y liberar varios miles de bloqueos. Bloquear escalado también ayuda a minimizar la memoria necesaria para realizar un seguimiento de bloqueos. La memoria que SQL Server puede asignar dinámicamente para estructuras de bloqueo es finita, de modo que si deshabilita escalada del bloqueo y el bloqueo de memoria grande suficiente, intentos de asignar bloqueos adicionales para cualquier consulta que fallan y se produce el error siguiente:

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 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 propio puede impedir que los usuarios o llevar a un tiempo de recuperación de base de datos largo si reiniciar el servicio.

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

Los otros métodos de extensión de bloqueo de evitar que se describen anteriormente en este artículo están mejores opciones de habilitar el indicador de traza. Además, los otros métodos suelen producir mejor rendimiento de la consulta que deshabilitar la extensión de bloqueo para la instancia completa. Microsoft recomienda habilitar este indicador de traza sólo mitigar bloqueo grave provocada por la extensión de bloqueo mientras otras opciones, como las descritas anteriormente en este artículo, se está investigando. Para habilitar el indicador de traza de manera que está activado siempre que SQL Server se inicie, agregarlo como un parámetro de inicio del servidor.

Para agregar un parámetro de inicio de servidor, haga clic con el botón secundario en el servidor en el Administrador corporativo de SQL, haga clic en Propiedades y a continuación, en la ficha General , haga clic en Parámetros de inicio y, a continuación, agregue el parámetro siguiente de (exactamente como se muestra):
-T1211
Debe pasar el servicio SQL Server para que surta efecto un nuevo parámetro de inicio. Si ejecuta la consulta siguiente 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 activa el servicio de SQL Server. Activar el indicador de traza impide cualquier bloqueo futuras extensiones, pero no invierte las extensiones de bloqueo ya han tenido lugar una transacción activa.

Propiedades

Id. de artículo: 323630 - Última revisión: jueves, 22 de febrero de 2007 - Versión: 10.3
La información de este artículo se refiere a:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Palabras clave: 
kbmt kbinfo KB323630 KbMtes
Traducción automática
IMPORTANTE: Este artículo ha sido traducido por un software de traducción automática de Microsoft (http://support.microsoft.com/gp/mtdetails) en lugar de un traductor humano. Microsoft le ofrece artículos traducidos por un traductor humano y artículos traducidos automáticamente para que tenga acceso en su propio idioma a todos los artículos de nuestra base de conocimientos (Knowledge Base). Sin embargo, los artículos traducidos automáticamente pueden contener errores en el vocabulario, la sintaxis o la gramática, como los que un extranjero podría cometer al hablar el idioma. Microsoft no se hace responsable de cualquier imprecisión, error o daño ocasionado por una mala traducción del contenido o como consecuencia de su utilización por nuestros clientes. Microsoft suele actualizar el software de traducción frecuentemente.
Haga clic aquí para ver el artículo original (en inglés): 323630

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com