Cómo manejar interbloqueos en ApsaraDB RDS for SQL Server - ApsaraDB RDS

Descripción del problema

Cuando una aplicación lee o escribe con frecuencia en la misma tabla o recurso, se pueden producir interbloqueos. Al ocurrir uno, SQL Server finaliza una de las transacciones y devuelve un mensaje de error al cliente, como a continuación:

Error Message: Msg 1205, Level 13, State 47, Line 1Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Solución

Conéctese a la instancia desde un cliente. Para obtener más información, consulte Connect to an ApsaraDB RDS for SQL Server instance.

Supervise las vistas correspondientes.

Ejecute la siguiente instrucción SQL para supervisar periódicamente SYS.SYSPROCESSES.
```
WHILE 1 = 1
BEGIN
SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
WAITFOR DELAY '[$Time]';
END;
```
Nota
Este ejemplo utiliza [$Time]. Personalice el intervalo de sondeo según sus necesidades, por ejemplo, 00:00:01.

En los resultados de varias iteraciones, SPID 53 espera un bloqueo de tipo LCK_M_X y SPID 56 espera un bloqueo de tipo LCK_M_S. El valor blocked de SPID 53 es 56, y el valor blocked de SPID 56 es 53, lo que indica que se bloquean mutuamente. El valor waittime de ambas sesiones aumenta con cada ciclo.

Nota
En los resultados del monitoreo, la columna blocked muestra el identificador de la sesión que causa el bloqueo, mientras que la columna waitresource indica el recurso que espera la sesión bloqueada. Estos resultados confirman que SPID 53 y SPID 56 se bloquean entre sí y forman un interbloqueo.

Ejecute la siguiente instrucción SQL para supervisar periódicamente vistas como sys.dm_tran_locks y sys.dm_os_waiting_tasks.

while 1=1
    Begin
    SELECT
    db.name DBName,
    tl.request_session_id,
    wt.blocking_session_id,
    OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
    tl.resource_type,
    h1.TEXT AS RequestingText,
    h2.TEXT AS BlockingText,
    tl.request_mode
    FROM sys.dm_tran_locks AS tl
    INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
    INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
    INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
    INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
    INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
    CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
    CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
    waitfor delay '[$Time]'
    End

Dos sesiones se bloquean mutuamente y generan un interbloqueo: request_session_id 56 está bloqueada por blocking_session_id 62 en el objeto Tbl2, con un request_mode X. Al mismo tiempo, la sesión 62 está bloqueada por la sesión 56 en el objeto Tbl1, también con un request_mode X. El valor RequestingText de ambas sesiones muestra instrucciones INSERT, lo que confirma una relación de bloqueo cíclica que provoca el interbloqueo.

A continuación se describen las columnas devueltas.

Parámetro	Descripción
`DBName`	La base de datos a la que accede la sesión solicitante.
`request_session_id`	El identificador de la sesión bloqueada.
`blocking_session_id`	El identificador de la sesión que causa el bloqueo.
`BlockedObjectName`	El objeto al que accede la sesión bloqueada.
`resource_type`	El tipo de recurso que se espera.
`RequestingText`	La instrucción que ejecuta la sesión bloqueada.
`BlockingText`	La instrucción que ejecuta la sesión que causa el bloqueo.
`request_mode`	El modo de bloqueo que solicita la sesión bloqueada.

Si su instancia ejecuta ApsaraDB RDS for SQL Server 2012, también puede usar SQL Server Profiler para supervisar y capturar un gráfico de interbloqueo.

En el cuadro de diálogo Trace Properties, dentro de la pestaña Events Selection, expanda la categoría Locks y seleccione los eventos Deadlock graph y Lock:Deadlock.

Gráfico de interbloqueo capturado:

Los resultados del seguimiento de SQL Server Profiler muestran dos eventos Lock:Deadlock Chain: SPID 52 retiene un bloqueo 5-X (exclusivo) en la base de datos test_shrink (transacción 1374753), mientras que SPID 55 retiene un bloqueo 3-S (compartido) en la transacción 1374762. Esto desencadena posteriormente un evento Lock:Deadlock. El gráfico de interbloqueo indica que SPID 52 y SPID 55 bloquean recursos de clave en los índices i de test_shrink.dbo.Th12 y test_shrink.dbo.Th11, respectivamente. Ambos procesos solicitan los bloqueos que retiene el otro (el modo de solicitud es X/S), lo que crea una dependencia circular y provoca el interbloqueo.

Recomendaciones

Siga estas recomendaciones para resolver y prevenir interbloqueos.

Finalice la sesión que causa el bloqueo para resolver rápidamente el interbloqueo.
Compruebe si existen transacciones de larga duración y confírmelas de inmediato para liberar los bloqueos.
Si el interbloqueo lo causa un bloqueo compartido y su aplicación tolera lecturas sucias, utilice la sugerencia de consulta WITH (NOLOCK). Por ejemplo, agregue SELECT * FROM table WITH (NOLOCK); a su consulta. De esta forma, la consulta se ejecuta sin solicitar un bloqueo, lo que ayuda a prevenir interbloqueos.
Revise la lógica de la aplicación para asegurarse de que los recursos se accedan siempre en el mismo orden.

Operaciones relacionadas

Consulte los detalles de los interbloqueos de su instancia de ApsaraDB RDS for SQL Server en la consola de ApsaraDB RDS. Para obtener más información, consulte View deadlock statistics.