Detectar y finalizar interbloqueos

Un interbloqueo se produce cuando dos o más tareas se bloquean entre sí permanentemente teniendo cada tarea un bloqueo en un recurso que las otras tareas intentan bloquear. En el siguiente gráfico se presenta una vista de alto nivel de un estado de interbloqueo donde:

• La tarea T1 tiene un bloqueo en el recurso R1 (indicado por la flecha de R1 a T1) y ha solicitado un bloqueo en el recurso R2 (indicado por la flecha de T1 a R2).

• La tarea T2 tiene un bloqueo en el recurso R2 (indicado por la flecha de R2 a T2) y ha solicitado un bloqueo en el recurso R1 (indicado por la flecha de T2 a R1).

• Dado que ninguna tarea puede continuar hasta que un recurso esté disponible y ningún recurso puede liberarse hasta que continúe una tarea, existe un estado de interbloqueo.

El SQL Server Database Engine (Motor de base de datos de SQL Server) detecta automáticamente los ciclos de interbloqueo en SQL Server. El Motor de base de datos elige una de las sesiones como sujeto del interbloqueo y la transacción actual finaliza con un error para romper el interbloqueo.

Recursos que pueden causar interbloqueos

Cada sesión de usuario puede tener una o más tareas en ejecución y cada tarea puede adquirir o esperar para adquirir una serie de recursos. Los siguientes tipos de recursos pueden causar bloqueos que podrían dar como resultado un interbloqueo.

• Bloqueos. Esperar para adquirir bloqueos en recursos, como objetos, páginas, filas, metadatos y aplicaciones, puede causar un interbloqueo. Por ejemplo, la transacción T1 tiene un bloqueo compartido (S) en la fila f1 y está esperando para obtener un bloqueo exclusivo (X) en f2. La transacción T2 tiene un bloqueo compartido (S) en f2 y está esperando para obtener un bloqueo exclusivo (X) en la fila f1. Esta situación tiene como resultado un ciclo de bloqueo en el que T1 y T2 esperan que la otra transacción libere los recursos bloqueados.

• Subprocesos de trabajo. Una tarea en cola que espera un subproceso de trabajo disponible puede causar un interbloqueo. Si la tarea en cola es propietaria de recursos que están bloqueando todos los subprocesos de trabajo, se generará un interbloqueo. Por ejemplo, la sesión S1 inicia una transacción y adquiere un bloqueo compartido (S) en la fila f1 y, a continuación, se suspende. Las sesiones activas que se ejecutan en todos los subprocesos de trabajo disponibles intentan adquirir bloqueos exclusivos (X) en la fila f1. Dado que la sesión S1 no puede adquirir un subproceso de trabajo, no puede confirmar la transacción y liberar el bloqueo de la fila f1. Esta situación tiene como resultado un interbloqueo.

• Memoria. Cuando hay solicitudes simultáneas esperando concesiones de memoria que no se pueden satisfacer con la memoria disponible, puede producirse un interbloqueo. Por ejemplo, dos consultas simultáneas, C1 y C2, se ejecutan como funciones definidas por el usuario que adquieren 10 MB y 20 MB de memoria respectivamente. Si cada consulta necesita 30 MB y el total de memoria disponible es 20 MB, C1 y C2 tienen que esperar a que la otra consulta libere memoria, y esta situación tiene como resultado un interbloqueo.

• Recursos relacionados con la ejecución de consultas en paralelo. Los subprocesos de coordinador, productor o consumidor asociados a un puerto de intercambio pueden bloquearse entre sí y provocar un interbloqueo si incluyen al menos otro proceso que no forma parte de la consulta en paralelo. Además, cuando se inicia la ejecución de una consulta en paralelo, SQL Server determina el grado de paralelismo, o el número de subprocesos de trabajo, en función de la carga de trabajo actual. Si la carga de trabajo del sistema cambia de forma inesperada, por ejemplo, si se empiezan a ejecutar nuevas consultas en el servidor o el sistema se queda sin subprocesos de trabajo, se puede producir un interbloqueo.

