Del boletín técnico de SQL Server: cómo resolver un interbloqueo

Boletín técnico de SQL Server

Trata el tema de este problema: cómo resolver un interbloqueo
Para la documentación actualizada y recomendaciones que son aplicables a este tema en las versiones posteriores de SQL Server, consulte los siguientes sitios Web:

Solución de problemas de interbloqueo
Detectar y finalizar los interbloqueos

Si está intentando solucionar problemas generales de rendimiento, consulte la siguiente documentación:

Mejorar el rendimiento de las consultas SQL

Objetivo

Para identificar, solucionar problemas y recomendar una solución para resolver un interbloqueo.

Introducción

Este artículo examina una situación de interbloqueo y proporciona los pasos necesarios para resolver el interbloqueo. Cada interbloqueo puede ser diferente y puede deberse a varias variables de entorno diferente. La información proporcionada en este artículo puede ayudarle a identificar y resolver un interbloqueo.

Estudio de caso

En un estudio de caso, examinaremos un sistema 911 que tiene seis operadores. Durante la actividad pico, experiencias de la aplicación front-end de Microsoft Visual Basic que utilizan las conexiones interrumpidas. Debido a las conexiones interrumpidas, los operadores deben volver a ingresar los datos. Para un sistema 911 que funciona 24 horas al día, siete días a la semana, este comportamiento es inaceptable.

¿Qué es un bloqueo?

Un interbloqueo se produce cuando dos sistema servidor ID (SPID) están esperando un recurso y ningún proceso puede avanzar porque otro proceso está impidiendo que éste obtener el recurso.

Subproceso del Administrador de bloqueos busca interbloqueos. Cuando el algoritmo de detección de interbloqueo de un administrador de bloqueos detecta un interbloqueo, el Administrador de bloqueos elige como víctima uno de los SPID. El Administrador de bloqueos inicia un mensaje de error 1205 que se envía al cliente, y el Administrador de bloqueos elimina el SPID. Matando el SPID libera los recursos y permite que los otro SPID continuar. Matando el SPID que es víctima del interbloqueo es lo que hace que la conexión rota que experimenta la aplicación cliente de Visual Basic.

En una aplicación bien diseñada, la aplicación cliente debe interceptar el error 1205, volver a conectarse a SQL Server y, a continuación, volver a enviar la transacción.

Aunque se pueden minimizar los interbloqueos, que no pueden evitarse completamente. Por esta razón la aplicación front-end debe estar diseñada para controlar los interbloqueos.

Cómo identificar un interbloqueo

Paso 1

Para identificar un interbloqueo, primero debe obtener información del registro. Si sospecha que un interbloqueo, debe recopilar información sobre el (SPID) y los recursos que participan en el interbloqueo. Para ello, agregue el - T1204 - T3605 inicio parámetros y a SQL Server. Para agregar estos dos parámetros de inicio, siga estos pasos:
  • Inicie el Administrador corporativo de SQL Server.
  • Seleccione y, a continuación, haga clic en el servidor.
  • Haga clic en Propiedades.
  • Haga clic en parámetros de inicio.
  • En el cuadro de diálogo Parámetros de inicio , escriba -T1204 en el texto de parámetros de cuadro y, a continuación, haga clic en Agregar.
  • En el cuadro de texto de parámetros , escriba
    -T3605y, a continuación, haga clic en Agregar.
  • Haga clic en Aceptar.

Los parámetros de inicio surtirán efecto cuando SQL Server se detiene y, a continuación, vuelva a iniciar.

-T1204 inicio parámetro recopila información acerca del proceso y los recursos cuando el algoritmo de detección de interbloqueo encuentra un interbloqueo. -T3605 parámetro de inicio escribe esta información en los registros de errores de SQL Server.

-T1205 inicio parámetro recopila información cada vez que se comprueba el algoritmo de bloqueo para un interbloqueo, no cuando se encuentra un interbloqueo. No es necesario utilizar el T1205 inicio parámetro-.


