SEGUIMIENTO DE DEADLOCKS (INTERBLOQUEOS) EN SQL SERVER

SEGUIMIENTO DE DEADLOCKS (INTERBLOQUEOS) EN SQL SERVER

Es importante entender porque suceden los Deadlocks antes de tratar de corregirlos. Los Deadlocks suceden cuando dos o más procesos de SQL Server tienen bloqueos separados de objetos en la base de datos y cada uno de estos procesos intenta adquirir el bloqueo de alguno de los objetos que otro proceso ha bloqueado previamente. Cuando esto sucede SQL Server identifica el problema y finaliza el Deadlock escogiendo la eliminación de uno de los dos procesos. Normalmente se elimina el proceso que utiliza menor cantidad de recursos del servidor al momento del interbloqueo, dejando que el otro proceso continúe y realizando el respectivo Rollback sobre el proceso eliminado.

SOLUCIÓN DEL PROBLEMA

Algunos de las recomendaciones que puedo dar para solucionar el problema o prevenirlo son:

• Acceder siempre a los objetos de la base de datos en el mismo orden.
• Mantener las transacciones lo más corto posible. Trate de mantener encapsulado el proceso por ejemplo utilizando Stored procedures y de reducir la cantidad de viajes entre la aplicación y el servidor de datos. Así mismo trate de reducir los tiempos de bloqueo.
• Utilice el Nivel de isolación (Isolation level) más bajo posible para sus conexiones. El Transaction isolation level default es read-commited, con él obtenemos menos intercambio de datos (swapping) con la Temdb, pero más bloqueos. En estos casos se puede considerar habilitar el Snapshot isolation para su base de datos pero se debe considerar tener un robusto RAID 10 para almacenar la Tempdb.
• Reduzca la escalacion de bloqueos de registro o página (Rowlock, Paglock) en vez de bloqueos de tabla.
• Reduzca la cantidad y la duración de los bloqueos. Algunos procesos obtienen bloqueos innecesarios o una cantidad considerable de bloqueos haciendo lentas las consultas relacionadas con ellas. Trate de no utilizar bloqueos en las consultas.
• No permitir que los usuarios ingresen datos durante una transacción o evitar que tengan interacción con ella.
• Evitar los cursores.
• Asegurar que el diseño de la base de datos está normalizada.
• En ciertas ocasiones el aumento de la memoria RAM ayuda a mermar los interbloqueos. En cierto momento del día cuando el servidor tiene múltiples procesos ejecutando instrucciones intensas, mucha información relevante se instala en el cache del buffer y SQL Server debe ir hasta el disco para liberar la información, incrementando el tiempo de lectura y por lo tanto aumentando la posibilidad de bloqueos.

QUE ES SQL SERVER PROFILER

SQL Server Profiler es una herramienta proveída por Microsoft dentro del conjunto de aplicaciones SQL Server, esta herramienta sirve para realizar seguimientos a la actividad interna de SQL Server (trace), permitiendo ver que pasa dentro de SQL Server incluyendo los procesos en ejecución y los bloqueos como los Deadlocks. SQL Server Profiler utiliza los siguientes elementos básicos:

• Conexión: se debe realizar una conexión al servidor de base de datos a monitorear.
• Eventos: se debe seleccionar los eventos que se desean analizar, para esto la herramienta incluye un listado de eventos que van desde comandos de transacción hasta eventos de sesión.
• Columnas: se proveen una lista de columnas visibles para los monitoreos, pero algunas de estas columnas dependen del evento seleccionado. El usuario puede escoger el orden de las columnas.
• Filtros: se puede adicionar filtros a cada una de las columnas con la finalidad de capturar solo la información que se desea.

COMO IDENTIFICAR UN DEADLOCK USANDO SQL SERVER PROFILER

SQL Server Profiler se puede utilizar para visualizar, almacenar o replicar eventos ejecutados sobre el motor de bases de datos, desde la versión SQL Server 2005 se incluyeron nuevas funcionalidades al Profiler para detectar y visualizar nuevos eventos, ahora con la versión SQL Server 2012 se hacen más evidentes las mejoras incluidas para detectar y analizar los eventos de interbloqueos.

Los eventos que recomiendo incluir para realizar el seguimiento a los interbloqueos son los siguientes:

Sección Locks

                Deadlock graph

                Lock: Deadlock

                Lock: Deadlock Chain

Sección Stored Procedures

                RPC: Completed

                SP: StmtCompleted

Sección TSQL

                SQL: BatchCompleted

El evento Deadlock Graph inserta en la columna TextData información con formato XML relacionado con el proceso y los objetos que están relacionados con el deadlock. Esta información puede ser extraida a un archivo deadlock XML (.xdl) el cual se puede visualizar luego en SQL Server Management Studio, esta información se puede extraer utilizando la opción Extract SQL Server Events ubicada en el menú File.

El evento Lock:Deadlock se ejecuta cuando un bloqueo es cancelado porque es parte de un deadlocks y siempre está asociado a un evento Deadlock Graph.

El evento Lock:Deadlock Chain se ejecuta cuando sucede un interbloqueo, se dispara un evento para cada participante del deadlock, este evento sirve para determinar los objetos relacionados en el interbloqueo.