• Conjuntos de resultados activos múltiples (MARS). Estos recursos se utilizan para controlar la intercalación de varias solicitudes activas en MARS (vea Entorno de ejecución de lotes y MARS).

  • Recurso de usuario. Cuando un subproceso espera un recurso que potencialmente está controlado por una aplicación de usuario, se considera que el recurso es externo o de usuario y se trata como un bloqueo.

  • Exclusión mutua de sesión. Las tareas que se ejecutan en una sesión se intercalan, lo que significa que sólo puede ejecutarse una tarea en la sesión en un momento dado. Antes de que se pueda ejecutar la tarea, debe tener acceso exclusivo a la exclusión mutua de sesión.

  • Exclusión mutua de transacción. Todas las tareas que se ejecutan en una transacción se intercalan, lo que significa que sólo puede ejecutarse una tarea en la transacción en un momento dado. Antes de que se pueda ejecutar la tarea, debe tener acceso exclusivo a la exclusión mutua de transacción.

  Para que una tarea se ejecute en MARS, debe adquirir la exclusión mutua de sesión. Si la tarea se ejecuta en una transacción, debe adquirir la exclusión mutua de transacción. Esto garantiza que sólo una tarea esté activa en un momento dado en una sesión determinada y en una transacción concreta. Una vez adquiridas las exclusiones mutuas necesarias, se puede ejecutar la tarea. Cuando finaliza la tarea, o se produce en medio de la solicitud, primero liberará la exclusión mutua de transacción seguida de la exclusión mutua de sesión en el orden inverso a la adquisición. Sin embargo, pueden producirse interbloqueos con estos recursos. En el ejemplo de código siguiente hay dos tareas, la solicitud de usuario U1 y la solicitud de usuario U2, que se ejecutan en la misma sesión.

  U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
  U2:    Rs2=Command2.Execute("select colA from sometable");
  

  El procedimiento almacenado que se ejecuta a partir de la solicitud de usuario U1 ha adquirido la exclusión mutua de sesión. Si el procedimiento almacenado tarda mucho tiempo en ejecutarse, el Motor de base de datos considerará que el procedimiento almacenado está esperando la intervención del usuario. La solicitud de usuario U2 está esperando la exclusión mutua de sesión mientras que el usuario está esperando el conjunto de resultados de U2, y U1 está esperando un recurso de usuario. Éste es un estado de interbloqueo que se ilustra de forma lógica como:

Detección de interbloqueos

Todos los recursos enumerados en la sección anterior participan en el esquema de detección de interbloqueos del Motor de base de datos. La detección de interbloqueos la realiza un subproceso de supervisión de bloqueos que periódicamente inicia una búsqueda por todas las tareas de una instancia del Motor de base de datos. En los siguientes puntos se describe el proceso de búsqueda:

• El intervalo predeterminado es de 5 segundos.

• Si el subproceso de supervisión de bloqueos encuentra interbloqueos, el intervalo de detección de interbloqueos pasará de 5 segundos a hasta sólo 100 milisegundos, en función de la frecuencia de los interbloqueos.

• Si el subproceso de supervisión de bloqueos deja de encontrar interbloqueos, el Motor de base de datos aumentará los intervalos entre las búsquedas a 5 segundos.

• Si se acaba de detectar un interbloqueo, se considera que los siguientes subprocesos que deben esperar un bloqueo entran en el ciclo de interbloqueo. La primera pareja de esperas de bloqueo después de que se haya detectado un interbloqueo desencadenará inmediatamente una búsqueda de interbloqueos, en vez de esperar al siguiente intervalo de detección de interbloqueos. Por ejemplo, si el intervalo actual es de 5 segundos y se acaba de detectar un interbloqueo, la siguiente espera de bloqueo activará inmediatamente el detector de interbloqueos. Si esta espera de bloqueo forma parte de un interbloqueo, se detectará en seguida en lugar de durante la siguiente búsqueda de interbloqueos.

El Motor de base de datos sólo suele realizar detecciones de interbloqueos periódicas. Dado que el número de interbloqueos que se encuentran en el sistema suele ser pequeño, si se detectan periódicamente, el sistema no se ve sobrecargado por este tipo de detecciones.

