Análisis y prevención de interbloqueos en Azure SQL Database

Se aplica a: Azure SQL Database

En este artículo, se explica cómo identificar los interbloqueos en Azure SQL Database, usar grafos de interbloqueos y el Almacén de consultas para identificar las consultas del interbloqueo, y planear y probar los cambios para evitar que los interbloqueos vuelvan a ocurrir.

Este artículo se centra en la identificación y el análisis de los interbloqueos debidos a la contención de bloqueos. Más información sobre otros tipos de interbloqueos en Recursos que pueden causar interbloqueos.

Cómo se producen los interbloqueos en Azure SQL Database

Cada nueva base de datos de Azure SQL Database tiene la opción de base de datos Instantánea de lectura confirmada (RCSI) habilitada de forma predeterminada. El bloqueo entre las sesiones en las que se leen datos y aquellas en las que se escriben se reduce al mínimo en RCSI, ya que usa versiones de fila para aumentar la simultaneidad. Sin embargo, bloqueos e interbloqueos pueden seguir apareciendo en las bases de datos de Azure SQL Database ya que:

• Las consultas que modifican los datos pueden bloquearse entre sí.
• Las consultas se pueden ejecutar en niveles de aislamiento que aumentan el bloqueo. Los niveles de aislamiento se pueden especificar mediante métodos de la biblioteca cliente, sugerencias de consulta o instrucciones SET en Transact-SQL.
• RCSI se puede deshabilitar, lo que hace que la base de datos use bloqueos compartidos (S) para proteger las instrucciones SELECT que se ejecutan en el nivel de aislamiento de lectura confirmada. Esto puede aumentar los bloqueos e interbloqueos.

Interbloqueo de ejemplo

Un interbloqueo se produce cuando dos o más tareas se bloquean entre sí permanentemente porque cada tarea tiene un bloqueo en un recurso que la otra tarea intenta bloquear. Un interbloqueo también se llama dependencia cíclica: en el caso de un interbloqueo de dos tareas, la transacción A tiene una dependencia en la transacción B y la transacción B cierra el círculo al tener una dependencia en la transacción A.

Por ejemplo:

1. La sesión A inicia una transacción explícita y ejecuta una instrucción de actualización que adquiere un bloqueo de actualización (U) en una fila de la tabla SalesLT.Product que se convierte en un bloqueo exclusivo (X).
2. La sesión B ejecuta una instrucción de actualización que modifica la tabla SalesLT.ProductDescription. La instrucción de actualización se combina con la tabla SalesLT.Product para buscar las filas correctas que se van a actualizar.
   • La sesión B adquiere un bloqueo de actualización (U) en 72 filas de la tabla SalesLT.ProductDescription.
   • La sesión B necesita un bloqueo compartido en las filas de la tabla SalesLT.Product, incluida la fila bloqueada por la sesión A. La sesión B está bloqueada en SalesLT.Product.
3. La sesión A continúa su transacción y ahora ejecuta una actualización en la tabla SalesLT.ProductDescription. La sesión A está bloqueada por la sesión B en SalesLT.ProductDescription.

Todas las transacciones de un interbloqueo esperarán indefinidamente, a menos que se revierta una de las transacciones participantes, por ejemplo, porque su sesión se terminó.

La supervisión de interbloqueos del motor de base de datos comprueba periódicamente si hay tareas con un interbloqueo. Si el monitor de interbloqueo detecta una dependencia cíclica, elige una de las tareas como víctima y finaliza su transacción con el error 1205, "La transacción (id. de proceso N) tenía un interbloqueo en los recursos de bloqueo con otro proceso y se ha elegido como víctima del interbloqueo. Vuelva a ejecutar la transacción". Interrumpir el interbloqueo de esta manera permite que la otra tarea o tareas del interbloqueo completen sus transacciones.

La aplicación con la transacción elegida como víctima del interbloqueo debe reintentar la transacción, que normalmente se completa una vez finalizada la otra transacción o transacciones implicadas en el interbloqueo.

Se recomienda introducir un retraso corto y aleatorio antes de volver a intentarlo para evitar volver a encontrar el mismo interbloqueo. Obtenga información sobre cómo diseñar la lógica de reintento para errores transitorios.

Nivel de aislamiento predeterminado en Azure SQL Database

Las nuevas bases de datos de Azure SQL Database habilitan la instantánea de lectura confirmada (RCSI) de manera predeterminada. RCSI cambia el comportamiento del nivel de aislamiento de la lectura confirmada para usar el control de versiones de las filas para proporcionar coherencia de nivel de instrucción sin el uso de bloqueos compartidos (S) para instrucciones SELECT.

Con RCSI habilitado:

• Las instrucciones que leen datos no bloquean las instrucciones que modifican datos.
• Las instrucciones que modifican datos no bloquean las instrucciones que leen datos.