Si utiliza el T1205 inicio parámetro-, lo siguiente es un ejemplo de la salida que se incluirá en el registro de errores de SQL Server:

2003-05-14 11:46:26.76 spid4     Starting deadlock search 12003-05-14 11:46:26.76 spid4     Target Resource Owner:
2003-05-14 11:46:26.76 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4
2003-05-14 11:46:26.76 spid4 End deadlock search 1 ... a deadlock was not found.
2003-05-14 11:46:26.76 spid4 ----------------------------------
2003-05-14 11:46:31.76 spid4 ----------------------------------
2003-05-14 11:46:31.76 spid4 Starting deadlock search 2



En ocasiones, podría no ser capaz de detener y reiniciar SQL Server. En ese caso, puede utilizar el analizador de consultas para ejecutar el siguiente comando para habilitar los indicadores de traza de interbloqueo.

Nota: De esta forma que puede recopilar información acerca de los interbloqueos inmediatamente. "-1" indica todos los SPID.

dbcc traceon (1204, 3605, -1)go
dbcc tracestatus(-1)
go


Paso 2

A continuación, debe recopilar una traza del analizador de SQL. Si activa el indicador de traza de interbloqueo, obtendrá la mayoría de la información necesaria, pero no siempre. Por ejemplo, en un caso práctico el resultado del indicador de seguimiento identifica que un sistema sp_cursoropen procedimiento almacenado y un "UPDATE tblQueuedEvents set notifyid = 3, ResynchDate" instrucción implicados en un interbloqueo. Por desgracia, no conoce la definición del procedimiento almacenado del sistema sp_cursoropen . También tiene la instrucción de actualización completa porque se truncó.

Analizador de SQL puede obtener las instrucciones completas además de los planes de ejecución de las instrucciones. Una traza del analizador de SQL también tiene un evento de bloqueo para "interbloqueo" y "cadena de interbloqueos". "Interbloqueo" corresponde al indicador - T1204 y "cadena de interbloqueos" corresponde al indicador - T1205. Activación de los indicadores de traza de interbloqueo y ejecutando una traza del analizador de SQL durante la aparición de un interbloqueo deben proporcionarle los datos que debe tener para solucionar un interbloqueo. En este caso y en otros, ejecutando el analizador de SQL cambia el tiempo de ejecución lo suficientemente para evitar el interbloqueo. Por lo tanto, normalmente se captura la información de interbloqueo con los indicadores de traza y, a continuación, ejecute al analizador de SQL.

Solucionar problemas de un interbloqueo

Una vez que se produce un interbloqueo, puede recopilar información sobre el interbloqueo utilizando la
sqldiag utilidad y con el analizador de SQL. En la salida del archivo SQLDiag.txt, busque una entrada "Espera para gráfico". Una entrada "espera-gráfico" indica que se detectó un interbloqueo.

El siguiente es un ejemplo del resultado que puede aparecer en el registro de errores de SQL Server cuando se utiliza el T1205 inicio parámetro-.

2003-05-05 15:11:50.80 spid4    Wait-for graph2003-05-05 15:11:50.80 spid4    Node:1
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4 Victim Resource Owner:
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:60 ECID:0 Ec:(0x1F1BB5B0) Value:0x193
2003-05-05 15:11:50.80 spid4 Requested By:
2003-05-05 15:11:50.80 spid4 Input Buf: RPC Event: sp_cursoropen;1
2003-05-05 15:11:50.80 spid4 SPID: 55 ECID: 0 Statement Type: EXECUTE Line #: 1
2003-05-05 15:11:50.80 spid4 Owner:0x1937f2a0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:0
2003-05-05 15:11:50.80 spid4 Grant List 0::
2003-05-05 15:11:50.80 spid4 KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0