Cuando el monitor de bloqueos inicia una búsqueda de interbloqueos para un subproceso determinado, identifica el recurso que está esperando. Después, el monitor de bloqueos encuentra al propietario o a los propietarios de ese recurso y continúa recursivamente la búsqueda de interbloqueos para esos subprocesos hasta que encuentra un ciclo. Un ciclo que se identifica de esta manera forma un interbloqueo.

Una vez detectado un interbloqueo, el Motor de base de datos finaliza un interbloqueo eligiendo uno de los subprocesos como sujeto del interbloqueo. El Motor de base de datos finaliza el lote actual que se está ejecutando para el subproceso, revierte la transacción del sujeto del interbloqueo y devuelve un error 1205 a la aplicación. Revertir la transacción para el sujeto del interbloqueo libera todos los bloqueos que tiene la transacción. Esto permite que las transacciones de otros subprocesos se desbloqueen y continúen. El error 1205 del sujeto del interbloqueo registra información sobre los subprocesos y recursos implicados en un interbloqueo en el registro de errores.

De forma predeterminada, el Motor de base de datos elige como sujeto del interbloqueo la sesión que ejecuta la transacción cuya reversión resulta menos costosa. Como alternativa, un usuario puede especificar la prioridad de las sesiones en una situación de interbloqueo mediante la instrucción SET DEADLOCK_PRIORITY. DEADLOCK_PRIORITY puede establecerse como LOW, NORMAL o HIGH; también puede establecerse como un valor entero en el intervalo de -10 a 10. El valor predeterminado de la prioridad de interbloqueo es NORMAL. Si dos sesiones tienen distintas prioridades de interbloqueo, la sesión con la prioridad menor se elige como el sujeto del interbloqueo. Si ambas sesiones tienen la misma prioridad de interbloqueo, se elige la sesión con la transacción cuya reversión resulta menos costosa. Si las sesiones implicadas en el ciclo de interbloqueo tienen la misma prioridad de interbloqueo y el mismo costo, se elige un sujeto de forma aleatoria.

Cuando se trabaja con CLR, el monitor de interbloqueos detecta automáticamente el interbloqueo de los recursos de sincronización (monitores, bloqueo de lectura y escritura, y combinación de subprocesos) a los que se ha tenido acceso dentro de los procedimientos administrados. Si embargo, el interbloqueo se resuelve iniciando una excepción en el procedimiento que se seleccionó como sujeto del interbloqueo. Es importante comprender que la excepción no libera automáticamente los recursos que posee actualmente el sujeto; los recursos se tienen que liberar de forma explícita. De forma coherente con el comportamiento de la excepción, la excepción utilizada para identificar un sujeto del interbloqueo se puede interceptar y descartar.

Herramientas de información de interbloqueos

Para ver la información de interbloqueos, Motor de base de datos proporciona herramientas de supervisión en la forma de dos marcas de traza, y el evento deadlock graph del SQL Server Profiler.

Marcas de traza 1204 y 1222

Cuando se produce el interbloqueo, las marcas de traza 1204 y 1222 devuelven la información que se ha capturado en el registro de errores de SQL Server 2005. La marca de traza 1204 informa sobre el interbloqueo con un formato que especifica cada nodo implicado en el mismo. La marca de traza 1222 aplica formato a la información de interbloqueo, primero por procesos y luego por recursos. Es posible habilitar ambas marcas de traza para obtener dos representaciones del mismo evento de interbloqueo.

Además de definir las propiedades de las marcas de traza 1204 y 1222, en la siguiente tabla se muestran las similitudes y las diferencias.