El nivel de aislamiento de instantánea también se habilita de forma predeterminada para las nuevas bases de datos de Azure SQL Database. El aislamiento de instantáneas es un nivel de aislamiento basado en filas adicional que proporciona coherencia en el nivel de transacción para los datos y que usa versiones de fila para seleccionar las filas que se van a actualizar. Para usar el aislamiento de instantáneas, las consultas o las conexiones deben establecer explícitamente su nivel de aislamiento de transacción en SNAPSHOT. Esto solo se puede hacer cuando el aislamiento de instantáneas está habilitado para la base de datos.

Puede identificar si RCSI y/o el aislamiento de instantáneas está habilitado con Transact-SQL. Conéctese a la base de datos de Azure SQL Database y ejecute la consulta siguiente:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Si RCSI está habilitado, la columna is_read_committed_snapshot_on devolverá el valor 1. Si el aislamiento de instantáneas está habilitado, la columna snapshot_isolation_state_desc devolverá el valor ON.

Si se ha deshabilitado RCSI para una base de datos de Azure SQL Database, investigue por qué se deshabilitó RCSI antes de volver a habilitarlo. Es posible que el código de la aplicación se haya escrito esperando que las consultas que leen datos se bloqueen mediante consultas que escriben datos, lo que da lugar a resultados incorrectos de las condiciones de carrera cuando RCSI está habilitado.

Interpretación de eventos de interbloqueo

Se emite un evento de interbloqueo después de que el administrador de interbloqueos de Azure SQL Database detecte un interbloqueo y seleccione una transacción como víctima. En otras palabras, si configura alertas para los interbloqueos, la notificación se activa después de que se haya resuelto un interbloqueo individual. No hay ninguna acción de usuario que se deba realizar para ese interbloqueo. Las aplicaciones se deben escribir para incluir una lógica de reintento para que continúen automáticamente después de recibir el error 1205, "La transacción (id. de proceso N) tenía un interbloqueo en los recursos de bloqueo con otro proceso y se ha elegido como víctima del interbloqueo. Ejecute de nuevo la transacción."

Sin embargo, resulta útil configurar alertas, ya que los interbloqueos pueden volver a ocurrir. Las alertas de interbloqueos le permiten investigar si se está produciendo un patrón de interbloqueos repetidos en la base de datos, en cuyo caso puede optar por tomar medidas para evitar que se repitan los interbloqueos. Más información sobre las alertas en la sección Supervisión y alertas sobre interbloqueos de este artículo.

Métodos principales para evitar interbloqueos

El enfoque de riesgo más bajo para evitar que los interbloqueos se vuelvan a producir generalmente es ajustar los índices no agrupados para optimizar las consultas implicadas en el interbloqueo.

• El riesgo es bajo para este enfoque porque el ajuste de los índices no agrupados no requiere cambios en el propio código de la consulta, lo que reduce el riesgo de un error de usuario al reescribir la instrucción de Transact-SQL que hace que se devuelvan datos incorrectos al usuario.
• Un ajuste eficaz de los índices no agrupados ayuda a las consultas a encontrar los datos que se van a leer y modificar de forma más eficaz. Al reducir la cantidad de datos a los que tiene que acceder una consulta, la probabilidad de bloqueo se reduce y, a menudo, se pueden evitar interbloqueos.

En algunos casos, la creación o ajuste de un índice agrupado puede reducir los bloqueos y los interbloqueos. Dado que el índice agrupado se incluye en todas las definiciones de índices no agrupados, la creación o modificación de un índice agrupado puede ser una operación intensiva en E/S y una operación que consume mucho tiempo en tablas más grandes con índices no agrupados existentes. Más información en Directrices de diseño de índices agrupados.

Cuando el ajuste de índices no tiene éxito para evitar los interbloqueos, hay otros métodos disponibles:

• Si el interbloqueo solo se produce cuando se elige un plan determinado para una de las consultas implicadas en el interbloqueo, forzar un plan de consulta con el Almacén de consultas puede evitar que se vuelvan a producir interbloqueos.
• La reescritura de las instrucciones de Transact-SQL de una o varias de las transacciones implicadas en el interbloqueo también puede ayudar a evitar los interbloqueos. La división de transacciones explícitas en transacciones más pequeñas requiere codificación y pruebas cuidadosas para garantizar la validez de los datos cuando se producen modificaciones simultáneas.

Más información sobre cada uno de estos enfoques en la sección Evitar que se vuelva a producir un interbloqueo de este artículo.

Supervisión y alertas sobre interbloqueos

En este artículo, usaremos la base de datos de ejemplo AdventureWorksLT para configurar alertas para los interbloqueos, provocar un interbloqueo de ejemplo, analizar el grafo de interbloqueo del interbloqueo de ejemplo y probar los cambios para evitar que se vuelva a producir el interbloqueo.