2003-05-05 15:11:50.80 spid4 Node:2
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4 Requested By:
2003-05-05 15:11:50.80 spid4 Input Buf: Language Event: Update tblQueuedEvents Set NotifyID = 2, ResynchDate
2003-05-05 15:11:50.80 spid4 SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
2003-05-05 15:11:50.80 spid4 Owner:0x1936e420 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0
2003-05-05 15:11:50.80 spid4 Grant List 0::
2003-05-05 15:11:50.80 spid4 KEY: 8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode: X Flags: 0x0



En la entrada de "Espera para gráfico" tiene nodo 1 y nodo 2. Cada nodo tiene una sección de concesión y una solicitud. La sección de concesión es la "lista de concesión" y la sección de la solicitud es el "solicitar."
En cada nodo, puede identificar lo siguiente:
  • El SPID.
  • El comando que se estaba ejecutando el SPID.
  • El recurso.
  • El modo de bloqueo en el recurso.

Por ejemplo, en el nodo 1, la lista Grant, SPID 55 habían concedido un bloqueo de actualización, modo: U, en recurso clave: 8:1653632984:2. 8 = DBID, 1653632984 = ObjectID y 2 = Indid. Para obtener el número de identificación de la base de datos, ejecute el procedimiento sp_helpdb almacenados. Para obtener la tabla, ejecute el siguiente código:

select * from sysobjects where id = 1653632984


Para obtener el índice, ejecute el siguiente código:
select * from sysindexes where indid = 2 and id = 1653632984

Si IndexId es igual a 2, sabrá que el índice es un índice no agrupado. El comando que se estaba ejecutando el SPID 55 era el procedimiento sp_cursoropen almacenados.

En el nodo 2, la lista Grant, 60 SPID se concedió un bloqueo exclusivo, modo: X, en recurso clave: 8:1653632984:1. 8 = DBID, 1653632984 = ObjectID, 1 = Indid. Esto es en la misma tabla pero 1 es el índice agrupado. El comando que se estaba ejecutando el SPID 60 era:
Update tblQueuedEvents Set NotifyID = 2, ResynchDate

Un IndexId que es igual a 1 es un índice agrupado.

Un IndexId que es igual a 2 es un índice no agrupado.

Nota: Los interbloqueos son dependiente del tiempo.

A continuación, en el nodo 1, solicitado por el SPID 55, un bloqueo compartido, modo: S, en IndexId = 1. En el nodo 2, solicitar por 60 SPID solicita un bloqueo exclusivo, modo: X, en IndexId = 2. Debido a estas peticiones de bloqueo se producen al mismo tiempo, se produce el interbloqueo. Cada SPID con bloqueos otorgados impide que los bloqueos solicitados puedan continuar

La siguiente tabla muestra la tabla de compatibilidad de bloqueo. Para obtener más información acerca de la compatibilidad de bloqueo, vea el tema "Compatibilidad de bloqueo" en los libros en pantalla de SQL Server 2000.

Tabla de compatibilidad de bloqueo
Modo solicitadoISSUIXSEISX
Intención compartida (IS)No
Compartido (S)NoNoNo
Actualización (U)
NoNoNoNo
Intención exclusiva (IX)NoNoNoNo
Compartido con intención exclusivo (SIX)
NoNoNoNoNo
Exclusivo (X)NoNoNoNoNoNo


A continuación, examinando el resultado, identificar 1653632984 ObjectId como la tabla tblQueuedEvents y obtenga un procedimiento almacenado sp_help para la tabla de salida. Existían dos índices en la tabla. Los dos índices eran ix_tblQueuedEvents y PK_tblQueuedEvent. ix_tblQueuedEvents es un índice agrupado en ResynchDate y PK_tblQueuedEvent es una clave principal, el índice no agrupado único en EventSID.