El evento RPC:Completed se ejecuta se completa la ejecución de un procedimiento remoto, por ejemplo un Stored procedure ejecutado desde una aplicación que usa CommandParameter como Texto. Si deseo analizar la duración de mis procesos se debe analizar este evento.

El evento SP:StmtCompleted indica que se ha completado una instrucción Transact-SQL que involucra un stored procedure, en algunas ocasiones este evento visualiza la misma información del evento RPC:Completed.

El evento SQL:BatchCompleted indica que se ha completado un lote de instrucciones Transact-SQL. Este evento adiciona información sobre la duración, el número de registros, lecturas y escrituras lógicas.

DEADLOCK GRAPH

Ahora analicemos más detenidamente este evento. Los componentes que incluye el grafico son los siguientes y además se define la relación con la imagen inferior:

Nodo del proceso: es un hilo que ejecuta una tarea, por ejemplo una instrucción Insert, Update o Delete. En la imagen el Nodo es el Server process id 71 o 72, allí se informa los datos del proceso. Al ubicar el mouse sobre el nodo se puede visualizar la instrucción ejecutada.

Recurso del nodo: es un objeto de la base de datos. Por ejemplo una tabla, un índice o una fila. En la imagen los recursos son los dos bloqueos de registro RID Lock.

Edge: es la relación entre el proceso y el recurso, aquí se utilizan los términos Request y Owner para definir el proceso que espera un recurso y el recurso que espera un proceso respectivamente. En la imagen podemos observar la solicitud de un recurso (Request) con una solicitud compartida (Mode: S) y en su contrapartida se encuentra el dueño de recurso (Owner) con modo exclusivo (Modo: X).

Algunos de los términos utilizados dentro del grafico son:

Nodo del proceso
Server process Id:	Es el SPID, permite relacionar el proceso.
Server batch Id:	Es el SBID, identifica el número interno del lote.
Execution context Id:	Es el ECID. Es el código del contexto de ejecución del hilo asociado al SPID.
ECID = {0,1,2,3, ...n}, donde 0 siempre representa el hilo principal y {1,2,3, ...n} representa los  sub-hilos.
Deadlock priority:	Define la prioridad del proceso. Esta prioridad se puede definir utilizando SET DEADLOCK PRIORITY.
Log Used:	Define la cantidad de espacio del Log usada por el proceso o la transacción. Esta información es útil para determinar cual transacción usa más recursos.
Owner Id:	Es el código de la transacción donde esta el proceso (Transaction ID).
Transaction descriptor:	Es el código interno del apuntador del descriptor de la transacción. Este objeto describe el estado de la transacción.
Input buffer:	Define el tipo de evento y la instrucción ejecutada. Algunos valores posibles: Language RPC None
Statement:	Define el tipo de instrucción. Algunos valores posibles: NOP SELECT UPDATE INSERT DELETE Unknown
Recurso del nodo
HoBt ID:	Este número se identifica el subconjunto de datos o la página de índices en una partición sencilla. Este número es idéntico al código del ID Partition.
Associated Objid:	Este es el código de la tabla asociada con el índice (Object ID).
Index Name:	Es el nombre del índice.
DB ID:	Es el código de la base de datos.
Page ID:	Es el código de la página de datos.

INSTRUCCIONES TRANSACT SQL

Algunas instrucciones útiles para identificar información relacionada con un deadlock son:

sp_lock: esta instrucción muestra una fila por cada bloqueo que tiene una o todas las sesiones actuales. Esta instrucción será eliminada en futuras versiones de SQL Server.

Ejemplo del script:

exec sp_lock

sys.dm_tran_locks: es una vista del sistema que devuelve los bloqueos activos en SQL Server.

Ejemplo del script:

select * from sys.dm_tran_locks

sys.partitions: es una vista del sistema que permite visualizar la Metada y devuelve cada una de las particiones de las tablas en una base de datos. Específicamente para el caso de los interbloqueos sirve para detectar la partición relacionada con el HoBt ID.

Ejemplo del script:

select * from sys.partitions

where hobt_id = 72057594065453056

sys.objects: es una vista del sistema que devuelve los objetos definidos en una base de datos. Para este caso sirve para encontrar el objeto que está relacionado con el interbloqueo, el código del objeto (object ID) se obtiene de la consulta anterior asociada a la partición.

Ejemplo del script

select * from sys.objects

where object_id = 107147427

Otra herramienta para identificar problemas relacionados con Deadlocks (aunque antigua) son las banderas del trace, para identificar cuales tablas o procedimientos almacenados están causando los problemas de deadlock, encienda las banderas 1204 y 1205, las cuales sirven para mostrar resultados básicos y detallados de los datos en el trace o la bandera 1222 para visualizar información XML sobre el deadlock. Asegurese de apagar estas banderas cuando termine de realizar la revisión porque este seguimiento consume muchos recursos del servidor disminuyendo el rendimiento.

Ejemplo del script para encender los flags:

DBCC TRACEON (3605,1204,-1)

Autor: Hermilson Tinoco Gaviria
Cali - Colombia