Bloqueo procedimiento por un Blk By en SQL server

Bloqueo procedimiento por un Blk By en SQL Server. En SQL Server, la gestión de bloqueos es una tarea crucial para garantizar el rendimiento y la disponibilidad de las bases de datos. Los bloqueos ocurren cuando un proceso en SQL Server impide que otro proceso acceda a los recursos que necesita, lo que puede llevar a cuellos de botella y problemas de rendimiento. Una herramienta clave para identificar y resolver estos problemas es la columna «Blk By» (Blocked By), que indica qué sesión está bloqueando a otra. En este blog, exploraremos qué es el bloqueo en SQL Server, cómo utilizar «Blk By» para identificar bloqueos y algunos ejemplos prácticos para gestionar y solucionar estos problemas.

¿Qué es un bloqueo en SQL Server?

Un bloqueo en SQL Server ocurre cuando un proceso (una sesión o transacción) mantiene un recurso, como una tabla o una fila, y otro proceso intenta acceder a ese mismo recurso. SQL Server utiliza bloqueos para garantizar la integridad de los datos, pero cuando los bloqueos no se gestionan correctamente, pueden causar que otros procesos se queden esperando indefinidamente, lo que se conoce como bloqueo.

¿Qué es «Blk By» y cómo funciona?

La columna «Blk By» (Blocked By) es una propiedad en la vista del sistema sys.dm_exec_requests que indica el ID de la sesión que está bloqueando a otra sesión. Esta información es crucial para identificar qué transacciones están bloqueando otras operaciones y es el primer paso para resolver problemas de rendimiento relacionados con bloqueos.

Puedes obtener una lista de sesiones bloqueadas y las sesiones que las están bloqueando usando la siguiente consulta T-SQL:

sql: SELECT
    session_id AS [SPID],
    blocking_session_id AS [Blk By],
    wait_type,
    wait_time,
    wait_resource,
    transaction_id,
    TEXT AS [SQL Text]
FROM
    sys.dm_exec_requests
CROSS APPLY
    sys.dm_exec_sql_text(sql_handle)
WHERE blocking_session_id <> 0;

Este script te proporciona una lista de sesiones que están actualmente bloqueadas, mostrando el ID de la sesión bloqueada, el ID de la sesión que la está bloqueando, el tipo de espera, el tiempo de espera y el recurso específico que está causando el bloqueo.

Ejemplo Práctico: Identificación y Resolución de un Bloqueo

Imagina que tienes dos transacciones ejecutándose en tu base de datos:

1. Transacción A: Actualiza una fila en la tabla Orders.
2. Transacción B: Intenta leer la misma fila en la tabla Orders antes de que la Transacción A haya finalizado.

Aquí es donde podría ocurrir un bloqueo:

• La Transacción A adquiere un bloqueo exclusivo en la fila para actualizarla.
• La Transacción B intenta leer esa misma fila, pero se encuentra bloqueada porque la Transacción A aún no ha terminado.

Si ejecutas el script mencionado anteriormente durante este bloqueo, verás algo como esto:

SPID	Blk By	wait_type	wait_time	wait_resource	transaction_id	SQL Text
53	52	LCK_M_S	2000	PAGE: 7:1:123	123456	SELECT * FROM Orders WHERE …

En este caso:

• SPID 53 está bloqueado por SPID 52.
• El wait_type indica que SPID 53 está esperando un bloqueo compartido (LCK_M_S).
• La columna wait_time muestra cuánto tiempo ha estado esperando la transacción bloqueada.

Resolución de Bloqueos

Una vez identificado el bloqueo utilizando «Blk By», hay varias formas de resolverlo:

1. Revisión del código: Analiza las consultas que están causando los bloqueos. En muchos casos, es posible optimizar las consultas o la lógica de las transacciones para reducir la posibilidad de bloqueos.
2. Terminación de la sesión bloqueadora: Si el bloqueo está afectando gravemente al rendimiento y no puede resolverse rápidamente, puedes optar por finalizar la sesión que está causando el bloqueo. Esto se hace con el comando KILL:

KILL 52;  — Termina la sesión bloqueadora SPID 52

1. Advertencia: Finalizar una sesión puede causar la pérdida de datos si la transacción no se ha completado.
2. Implementar niveles de aislamiento de transacciones: Puedes ajustar los niveles de aislamiento de las transacciones para reducir la probabilidad de bloqueos. Por ejemplo, puedes utilizar Read Committed Snapshot Isolation (RCSI) para permitir que las transacciones lean datos sin esperar a que se liberen los bloqueos.

Consejos para Evitar Bloqueos en el Futuro

• Mantén las transacciones cortas: Cuanto más tiempo duren las transacciones, mayor es la posibilidad de que causen bloqueos. Asegúrate de que las transacciones se completen lo más rápido posible.
• Orden de acceso a los recursos: Diseña tus aplicaciones para que accedan a los recursos en un orden coherente. Esto ayuda a evitar situaciones en las que dos transacciones se bloquean mutuamente.
• Monitoreo continuo: Usa herramientas de monitoreo de SQL Server para detectar bloqueos en tiempo real y actuar antes de que se conviertan en un problema mayor.