La traza del analizador de SQL no pudo capturar la aparición de interbloqueo. Recuerde, los interbloqueos son depende del tiempo. La sobrecarga del analizador de SQL de había agregado probablemente algún tiempo a la ejecución de uno de los procesos y que impide que se dé una situación de interbloqueo SQL Profiler. Sin embargo, proporcionó información que puede utilizar para solucionar el problema. Encontrar la instrucción de tblQueuedEvents de actualización completa para que sea similar al siguiente:

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16' where eventSID = 73023
También se encuentra el plan de ejecución. Todavía no tiene la instrucción del procedimiento completo sp_cursoropen almacenado, pero tiene suficiente información para recomendar una solución que resuelva el interbloqueo.

Este es el plan de ejecución.

Nota: Este plan de ejecución concreto se lee de derecha a izquierda y en orden descendente.

StmtText                                                                                                                                 


--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16'
where eventSID = 73023


|--Clustered Index
Update(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[ix_tblQueuedEvents ]),
SET:([tblQueuedEvents].[NotifyID]=[@1],
[tblQueuedEvents].[ResynchDate]=[Expr1004]))
|--Top(1)



|--Compute Scalar(DEFINE:([Expr1004]=Convert([@2])))



|--Index
Seek(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[PK_tblQueuedEvents]),
SEEK:([tblQueuedEvents].[EventSID]=[@3])

Recomendar una solución para resolver el interbloqueo

Tenga en cuenta que la instrucción UPDATE es realizar una "actualización de índice agrupado" en el índice agrupado. Por lo tanto, el índice no agrupado y el índice agrupado deben ambas se actualizan. El índice agrupado es ix_tblQueuedEvents y el índice no agrupado es PK_tblQueuedEvents. Para realizar las actualizaciones, la instrucción UPDATE debe obtener bloqueos exclusivos sobre ambos índices. Estos dos índices son los índices que están implicados en el interbloqueo. De revisar las trazas del analizador de SQL, no aparece ninguna consulta que usa el ResynchDate en la cláusula WHERE. Todas las instrucciones eran muy específicas y usan la EventSID en la cláusula WHERE. Una mejor elección de un índice agrupado sería EventSID. Con esta información y una conversación con el cliente, descubrimos que el índice ResynchDate era antiguo y no era necesario. Se recomienda que el cliente, colocar el índice ix_tblQueuedEvents en ResynchDate y que realizan PK_tblQueuedEvent un índice agrupado. Esto resuelve la situación de interbloqueo.

Esto es sólo un ejemplo de un caso de interbloqueo incluye bloqueos. Interbloqueos también pueden implicar paralelismo e implican subprocesos. Pueden implicar uno, dos, tres o más SPID y recursos. Con cualquier caso de interbloqueo, debe obtener – T1204 salida de parámetro de inicio y la traza del analizador de SQL para identificar y solucionar problemas para resolver el interbloqueo. La situación de interbloqueo implicarán diferentes procesos y recursos. Por lo tanto, las soluciones varían de un caso a otro. Incluyen métodos típicos que puede utilizar para resolver interbloqueos:
  • Agregar y quitar índices.
  • Agregar sugerencias de índice.
  • Modificar la aplicación para tener acceso a los recursos en un modelo similar.
  • Eliminación de actividad de la transacción como desencadenadores. De forma predeterminada, los desencadenadores son transaccionales.
  • Mantener transacciones lo más cortas posible.
Propiedades

Id. de artículo: 832524 - Última revisión: 24 ene. 2017 - Revisión: 2

Microsoft SQL Server 2000 Standard Edition, Windows Server 2008 Datacenter, Windows Server 2008 Enterprise, Windows Server 2008 Standard, Windows Server 2008 R2 Enterprise, Windows Server 2008 R2 Datacenter, Windows Server 2008 R2 Standard, Windows Server 2008 R2 Foundation, Windows Server 2012 Datacenter, Windows Server 2012 Foundation, Windows Server 2012 Standard, Windows Server 2012 R2 Datacenter, Windows Server 2012 R2 Essentials, Windows Server 2012 R2 Standard, Windows Server 2012 R2 Foundation

Comentarios