En este artículo, usaremos el cliente de SQL Server Management Studio (SSMS), ya que contiene funcionalidad para mostrar grafos de interbloqueos de un modo visual interactivo. Puede usar otros clientes, como Azure Data Studio, para seguir los ejemplos, pero es posible que solo pueda ver los grafos de interbloqueos como XML.

Creación de la base de datos AdventureWorksLT

Para seguir los ejemplos, cree una nueva base de datos en Azure SQL Database y seleccione los datos de ejemplo como el origen de datos.

Para obtener instrucciones detalladas sobre cómo crear AdventureWorksLT con Azure Portal, la CLI de Azure o PowerShell, seleccione el enfoque que prefiera en Inicio rápido: Creación de una base de datos única de Azure SQL Database.

Configuración de alertas de interbloqueos en Azure Portal

Para configurar alertas para eventos de interbloqueo, siga los pasos del artículo Creación de alertas para Azure SQL Database y Azure Synapse Analytics mediante Azure Portal.

Seleccione Interbloqueos como nombre de señal de la alerta. Configure el grupo de acciones para que le notifique mediante el método que prefiera, como el tipo de acción Correo electrónico/SMS/Inserción/Voz.

Recopilación de grafos de interbloqueos en Azure SQL Database con eventos extendidos

Los grafos de interbloqueos son una fuente enriquecida de información sobre los procesos y bloqueos implicados en un interbloqueo. Para recopilar grafos de interbloqueos con eventos extendidos (XEvents) en Azure SQL Database, capture el evento sqlserver.database_xml_deadlock_report.

Puede recopilar grafos de interbloqueos con XEvents mediante el destino de búfer de anillo o un destino de archivo de eventos. Las consideraciones para seleccionar el tipo de destino adecuado se resumen en la tabla siguiente:

Seleccione el tipo de destino que quiere usar:

CREATE EVENT SESSION [deadlocks] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer 
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = START;
GO

IF 0 = (SELECT COUNT(*)
    FROM sys.symmetric_keys
    WHERE symmetric_key_id = 101 and name=N'##MS_DatabaseMasterKey##')
BEGIN
    PRINT N'Creating master key';
    CREATE MASTER KEY;
END
ELSE 
BEGIN
    PRINT N'Master key already exists, no action taken';
END
GO