Propiedad	Marcas de traza 1204 y 1222	Sólo marca de traza 1204	Sólo marca de traza 1222
Formato de salida	Los resultados se capturan en el registro de errores de SQL Server 2005.	Se centra en los nodos implicados en el interbloqueo. Cada nodo tiene una sección dedicada y la última sección describe al sujeto del interbloqueo.	Devuelve información en un formato XML que no se ajusta a un esquema de definición de esquemas XML (XSD). El formato tiene tres secciones principales. La primera sección declara el sujeto del interbloqueo. La segunda sección describe los procesos implicados en el interbloqueo. La tercera sección describe los recursos que son sinónimos de nodos en la marca de traza 1204.
Atributos de identificación	SPID:<x> ECID:<x>. Identifica el subproceso del identificador de proceso del sistema en los casos de procesos paralelos. La entrada SPID:<x> ECID:0, donde <x> se sustituye por el valor del SPID, representa el subproceso principal. La entrada SPID:<x> ECID:<y>, donde <x> se sustituye por el valor del SPID e <y> es mayor que 0, representa los subprocesos secundarios del mismo SPID. BatchID (sbid para la marca de traza 1222). Identifica el lote desde el que la ejecución del código está solicitando o manteniendo un bloqueo. Cuando se deshabilita Multiple Active Result Sets (MARTE), el valor de BatchID es 0. Cuando se habilita MART, el valor para los lotes activos es 1 para n. Si en la sesión no hay lotes activos, BatchID es 0. Mode. Especifica el tipo de bloqueo de un recurso en concreto que un subproceso solicita, concede o espera. Mode puede ser IS (Intención compartida), S (Compartido), U (Actualizar), IX (Intención exclusiva), SIX (Intención compartida exclusiva) y X (Exclusiva). Para obtener más información, vea Modos de bloqueo. Line # (line para la marca de traza 1222). Indica el número de línea en el lote actual de instrucciones que se estaba ejecutando cuando se produjo el interbloqueo. Input Buf (inputbuf para la marca de traza 1222). Indica todas las instrucciones del lote actual.	Node. Representa el numero de entrada en la cadena de interbloqueo. Lists. El propietario del bloqueo puede formar parte de estas listas: Grant List. Enumera los propietarios actuales del recurso. Convert List. Enumera los propietarios actuales que están intentando convertir sus bloqueos a un nivel más alto. Wait List. Enumera las solicitudes actuales del nuevo bloqueo para el recurso. Statement Type. Describe el tipo de instrucción DML (SELECT, INSERT, UPDATE o DELETE) en que los subprocesos tienen permisos. Victim Resource Owner. Especifica el subproceso participante que SQL Server elige como sujeto para interrumpir el ciclo de interbloqueo. El subproceso elegido y todos los subprocesos secundarios finalizan. Next Branch. Representa los dos o más subprocesos secundarios del mismo SPID que están implicados en el ciclo de interbloqueo.	deadlock victim. Representa la dirección de la memoria física de la tarea (vea sys.dm_os_tasks (Transact-SQL)) que se seleccionó como sujeto del interbloqueo. Puede ser 0 (cero) en caso de un interbloqueo no resuelto. Una tarea que se está revirtiendo no se puede seleccionar como sujeto del interbloqueo. executionstack. Representa el código Transact-SQL que se está ejecutando en el momento en que se produce el interbloqueo. priority. Representa la prioridad de interbloqueo. En ciertos casos, el Motor de base de datos puede optar por modificar la prioridad de interbloqueo durante una breve duración para conseguir una mejor simultaneidad. logused. Espacio de registro utilizado por la tarea. owner id. El Id. de la transacción que tiene el control de la solicitud. status. Estado de la tarea. Es uno de los siguientes valores: pending. Esperando un subproceso de trabajo. runnable. Preparado para ejecutarse pero esperando un cuanto. running. Ejecutándose actualmente en el programador. suspended. La ejecución se ha suspendido. done. La tarea se ha completado. spinloop. Esperando que un bloqueo por subproceso esté disponible. waitresource. El recurso que la tarea necesita. waittime. Tiempo en milisegundos de espera del recurso. schedulerid. Programador asociado a esta tarea. Vea sys.dm_os_schedulers (Transact-SQL). hostname. El nombre de la estación de trabajo. isolationlevel. El nivel de aislamiento de transacción actual. Xactid. El Id. de la transacción que tiene el control de la solicitud. currentdb. El Id. de la base de datos. lastbatchstarted. La última vez que un proceso de cliente inició la ejecución de lotes. lastbatchcompleted. La última vez que un proceso de cliente completó la ejecución de lotes. clientoption1 y clientoption2. Opciones establecidas en esta conexión de cliente. Se trata de una máscara de bits que incluye información acerca de las opciones controladas normalmente por instrucciones SET, como SET NOCOUNT y SET XACTABORT. associatedObjectId. Representa el Id. del árbol b o montón (HoBT).
Atributos del recurso	RID. Identifica la única fila de una tabla en la que se mantiene o se solicita un bloqueo. RID se representa como RID: db_id:file_id:page_no:row_no. Por ejemplo, RID: 6:1:20789:0. OBJECT. Identifica la tabla en la que se mantiene o se solicita un bloqueo. OBJECT se representa como OBJECT: db_id:object_id. Por ejemplo, TAB: 6:2009058193. KEY. Identifica el intervalo de clave de un índice en el que se mantiene o se solicita un bloqueo. KEY se representa como KEY: db_id:hobt_id (index key hash value). Por ejemplo, KEY: 6:72057594057457664 (350007a4d329). PAG. Identifica el recurso de página en el que se mantiene o se solicita un bloqueo. PAG se representa como PAG: db_id:file_id:page_no. Por ejemplo, PAG: 6:1:20789. EXT. Identifica la estructura de extensión. EXT se representa como EXT: db_id:file_id:extent_no. Por ejemplo, EXT: 6:1:9. DB. Identifica el bloqueo de la base de datos. DB se representa de una de las siguientes maneras: DB: db_id DB:db_id[BULK-OP-DB], que identifica el bloqueo de base de datos realizado por la copia de seguridad de la base de datos. DB: db_id[BULK-OP-LOG], que identifica el bloqueo realizado por el registro de copia de seguridad de esa base de datos en concreto. APP. Identifica el bloqueo realizado por un recurso de la aplicación. Se representa como APP: lock_resource. Por ejemplo, APP: Formf370f478. METADATA. Representa los recursos de metadatos implicados en un interbloqueo. Debido a que METADATA tiene muchos recursos secundarios, el valor devuelto depende del recurso secundario que se haya interbloqueado. Por ejemplo, METADATA.USER_TYPE devuelve user_type_id = <integer_value>. Para obtener más información acerca de los recursos y recursos secundarios de METADATA, vea sys.dm_tran_locks (Transact-SQL). HOBT. Representa al montón o árbol b implicados en un interbloqueo.	Nada exclusivo de esta marca de traza.	Nada exclusivo de esta marca de traza.