Porque puede bloquear una de sus propias operaciones SQL Server

Un proceso en SQL Server puede bloquear una de sus propias operaciones en situaciones donde hay competencia por los mismos recursos que el proceso necesita para continuar. Esto generalmente ocurre debido a la forma en que SQL Server maneja los bloqueos y las transacciones. Aquí te explico algunas de las razones más comunes por las cuales un proceso puede bloquearse a sí mismo:

1. Contención de Recursos dentro de una Transacción

• Cuando un proceso ejecuta múltiples operaciones que necesitan acceder al mismo recurso dentro de una transacción, SQL Server puede bloquearse si la primera operación aún no ha completado su trabajo en ese recurso.
• Ejemplo: Si un índice se está reconstruyendo y la operación intenta acceder a las mismas páginas de datos o índices que ya están bloqueados por otra parte de la misma transacción, esto puede causar un bloqueo interno.

2. Escalado de Bloqueos

• SQL Server puede escalar un bloqueo de nivel de fila o página a un bloqueo de nivel de tabla si se están tocando muchos recursos. Si una parte del proceso ya tiene un bloqueo en una fila o página y otra parte del mismo proceso intenta escalar ese bloqueo, podría bloquearse a sí mismo.
• Ejemplo: Durante una operación de índice o actualización masiva, SQL Server intenta bloquear la tabla completa después de haber adquirido muchos bloqueos de página, lo que causa que la operación espere a sí misma.

3. Operaciones Concurrentes en Paralelo

• En algunos casos, SQL Server puede ejecutar partes de un mismo proceso en paralelo (especialmente si la opción MAXDOP está habilitada). Si una de estas operaciones paralelas necesita un recurso que otra parte del proceso ya ha bloqueado, puede haber un bloqueo interno.
• Ejemplo: Diferentes threads del mismo proceso paralelo intentan acceder a la misma página de índice al mismo tiempo.

4. Espacio en TempDB

• Si el proceso está usando tempdb intensivamente, como cuando se reconstruyen índices con la opción SORT_IN_TEMPDB, y tempdb no tiene suficiente espacio, esto puede hacer que las diferentes partes del proceso se bloqueen mientras esperan recursos en tempdb.

5. Actualizaciones o Modificaciones Complejas

• Si un proceso realiza operaciones complejas de actualización que requieren múltiples pasos en una sola transacción, SQL Server podría establecer bloqueos en diferentes niveles de la jerarquía (fila, página, tabla) y acabar bloqueando el proceso.
• Ejemplo: Una actualización que involucra una combinación de varias filas y luego un intento de acceso a esas mismas filas en la misma transacción.

6. Deadlocks Internos

• Aunque menos común, un proceso puede experimentar un deadlock interno, donde diferentes partes de una misma operación se bloquean mutuamente.

Diagnóstico y Resolución bloqueo procedimiento por un Blk By en SQL Server

• Monitoreo de Esperas (sys.dm_exec_requests):
• Usa la consulta que te proporcioné antes para identificar el tipo de espera (wait_type) y el recurso (wait_resource) que está causando el bloqueo.
• Revisión del Plan de Ejecución:
• Si el proceso involucra una consulta compleja, revisa el plan de ejecución para ver si hay operaciones que están escalando los bloqueos o creando contenciones inesperadas.
• Revisar las Transacciones:
• Si el proceso está ejecutando múltiples pasos dentro de una transacción, considera si es posible dividir las operaciones o mejorar el manejo de los bloqueos para evitar la contención.
• Optimización de Índices y Consultas:
• Asegúrate de que las consultas y las operaciones de índice están optimizadas para evitar acceder repetidamente a los mismos recursos en paralelo.
• Uso de Hints de Bloqueo:
• Si es apropiado, considera el uso de hints de bloqueo para controlar mejor cómo SQL Server maneja los bloqueos durante la operación.

Conclusión

Gestionar los bloqueos en SQL Server es fundamental para mantener el rendimiento y la disponibilidad de las bases de datos. La columna «Blk By» es una herramienta esencial para identificar rápidamente las sesiones que están causando bloqueos y tomar medidas correctivas. Implementar buenas prácticas y utilizar herramientas de monitoreo proactivas te ayudará a minimizar la incidencia de bloqueos y mantener tus bases de datos funcionando de manera eficiente.

Cómo Obtener el tamaño de los archivos de Datos en SQL Server

dm_exec_procedure_stats en SQL Server

dm_exec_procedure_stats en SQL Server

Script Creación de Roles en SQL Server

¿Qué es el Transaction Log? La Importancia en SQL Server

Procedimientos Almacenados Temporales en SQL Server

Identificar Objetos No Utilizados en su Base de Datos SQL Server