CREATE DATABASE SCOPED CREDENTIAL
    [https://yourstorageaccountname.blob.core.windows.net/yourcontainername]
    WITH
        IDENTITY = 'SHARED ACCESS SIGNATURE', 
        SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=pUNbbsmDiMzXr1vuNGZh84zyOMBFaBjgWv53IhOzYWQ%3D'
    ;
GO

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
    (SET filename =
    'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/deadlocks.xel'
    )
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = START;
GO

Provocar un interbloqueo en AdventureWorksLT

Para provocar un interbloqueo, deberá conectar dos sesiones a la base de datos AdventureWorksLT. Nos referiremos a estas sesiones como Sesión A y Sesión B.

En la sesión A, ejecute la siguiente instrucción de Transact-SQL. Este código inicia una transacción explícita y ejecuta una sola instrucción que actualiza la tabla SalesLT.Product. Para ello, la transacción adquiere un bloqueo de actualización (U) en una fila de la tabla SalesLT.Product que se convierte en un bloqueo exclusivo (X). Dejaremos abierta la transacción.

BEGIN TRAN

    UPDATE SalesLT.Product SET SellEndDate = SellEndDate + 1
        WHERE Color = 'Red';

Ahora, en la sesión B, ejecute la siguiente instrucción de Transact-SQL. Este código no inicia explícitamente una transacción. En su lugar, funciona en modo de transacción de confirmación automática. Esta instrucción actualiza la tabla SalesLT.ProductDescription. La actualización tomará un bloqueo de actualización (U) en 72 filas de la tabla SalesLT.ProductDescription. La consulta se combina con otras tablas, incluida la tabla SalesLT.Product.

UPDATE SalesLT.ProductDescription SET Description = Description
    FROM SalesLT.ProductDescription as pd
    JOIN SalesLT.ProductModelProductDescription as pmpd on
        pd.ProductDescriptionID = pmpd.ProductDescriptionID
    JOIN SalesLT.ProductModel as pm on
        pmpd.ProductModelID = pm.ProductModelID
    JOIN SalesLT.Product as p on
        pm.ProductModelID=p.ProductModelID
    WHERE p.Color = 'Silver';

Para completar esta actualización, la sesión B necesita un bloqueo compartido (S) en las filas de la tabla SalesLT.Product, incluida la fila bloqueada por la sesión A. La sesión B estará bloqueada en SalesLT.Product.

Vuelva a la sesión A. Ejecute la siguiente instrucción de Transact-SQL. Esto ejecuta una segunda instrucción UPDATE como parte de la transacción abierta.

    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red';

La segunda instrucción de actualización de la sesión A estará bloqueada por la sesión B en la tabla SalesLT.ProductDescription.

La sesión A y la sesión B ahora se bloquean mutuamente entre sí. Ninguna transacción puede continuar, ya que cada una necesita un recurso bloqueado por la otra.

Después de unos segundos, el monitor de interbloqueo identificará que las transacciones de la sesión A y la sesión B se bloquean mutuamente entre sí y que ninguna puede avanzar. Debería ver que se produce un interbloqueo, con la sesión A elegida como víctima del interbloqueo. Aparecerá un mensaje de error en la sesión A con un texto similar al siguiente:

Msg 1205, Nivel 13, Estado 51, Línea 7 La transacción (id. de proceso 91) tenía un interbloqueo en los recursos de bloqueo con otro proceso y se ha elegido como víctima del interbloqueo. Vuelva a ejecutar la transacción.

La sesión B se completará correctamente.

Si configura alertas de interbloqueos en Azure Portal, debe recibir una notificación poco después de que se produzca el interbloqueo.

Visualización de grafos de interbloqueos desde una sesión de XEvents

Si ha configurado una sesión de XEvents para recopilar interbloqueos y se ha producido un interbloqueo después de iniciar la sesión, puede ver una presentación gráfica interactiva del grafo de interbloqueo, así como el código XML del grafo de interbloqueo.

Hay diferentes métodos disponibles para obtener información de interbloqueos para el destino de búfer de anillo y los destinos de archivo de eventos. Seleccione el destino que usó para la sesión de XEvents:

DECLARE @tracename sysname = N'deadlocks';

WITH ring_buffer AS (
    SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    JOIN sys.dm_xe_database_session_targets AS t 
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename and
    t.target_name = N'ring_buffer'
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM ring_buffer
    CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = STOP;
GO
ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = START;
GO

DECLARE @tracename sysname = N'deadlocks_eventfile',
    @filename nvarchar(2000);

WITH eft as (SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    JOIN sys.dm_xe_database_session_targets AS t 
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename and
    t.target_name = N'event_file'
)
SELECT @filename = ft.evtdata.value('(@name)[1]','nvarchar(2000)') 
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') as ft(evtdata);

WITH xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

declare @filename nvarchar(2000) = N'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/yourfilename.xel';

with xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Visualización y guardado de un grafo de interbloqueo en XML

La visualización de un grafo de interbloqueo en formato XML le permite copiar el elemento inputbuffer de las instrucciones de Transact-SQL implicadas en el interbloqueo. También puede que prefiera analizar los interbloqueos en un formato basado en texto.

Si ha usado una consulta de Transact-SQL para devolver la información del grafo de interbloqueo, para ver el código XML del grafo de interbloqueo, seleccione el valor de la columna deadlock_xml de cualquier fila para abrir el código XML del grafo de interbloqueo en una nueva ventana de SSMS.

El código XML de este grafo de interbloqueo de ejemplo es:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Silver';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Para guardar el grafo de interbloqueo como un archivo XML:

1. Seleccione Archivo y Guardar como....
2. Deje el valor Guardar como tipo en el valor predeterminado Archivos XML (*.xml).
3. Establezca Nombre de archivo en el nombre de su elección.
4. Seleccione Guardar.

Guardado de un grafo de interbloqueo como un archivo XDL que se puede mostrar de forma interactiva en SSMS

La visualización de una representación interactiva de un grafo de interbloqueo puede ser útil para obtener una visión general rápida de los procesos y recursos implicados en un interbloqueo e identificar rápidamente a la víctima del interbloqueo.

Para guardar un grafo de interbloqueo como un archivo que se puede mostrar gráficamente mediante SSMS:

1. Seleccione el valor de la columna deadlock_xml de cualquier fila para abrir el código XML del grafo de interbloqueo en una nueva ventana de SSMS.

2. Seleccione Archivo y Guardar como....

3. Establezca Guardar como tipo en Todos los archivos.

4. Establezca Nombre de archivo en el nombre que prefiera, con la extensión establecida en .xdl.

5. Seleccione Guardar.

   

6. Para cerrar el archivo, seleccione la X en la pestaña de la parte superior de la ventana o seleccione Archivo y, a continuación, Cerrar.

7. Para volver a abrir el archivo en SSMS, seleccione Archivo, después Abrir y, a continuación, Archivo. Seleccione el archivo que guardó con la extensión .xdl.

   El grafo de interbloqueo se mostrará ahora en SSMS con una representación visual de los procesos y recursos implicados en el interbloqueo.

   

Análisis de un interbloqueo de Azure SQL Database

Normalmente, un grafo de interbloqueo tiene tres nodos:

• Lista de víctimas. Identificador de proceso del elemento afectado por el interbloqueo.
• Lista de procesos. Información sobre todos los procesos implicados en el interbloqueo. Los grafos de interbloqueo usan el término "proceso" para representar una sesión que ejecuta una transacción.
• Lista de recursos. Información sobre todos los recursos implicados en el interbloqueo.

Al analizar un interbloqueo, resulta útil recorrer estos nodos.

Lista de víctimas del interbloqueo

La lista de víctimas del interbloqueo muestra el proceso elegido como víctima del interbloqueo. En la representación visual de un grafo de interbloqueo, los procesos se representan mediante óvalos. El proceso víctima del interbloqueo tiene una "X" dibujada sobre el óvalo.

En la vista XML de un grafo de interbloqueo, el nodo victim-list proporciona un identificador del proceso que ha sido víctima del interbloqueo.

En nuestro ejemplo de interbloqueo, el identificador del proceso víctima es process24756e75088. Podemos usar este identificador al examinar los nodos de lista de procesos y de lista de recursos para obtener información sobre el proceso víctima y los recursos que estaba bloqueando o para los que solicitaba un bloqueo.

Lista de procesos de interbloqueo

La lista de procesos de interbloqueo es una fuente enriquecida de información sobre las transacciones implicadas en el interbloqueo.

La representación gráfica del grafo de interbloqueo muestra solo un subconjunto de la información contenida en el código XML del grafo de interbloqueo. Los óvalos del grafo de interbloqueo representan el proceso y muestran información que incluye:

• Identificador de proceso del servidor, también conocido como identificador de sesión o SPID.

• Prioridad de interbloqueo de la sesión. Si dos sesiones tienen distintas prioridades de interbloqueo, la sesión con la prioridad menor se elige como el sujeto del interbloqueo. En este ejemplo, ambas sesiones tienen la misma prioridad de interbloqueo.

• Cantidad del registro de transacciones utilizado por la sesión expresado en bytes. Si ambas sesiones tienen la misma prioridad de interbloqueo, el monitor de interbloqueo elige como víctima del interbloqueo la sesión cuya reversión resulte menos costosa. El costo se determina comparando el número de bytes de registro escritos en ese punto de cada transacción.

  En nuestro ejemplo de interbloqueo, la sesión con el identificador 89 había usado una cantidad inferior del registro de transacciones y se seleccionó como víctima del interbloqueo.

Además, puede ver el búfer de entrada de la última instrucción ejecutada en cada sesión antes del interbloqueo al mantener el mouse sobre cada proceso. El búfer de entrada aparecerá en un elemento de información sobre herramientas.

Hay información adicional disponible sobre los procesos en la vista XML del grafo de interbloqueo, entre la que se incluye:

• Información de identificación de la sesión, como el nombre de cliente, el nombre de host y el nombre de inicio de sesión.
• Código hash del plan de consulta de la última instrucción ejecutada por cada sesión antes del interbloqueo. El código hash del plan de consulta es útil para recuperar más información sobre la consulta desde el Almacén de consultas.

En nuestro ejemplo de interbloqueo:

• Podemos ver que ambas sesiones se ejecutaron con el cliente de SSMS con el nombre de inicio de sesión chrisqpublic.
• El código hash del plan de consulta de la última instrucción ejecutada antes del interbloqueo por nuestra víctima del interbloqueo es 0x02b0f58d7730f798. Podemos ver el texto de esta instrucción en el búfer de entrada.
• El código hash del plan de consulta de la última instrucción ejecutada por la otra sesión de nuestro interbloqueo es también 0x02b0f58d7730f798. Podemos ver el texto de esta instrucción en el búfer de entrada. En este caso, ambas consultas tienen el mismo código hash de plan de consulta porque las consultas son idénticas, excepto por un valor literal que se usa como predicado de igualdad.

Usaremos estos valores más adelante en este artículo para encontrar información adicional en el Almacén de consultas.

Limitaciones del búfer de entrada en la lista de procesos del interbloqueo

Hay algunas limitaciones que se deben tener en cuenta con respecto a la información del búfer de entrada en la lista de procesos del interbloqueo.

El texto de la consulta puede estar truncado en el búfer de entrada. El búfer de entrada se limita a los primeros 4000 caracteres de la instrucción que se ejecuta.

Además, es posible que algunas instrucciones implicadas en el interbloqueo no se incluyan en el grafo del interbloqueo. En nuestro ejemplo, la sesión A ejecutó dos instrucciones de actualización dentro de una sola transacción. Solo se incluye en el grafo del interbloqueo la segunda instrucción de actualización, la actualización que provocó el interbloqueo. La primera instrucción de actualización que ejecutó la sesión A jugó un papel en el interbloqueo bloqueando la sesión B. El búfer de entrada, query_hash, y la información relacionada de la primera instrucción ejecutada por la sesión A no se incluyen en el grafo del interbloqueo.

Para identificar la ejecución completa de Transact-SQL en una transacción de varias instrucciones implicada en un interbloqueo, deberá encontrar la información pertinente en el procedimiento almacenado o el código de aplicación que ejecutó la consulta, o ejecutar un seguimiento mediante eventos extendidos para capturar las instrucciones completas ejecutadas por las sesiones implicadas en un interbloqueo mientras se produce. Si una instrucción implicada en el interbloqueo se ha truncado y solo aparece parcialmente la instrucción de Transact-SQL en el búfer de entrada, puede encontrar el código de Transact-SQL de la instrucción en el Almacén de consultas con el plan de ejecución.

Lista de recursos del interbloqueo

La lista de recursos del interbloqueo muestra qué recursos de bloqueo son propiedad de los procesos del interbloqueo y están a la espera.

Los recursos se representan mediante rectángulos en la representación visual del interbloqueo:

En este ejemplo de interbloqueo:

• La víctima del interbloqueo, a la que nos hemos referido como sesión A:

  • Posee un bloqueo exclusivo (X) en una clave del índice PK_Product_ProductID de la tabla SalesLT.Product.
  • Solicita un bloqueo de actualización (U) en una clave del índice PK_ProductDescription_ProductDescriptionID de la tabla SalesLT.ProductDescription.

• El otro proceso, al que nos hemos referido como sesión B:

  • Posee un bloqueo de actualización (U) en una clave del índice PK_ProductDescription_ProductDescriptionID de la tabla SalesLT.ProductDescription.
  • Solicita un bloqueo compartido (S) en una clave del índice PK_ProductDescription_ProductDescriptionID de la tabla SalesLT.ProductDescription.

Podemos ver la misma información en el código XML del grafo de interbloqueo en el nodo de la lista de recursos.

Búsqueda de los planes de ejecución de las consultas en el Almacén de consultas

A menudo, resulta útil examinar los planes de ejecución de las consultas de las instrucciones implicadas en el interbloqueo. Estos planes de ejecución a menudo se pueden encontrar en el Almacén de consultas mediante el código hash del plan de consulta desde la vista XML de la lista de procesos del grafo de interbloqueo.

Esta consulta de Transact-SQL busca planes de consulta que coincidan con el hash del plan de consulta que hemos encontrado para nuestro interbloqueo de ejemplo. Conéctese a la base de datos de usuario en Azure SQL Database para ejecutar la consulta.

DECLARE @query_plan_hash binary(8) = 0x02b0f58d7730f798

SELECT 
    qrsi.end_time as interval_end_time,
    qs.query_id,
    qp.plan_id,
    qt.query_sql_text, 
    TRY_CAST(qp.query_plan as XML) as query_plan,
    qrs.count_executions
FROM sys.query_store_query as qs
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qrsi on qrs.runtime_stats_interval_id=qrsi.runtime_stats_interval_id
WHERE query_plan_hash =  @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

Es posible que no pueda obtener un plan de ejecución de las consultas desde el Almacén de consultas, en función de la configuración CLEANUP_POLICY o QUERY_CAPTURE_MODE del Almacén de consultas. En este caso, a menudo puede obtener la información necesaria mediante la visualización del plan de ejecución estimado de la consulta.

Búsqueda de patrones que aumentan el bloqueo

Al examinar los planes de ejecución de las consultas implicadas en los interbloqueos, busque patrones que puedan contribuir al bloqueo y los interbloqueos.

• Exámenes de tablas o índices. Cuando las consultas que modifican datos se ejecutan en RCSI, la selección de las filas que se van a actualizar se realiza mediante un examen de bloqueo en el que se obtiene un bloqueo de actualización (U) en la fila de datos cuando se leen los valores de los datos. Si la fila de datos no cumple los criterios de actualización, se liberará el bloqueo de actualización y se bloqueará y examinará la siguiente fila.

  La optimización de los índices para ayudar a las consultas de modificación a encontrar filas de forma más eficaz reduce el número de bloqueos de actualización emitidos. Esto reduce las posibilidades de bloqueo y los interbloqueos.

• Vistas indexadas que hacen referencia a más de una tabla. Al modificar una tabla a la que se hace referencia en una vista indexada, el motor de base de datos también debe mantener la vista indexada. Esto requiere tomar más bloqueos y puede provocar un aumento del bloqueo y los interbloqueos. Las vistas indexadas también pueden hacer que las operaciones de actualización se ejecuten internamente en el nivel de aislamiento de lectura confirmada.

• Modificaciones en las columnas a las que se hace referencia en las restricciones de clave externa. Al modificar las columnas de una tabla a las que se hace referencia en una restricción FOREIGN KEY, el motor de base de datos debe buscar las filas relacionadas en la tabla de referencia. Las versiones de fila no se pueden usar para estas lecturas. En los casos en los que se habilitan las actualizaciones o eliminaciones en cascada, el nivel de aislamiento se puede escalar a serializable durante la duración de la instrucción para protegerse contra las inserciones fantasma.

• Sugerencias de bloqueo. Busque las sugerencias de tabla que especifican niveles de aislamiento que requieren más bloqueos. Estas sugerencias incluyen HOLDLOCK (que es equivalente a serializable), SERIALIZABLE, READCOMMITTEDLOCK (que deshabilita RCSI) y REPEATABLEREAD. Además, las sugerencias como PAGLOCK, TABLOCK, UPDLOCK y XLOCK pueden aumentar los riesgos de bloqueo e interbloqueos.

  Si estas sugerencias están en funcionamiento, investigue por qué se implementaron las sugerencias. Estas sugerencias pueden impedir las condiciones de carrera y garantizar la validez de los datos. Es posible dejar estas sugerencias en funcionamiento y evitar futuros interbloqueos mediante el uso de un método alternativo de la sección Evitar que se vuelva a producir un interbloqueo de este artículo si es necesario.

  Nota

  Obtenga información adicional sobre el comportamiento al modificar los datos con el control de versiones de fila en la Guía de versiones de fila y bloqueo de transacciones.

Al examinar el código completo de una transacción, ya sea en un plan de ejecución o en el código de la consulta de la aplicación, busque patrones problemáticos adicionales:

• Interacción del usuario en las transacciones. La interacción del usuario dentro de una transacción explícita de varias instrucciones aumenta significativamente la duración de las transacciones. Esto hace que sea más probable que estas transacciones se superpongan y que se produzcan bloqueos e interbloqueos.

  Del mismo modo, mantener una transacción abierta y consultar una base de datos o una transacción intermedia del sistema no relacionada aumenta significativamente las posibilidades de bloqueos e interbloqueos.

• Transacciones que acceden a objetos con otra ordenación. Es menos probable que se produzcan interbloqueos cuando las transacciones explícitas simultáneas de varias instrucciones siguen los mismos patrones y acceden a los objetos en el mismo orden.

Evitar que se vuelva a producir un interbloqueo

Hay varias técnicas disponibles para evitar que los interbloqueos vuelvan a ocurrir, incluidos el ajuste de los índices, forzar planes con el Almacén de consultas y modificar las consultas de Transact-SQL.

• Revise el índice agrupado de la tabla. La mayoría de las tablas aprovechan las ventajas de los índices agrupados, pero, a menudo, las tablas se implementan como montones por accidente.

  Una manera de comprobar un índice agrupado es mediante el procedimiento almacenado del sistema sp_helpindex. Por ejemplo, podemos ver un resumen de los índices de la tabla SalesLT.Product mediante la ejecución de la instrucción siguiente:

  exec sp_helpindex 'SalesLT.Product';
  GO
  

  Revise la columna index_description. Una tabla solo puede tener un índice agrupado. Si se ha implementado un índice agrupado para la tabla, index_description contendrá la palabra "clustered".

  Si no hay ningún índice agrupado, la tabla es un montón. En este caso, revise si la tabla se creó intencionadamente como un montón para resolver un problema de rendimiento específico. Considere la posibilidad de implementar un índice agrupado basado en las directrices para diseñar índices agrupados.

  En algunos casos, la creación o ajuste de un índice agrupado puede reducir o eliminar el bloqueo en los interbloqueos. En otros casos, es posible que tenga que emplear una técnica adicional, como las demás de esta lista.

• Cree o modifique índices no agrupados. La optimización de los índices no agrupados puede ayudar a las consultas de modificación a encontrar los datos que se van a actualizar más rápidamente, lo que reduce el número de bloqueos de actualización necesarios.

  En nuestro ejemplo de interbloqueo, el plan de ejecución de la consulta que se encuentra en el Almacén de consultas contiene un examen de índice agrupado en el índice PK_Product_ProductID. El grafo de interbloqueo indica que un componente del interbloqueo es una espera de bloqueo compartido (S) en este índice.

  

  Este examen del índice se realiza porque la consulta de actualización tiene que modificar una vista indexada llamada vProductAndDescription. Como se mencionó en la sección Búsqueda de patrones que aumentan el bloqueo de este artículo, las vistas indexadas que hacen referencia a varias tablas pueden aumentar el bloqueo y la probabilidad de interbloqueos.

  Si creamos el siguiente índice no agrupado en la base de datos AdventureWorksLT que "cubre" las columnas de SalesLT.Product a las que hace referencia en la vista indexada, esto ayuda a la consulta a encontrar las filas de forma mucho más eficaz:

  CREATE INDEX ix_Product_ProductID_Name_ProductModelID on SalesLT.Product (ProductID, Name, ProductModelID);
  GO
  

  Después de crear este índice, el interbloqueo ya no se vuelve a repetir.

  Cuando los interbloqueos implican modificaciones en las columnas a las que se hace referencia en las restricciones de clave externa, asegúrese de que los índices de la tabla de referencia de FOREIGN KEY admitan la búsqueda eficaz de las filas relacionadas.

  Aunque los índices pueden mejorar considerablemente el rendimiento de las consultas en algunos casos, también tienen costos de administración y sobrecarga. Consulte Directrices generales para el diseño de índices para ayudar a evaluar las ventajas de los índices antes de crealos, especialmente los índices anchos y los índices en tablas grandes.

• Evalúe el valor de las vistas indexadas. Otra opción para evitar que vuelva a ocurrir el interbloqueo de ejemplo es quitar la vista indexada SalesLT.vProductAndDescription. Si no se usa esa vista indexada, se reducirá la sobrecarga de mantener la vista indexada a lo largo del tiempo.

• Utilice el aislamiento de instantánea. En algunos casos, establecer el nivel de aislamiento de la transacción en instantánea para una o varias de las transacciones implicadas en un interbloqueo puede evitar que se vuelvan a producir bloqueos y interbloqueos.

  Es más probable que esta técnica tenga éxito cuando se usa en instrucciones SELECT si la instantánea de lectura confirmada está deshabilitada en una base de datos. Cuando se deshabilita la instantánea de lectura confirmada, las consultas SELECT que usan el nivel de aislamiento de lectura confirmada requieren bloqueos compartidos (S). El uso del aislamiento de instantánea en estas transacciones elimina la necesidad de bloqueos compartidos, lo que puede impedir el bloqueo y los interbloqueos.

  En las bases de datos en las que se ha habilitado el aislamiento de instantánea de lectura confirmada, las consultas SELECT no requieren bloqueos compartidos (S), por lo que es más probable que se produzcan interbloqueos entre transacciones que modifican datos. En los casos en los que se producen interbloqueos entre varias transacciones que modifican datos, el aislamiento de instantánea puede provocar un conflicto de actualización en lugar de un interbloqueo. De forma similar, esto requiere que una de las transacciones vuelva a intentar su operación.

• Fuerce un plan con el Almacén de consultas. Es posible que una de las consultas del interbloqueo tenga varios planes de ejecución y que el interbloqueo solo se produzca cuando se usa un plan específico. Puede evitar que vuelva a ocurrir el interbloqueo al forzar un plan en el Almacén de consultas.

• Modifique el código Transact-SQL. Es posible que tenga que modificar el código Transact-SQL para evitar que se vuelva a producir el interbloqueo. La modificación del código Transact-SQL se debe realizar cuidadosamente y los cambios se deben probar rigurosamente para asegurarse de que los datos sean correctos cuando las modificaciones se ejecutan simultáneamente. Al volver a escribir código Transact-SQL, tenga en cuenta lo siguiente:

  • Instrucciones de ordenación en las transacciones para que accedan a los objetos en el mismo orden.
  • Dividir las transacciones en transacciones más pequeñas cuando sea posible.
  • Usar sugerencias de consulta, si es necesario, para optimizar el rendimiento. Puede aplicar sugerencias sin cambiar el código de la aplicación mediante el Almacén de consultas.

Encuentre más formas de minimizar los interbloqueos en la guía de interbloqueos.

Eliminación de una sesión de XEvents

Es posible que desee dejar en ejecución una sesión de XEvents que recopile información de interbloqueos de bases de datos críticas durante largos períodos. Tenga en cuenta que si usa un destino de archivo de eventos, esto puede dar lugar a archivos grandes si se producen varios interbloqueos. Puede eliminar archivos de blobs de Azure Storage de un seguimiento activo, excepto el archivo en el que se está escribiendo actualmente.

Cuando desee quitar una sesión de XEvents, la instrucción de Transact-SQL para eliminar la sesión es la misma, independientemente del tipo de destino seleccionado.

Para quitar una sesión de XEvents, ejecute la siguiente instrucción de Transact-SQL. Antes de ejecutar el código, reemplace el nombre de la sesión por el valor adecuado.

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Uso del Explorador de Azure Storage

El Explorador de Azure Storage es una aplicación independiente que simplifica el trabajo con destinos de archivo de eventos almacenados en blobs de Azure Storage. Puede usar el Explorador de Storage para:

• Crear un contenedor de blobs para contener los datos de la sesión de XEvent.
• Obtener la firma de acceso compartido (SAS) de un contenedor de blobs.
  • Como se menciona en Recopilación de grafos de interbloqueos en Azure SQL Database con eventos extendidos, se requieren permisos de lectura, escritura y enumeración.
  • Quite cualquier carácter ? inicial de la cadena Query string para usar el valor como secreto al crear una credencial con ámbito de base de datos.
• Ver y descargar archivos de eventos extendidos desde un contenedor de blobs.

Descargue el Explorador de Azure Storage.

Pasos siguientes

Obtenga información adicional sobre el rendimiento en Azure SQL Database:

• Descripción y resolución de problemas de bloqueo en Azure SQL Database
• Guía de versiones de fila y bloqueo de transacciones
• Guía de interbloqueos
• SET TRANSACTION ISOLATION LEVEL
• Azure SQL Database: mejora del ajuste del rendimiento con el ajuste automático
• Entrega de rendimiento coherente con Azure SQL
• Lógica de reintento para errores transitorios