La detección de problemas en SQL Server a tiempo real es un reto que los administradores afrontan todos los días. Sin embargo, SQL Server ha hecho esfuerzos para mejorar el diagnóstico y detección de problemas como parte de sus características, evitándonos la compra de un software externo.

En este artículo, revisaremos el concepto de deadlocks, porqué ocurren, simularemos la ocurrencia de un deadlock y capturaremos el detalle del mismo con Eventos Extendidos.

¿Qué son los deadlocks y porqué ocurren?

Los deadlocks (o interbloqueos por su nombre en español) son problemas especiales de concurrencia que impiden que una transacción pueda concretarse. Ocurren cuando dos sesiones están esperando recursos que están siendo bloqueados por el otro. Esto terminaría en una espera infinita, por lo que SQL Server escoge terminar una de esas sesiones, a la que se le denomina ‘víctima de deadlock’. Para ilustrar mejor este concepto, a continuación, simularemos un deadlock:

Primero, crearemos 2 tablas Empleados y Proveedor, e insertaremos unos registros de prueba:

CREATE TABLE Empleados (
    EmpId INT IDENTITY,
    EmpNombre VARCHAR(16),
    Telefono VARCHAR(16)
)
GO

INSERT INTO Empleados (EmpNombre, Telefono)
VALUES ('Maria', '555-1212'), ('Claudia', '555-8080')
GO

CREATE TABLE Proveedor(
    ProveedorId INT IDENTITY,
    ProvNombre VARCHAR(64),
    Fax VARCHAR(16)
)
GO

INSERT INTO Proveedor (ProvNombre, Fax)
VALUES ('Basa', '555-6060'), ('Metro', '257-1234')
GO

A continuación, abriremos dos ventanas de consulta en Management Studio. En la primera ventana de consulta en SSMS, ejecutaremos lo siguiente en el orden indicado:

--PASO 1
BEGIN TRAN;
UPDATE Empleados
SET EmpNombre = 'Mary'
WHERE EmpId = 1
--PASO 3
UPDATE Proveedor
SET Fax = N'555-1212'
WHERE ProveedorId = 1

En la segunda ventana de consulta, ejecutaremos esto:

--PASO 2
BEGIN TRAN;
UPDATE Proveedor
SET Fax = N'555-1212'
WHERE ProveedorId = 1
--PASO 4
UPDATE Empleados
SET Telefono = N'555-9999'
WHERE EmpId = 1

Si hemos ejecutado las transacciones en la secuencia correcta (Pasos del 1 al 4), veremos este mensaje de error:

Msg 1205, Level 13, State 45, Line 10
Transaction (Process ID %) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Eventos Extendidos

Desde SQL Server 2012 tenemos una herramienta que detecta y nos brinda los detalles de los interbloqueos que han sucedido: Eventos Extendidos. La mejor parte de esto es que la detección de deadlocks ya viene por defecto.

El detalle de los eventos rastreados por Extended Events lo podemos encontrar dentro de la carpeta ‘Management’ en nuestra instancia de base de datos (imagen 1).

Dentro de la carpeta Sessions veremos 3 sesiones creadas por defecto, AlwaysOn_health, system_health y telemetry_xevents, estas ultimas 2 ya iniciadas.

Para ver el detalle del deadlock que acabamos de generar, bastará con abrir la sesión system_health, dar clic derecho en el paquete llamado package0.event_file y escoger la opción View Target Data.

Y así se verá nuestro deadlock registrado en los eventos:

Análisis del Deadlock

Por último, SQL Server nos provee una representación grafica del deadlock, al cual podemos acceder desde el menú Deadlock del visor de Eventos Extendidos.

Del gráfico mostrado arriba podemos concluir lo siguiente:

1. Según el gráfico, hubo 2 procesos (SPIDs 61 y 54) involucrados en el deadlock, de los cuales el 61 fue escogido como víctima de deadlock (el nodo de la izquierda tachado con X).
2. Los objetos involucrados son: dbo.Empleados y dbo.Proveedor
3. En este caso el proceso 61 fue víctima porque usa menos log (Log used: 248) que el otro proceso (Log used: 260), lo que lo hace que su rollback sea más rápido.
4. El proceso 54 obtuvo un lock exclusivo sobre el objeto dbo.Empleados (al comenzar una transacción con BEGIN TRAN sin terminarla con COMMIT o ROLLBACK), y al mismo tiempo solicitó un lock para actualizar la tabla dbo.Proveedor.
5. La tabla dbo.Proveedor ya estaba siendo usado exclusivamente por el proceso 61, el cual también estaba solicitando un lock para actualizar dbo.Empleados.}
6. La confluencia de los puntos 4 y 5 fue lo que ocasionó el deadlock.

Y tú, cómo detectas los deadlocks en tu base de datos?

Si tienes alguna duda, comentarios o sugerencias, me puedes enviar un correo a bitácoradeundba@gmail.com.