Los procesos bloqueados causados por bloqueos en los objetos de la base de datos son un problema común. El bloqueo asegura la integridad de los datos almacenados dentro de las bases de datos forzando cada transacción SQL Server ejecutada a pasar la prueba ACID que considera que cada transacción debe cumplir con los siguientes requerimientos:
- Atomicidad –una transacción debe ser totalmente completada cuando se ejecuta, o no se ejecutará.
- Consistencia – las transacciones deben crear un nuevo estado de datos que sea válido, o si cualquier fallo ocurre, debe retornar todos los datos al estado que estaba antes de que la transacción sea ejecutada/iniciada.
- Aislamiento – una transacción que está en proceso de ejecución y no ha sido enviada aún, debe ser aislada de todas las otras transacciones.
- Durabilidad – los datos enviados deben ser grabados en una forma que mantendrá los datos en un estado correcto si la transacción no se completa, en el evento de una falla o un reinicio del sistema.
Como parte de la prueba ACID, el aislamiento es realizado bloqueando objetos y, como tal, el aislamiento puede afectar una o más filas de datos hasta que la transacción SQL Server que es ejecutada contra ese objeto sea enviada. Esto significa, en la práctica que, bloqueando el objeto, SQL Server evitará que cualquier otro proceso haga algún cambio contra los datos en ese objeto hasta que el bloqueo sea removido, ya sea enviando el cambio o retrotrayéndolo.
Imponer el bloqueo en un objeto causará que cualquier otro proceso que mande una solicitud contra el objeto bloqueado tenga que esperar hasta que el bloqueo es removido, lo cual retrasará la ejecución de ese proceso. El estado cuando una transacción está esperando para que el bloqueo sea removido o está parada es lo que se conoce como bloqueo. SQL Server está diseñado para trabajar de esta forma y, como tal, los bloqueos son una problemática inherente. Por tanto, sólo cuando un nivel alto de bloqueos ocurre, es detectado en SQL Server, o en un caso en el que el bloqueo ocurre y requiere de cantidades excesivas de tiempo para ser resuelto, debería ser tratado como un problema potencial.
Un ejemplo práctico de bloqueo de SQL Server es cuando la Transacción #1 está tratando de actualizar datos en la Tabla A, y mientras la Transacción #1 aún está corriendo y no está completada, la Transacción #2 intenta emplazar un nuevo bloqueo en la Tabla A; si la fila que debería ser eliminada es también la fila que será actualizada por la Transacción #2, entonces la Transacción #2 encontrará u bloqueo. Esto es porque la Transacción #1 tiene un bloqueo exclusivo impuesto en la tabla mientras la transacción esté corriendo.
Tomemos los siguientes scripts como un ejemplo.
Script 1
BEGIN TRAN UPDATE [dbo].[TableX] SET [ZAP_ID] = 5 WHERE [ZAP_ID] = 9 COMMIT
Script 2a
DELETE FROM [dbo].[TableX] WHERE [ZAP_ID] = 9
Script 2b
SELECT COUNT(*) FROM [dbo].[TableX]
Script 2c
TRUNCATE TABLE [dbo].[TableX]
Estos scripts serán usando para crear un bloqueo de SQL Server.
Primero, ejecute el Script 1, pero sólo la parte resaltada en amarillo (no ejecute el comando COMMIT). El script comenzará una transacción y emplazará un bloqueo en filas específicas en TableX.
Ahora, ejecute cualquiera de los otros scripts y ellos no podrán completarse porque la transacción inicial mantendrá su bloqueo exclusivo en las mismas filas en TableX que otros scripts están tratando de acceder. Como consecuencia de esto, los scripts tendrán que esperar a que transacción se complete (para realizar un COMMIT en la transacción).
Este simple ejemplo ilustra una situación cuando una transacción que está en progreso puede BLOQUEAR uno o más procesos que deben esperar hasta que la transacción con bloqueo exclusivo se complete o retrotraiga.
Así que, ¿cómo identificamos las instancias de SQL Server bloqueadas? Hay un par de maneras de hacer esto en SQL Server:
- Usando un monitor de actividad
- Usando DMVs de SQL Server
Usar el Monitor de Actividad (Activity Monitor)
Activity Monitor es una herramienta de SQL Server Mangement Studio que permite al usuario ver detalles de las conexiones SQL Server existentes, y puede ser usada para mostrar información acerca de los procesos abiertos y los bloqueos en los recursos de SQL Server.
Para abrir Activity Monitor en SQL Server Management Studio, haga clic derecho en el nombre del servidor en Object Explorer y haga clic en Activity Monitor.
Expanda Processes y localice el proceso que está esperando. Una vez que el proceso es localizado, dé un vistazo la columna Blocked para encontrar el ID del proceso que está causando el bloqueo.
Ahora, cuando el proceso que está causando el bloqueo es identificado, tiene que ser localizado en la misma página Processes.
Ahora, cuando la información acerca del proceso que está causando el bloqueo es encontrada, el usuario puede optar por matar ese proceso o permitirle correr hasta que se complete. Para matar el proceso, haga clic derecho en él y seleccione Kill Process.
Usando DMVs
La vista de administración dinámica sys.dm_exec_request es usada para identificar los bloqueos de SQL Server. Puede ser usada para listar los bloqueos actualmente activos ejecutando la siguiente consulta.
USE [master] GO SELECT session_id, blocking_session_id, wait_time, wait_type, last_wait_type, wait_resource, transaction_isolati, on_level, lock_timeout FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 GO
Puede verse en los resultados qué proceso está bloqueado (sesión_id), qué proceso está bloqueando (block_session_id), qué tipo de espera está involucrado y cuál es el tiempo de espera de ese bloqueo, así como los recursos involucrados.
Para terminar la sesión que bloquea, ID 74 en este caso, usamos el comando KILL.
KILL 74 GO
|
Consejo rápido: Tenga cuidado al matar un proceso y asegúrese de que hay un completo entendimiento de qué está haciendo ese proceso y por qué está causando bloqueos. Cuando se mata una transacción, todos los cambios de datos hecho por ese proceso pueden ser retrotraídos a un estado previo. |
Pero estos métodos nativos de SQL Server pueden ser usados sólo para detectar e identificar bloqueos activos. En muchas situaciones, cuando un bloqueo es detectado e identificado, es importante saber si este constituye un comportamiento normal y si ocurre raramente o sólo una vez. Sin tal información, no es posible realizar la resolución de problemas requerida, simplemente porque no está disponible ningún dato histórico como la frecuencia del bloqueo y la extensión del bloqueo.
Recursos útiles:
- Cómo encontrar tablas bloqueadas y matar la sesión en SQL Server
- Solucionando problemas de bloqueos en SQL Server 2000 y 2005
- Microsoft – SQL Server – Recurso de Espera – Objeto de Base de Datos y Página
- Mejor documentación para tareas esperando recursos
julio 7, 2017
