Deadlocks. Cómo identificarlos?

 

Muchos de nosotros hemos tenido que lidiar con el tema de los deadlocks. Quizás la mayoría de nosotros piensa que son temas que debe de resolver el programador, algunos dirán que debe de ser el DBA. El resultado final es que debe de ser un esfuerzo conjunto entre los dos equipos. El Desarrollador necesita del DBA para identificar y guiar al programador y el Desarrollador hará su magia para resolver los conflictos que se puedan presentar.

Como detectarlos?

Tenemos varias formas de hacerlo, la fácil y la complicada. A mi parecer la complicada es el Profiler. Por qué? Sencillo, cuantos pasos se requieren para configurar el Profiler y adaptarlo para que capture los deadlocks? Lo han intentado utilizar en sistemas altamente transaccionales? En Profilers que almacenan cerca de 25 millones de líneas en unas 4 horas de ejecución. Analizar luego esa data es complejo, se los aseguro.
El uso de Profiler con deadlocks es un tema que analizaremos posteriormente. Hoy me quiero enfocar en el mas sencillo de los dos. Utilizar los eventos de WMI de Windows es la forma mas sencilla de configurar y detectar los deadlocks.

Cuales son los pasos?

1. Ingresar a la consola de SQL Server.
2. Expandir el SQL Server Agent.
3. Sobre Alerts, presionar botón derecho. Crear nueva Alerta.
4. Seleccionar el Type WMI Event Alert
5. En el query ingresar SELECT * FROM DEADLOCK_GRAPH



Idealmente se debe de crear un JOB que permita la respuesta a esta alerta. El job lo que hará es insertar los datos recolectados por a alerta para nuestro posterior análisis.

Se debe de crear la tabla en donde vamos a almacenar los datos.

CREATE TABLE [dbo].DeadlockTrace(

[AlertTime] [datetime] NULL,

[DeadlockGraph] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

El Job debe de tener la siguiente sentencia :

INSERT INTO DeadlockTrace  (AlertTime, DeadlockGraph)
VALUES (getdate(), N'$(ESCAPE_SQUOTE(WMI(TextData))))')

Recordemos asignar en la alerta en el TAB Response el Job que recién acabamos de crear.

Para tener una vista comprendible de los datos ejecutamos la siguiente sentencia.

WITH CTE AS (
  SELECT AlertTime,
    CAST(REPLACE(REPLACE(CAST(DeadlockGraph AS VARCHAR(MAX)),'</TextData>)',''),'<TextData>','') AS XML) AS DeadlockGraph
  FROM dbo].[DeadlockTrace] )
SELECT ROW_NUMBER() OVER(PARTITION BY [AlertTime] ORDER BY [AlertTime] DESC) AS DeadlockNumber,
  [AlertTime],
  [DeadlockGraph],
  [PagelockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
  [DeadlockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
  [Victim] = CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') THEN 1 ELSE 0 END,
  [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
  [LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
  [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
  [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
  [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
  [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
  [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
  [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
FROM CTE
CROSS APPLY
  CTE.[DeadlockGraph].nodes('/deadlock-list/deadlock/process-list/process') AS Deadlock(Process)
ORDER BY 1 DESC

Y listo, ya podemos analizar de una manera mas sencilla los deadlocks en nuestra base de datos.