Detectar y finalizar interbloqueos

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

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)

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

Vea también

Referencia

Conceptos