Ejemplo de marca de traza 1204

En el siguiente ejemplo se muestra el resultado que se obtiene cuando se activa una marca de traza 1204. En este caso, la tabla de Node 1 es un montón sin índices, y la tabla de Node 2 es un montón con un índice no clúster. La clave de índice de Node 2 se está actualizando cuando se produce el interbloqueo.

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0 
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Ejemplo de marca de traza 1222

En el siguiente ejemplo se muestra el resultado que se obtiene cuando se activa una marca de traza 1222. En este caso, una tabla es un montón sin índices y la otra tabla es un montón con un índice no clúster. En la segunda tabla, la clave de índice se está actualizando cuando se produce el interbloqueo.

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868 
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444 
   transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0 
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54 
   sbid=0 ecid=0 priority=0 transcount=2 
   lastbatchstarted=2005-09-05T11:22:42.733 
   lastbatchcompleted=2005-09-05T11:22:42.733 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310444 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2008R2.dbo.usp_p1 line=6 stmtstart=202 
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1     
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380 
   waitresource=KEY: 6:72057594057457664 (350007a4d329)   
   waittime=5015 ownerId=310462 transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U 
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0 
   priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077 
   lastbatchcompleted=2005-09-05T11:22:44.077 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310462 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2008R2.dbo.usp_p2 line=6 stmtstart=200 
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2     
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2    
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2008R2.dbo.T2 
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2008R2.dbo.T1 
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X 
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Evento Deadlock Graph del Analizador

Éste es un evento del SQL Server Profiler que presenta una descripción gráfica de las tareas y recursos implicados en un interbloqueo. En el siguiente ejemplo se muestra el resultado del SQL Server Profiler cuando se ha activado el evento deadlock graph.

Para obtener más información acerca de cómo ejecutar el evento deadlock graph del SQL Server Profiler, vea Analizar interbloqueos con el Analizador de SQL Server.

Vea también

Referencia

SET DEADLOCK_PRIORITY (Transact-SQL)

Marcas de seguimiento (Transact-SQL)

Conceptos

Interbloqueos

Minimizar los interbloqueos

Ver el registro de errores de SQL Server

Supervisar los registros de errores