Bloqueos y Deadlocks en SQL Server

Entender la concurrencia de nuestros procesos es muy importante para los DBAs que queremos optimizar el rendimiento de nuestras bases de datos. En este sentido, muchas veces hemos nombrado en el blog los bloqueos. Sin embargo, en este artículo vamos a profundizar en el tema y vamos a tratar de llevar nuestros conocimientos sobre el tema al siguiente nivel.

 ¿Qué es un bloqueo y por qué se produce?

Un bloqueo es un mecanismo que SQL Server utiliza para garantizar la integridad de los datos cuando hay transacciones concurrentes que acceden a los mismos recursos. Un recurso puede ser una fila, una página, una tabla, una base de datos o cualquier otro objeto de SQL Server. Una transacción es una secuencia de operaciones que se ejecutan como una unidad lógica y que deben cumplir las propiedades ACID (atomicidad, consistencia, aislamiento y durabilidad). Cuando una transacción accede a un recurso, solicita un bloqueo sobre él, que puede ser de varios tipos y modos, dependiendo de la operación que realiza y del nivel de aislamiento que tiene.

Por ejemplo, una transacción que lee un recurso solicita un bloqueo compartido (S), que permite que otras transacciones también puedan leer el mismo recurso, pero no modificarlo. Una transacción que modifica un recurso solicita un bloqueo exclusivo (X), que impide que otras transacciones puedan acceder al mismo recurso, ya sea para leerlo o modificarlo.

Tipos de bloqueos

Acabamos de nombrar los bloqueos compartidos (S) y los exclusivos (X) pero, estos no son los únicos tipos de bloqueos en SQL Server. Además de los bloqueos compartidos (S) y los exclusivos (X) nos podemos encontrar con bloqueos de actualización (U) que, son parecidos a los exclusivos pero más flexibles. En estos casos si un recurso tiene un bloqueo compartido el bloqueo de actualización generará otro bloqueo compartido hasta que pueda generar el bloqueo exclusivo. 

Para terminar tenemos los llamados bloqueos de intención o propósito (I), estos se dan cuando SQL Server sabe que va a necesitar uno de los bloqueos anteriores. De esta manera, SQL se garantiza poder obtenerlo cuando lo necesite. Nos podemos encontrar con 3 tipos de bloqueos de intenciones generales: IS, IX o IU en función de si el bloqueo que va a necesitar es compartido, exclusivo o de actualización.
Para terminar con los bloqueos de intención tenemos los bloqueos de intención de conversión y también tenemos 3 tipos. El primero de ellos es el bloqueo SIX que se da cuando SQL va a necesitar un bloqueo compartido (S) sobre una serie de recursos que va a leer y posteriormente tiene intención (I) de generar un bloqueo exclusivo (X) sobre unos pocos de esos recursos que va a actualizar. Del mismo modo tenemos los bloqueos SIU si un bloqueo compartido (S) se va a convertir en uno de actualización (U) y los UIX cuando un bloqueo de actualización (U) se va a convertir en un bloqueo exclusivo (X).

Otros tipos de bloqueos

Por otro lado, SQL Server implementa también bloqueos a nivel de esquema Sch-M y Sch-S. El primero se da cuando ejecutamos una operación DDL que modifica el esquema y es exclusivo mientras que el segundo es compartido y lo veremos cuando una consulta dependa de un esquema. 

Para terminar tenemos los bloqueos BU de carga masiva que se producen al cargar una tabla con Bulk Insert y la opción TABLOCK. Estas cargas no permitirán el acceso a la tabla mientras estén en curso.

Jerarquía de los bloqueos.

Otro aspecto que debemos tener en cuenta es el nivel de granularidad de los bloqueos, es decir, el tamaño de las unidades de datos que se bloquean. SQL Server puede utilizar cuatro niveles de granularidad: base de datos, tabla, página o fila. El primero de ellos nos garantiza por ejemplo que una base de datos no se puede eliminar o restaurar mientras está en uso por alguna consulta. Cualquier operación sobre un objeto de base de datos genera un bloqueo compartido en la propia base de datos.

Los otros tres nos los podremos encontrar en nuestras consultas. El nivel de granularidad afecta al rendimiento y a la contención. Por un lado, un nivel más fino (fila) reduce la probabilidad de que dos transacciones accedan al mismo recurso, pero aumenta el número de bloqueos y el consumo de memoria. Por otro lado, un nivel más grueso (tabla) reduce el número de bloqueos y el consumo de memoria, pero aumenta la probabilidad de contención y tiempo de espera. SQL Server utiliza un algoritmo dinámico para elegir el nivel de granularidad más adecuado para cada caso, pero también podemos influir en esta decisión mediante las opciones de índice o las sugerencias de tabla.

Cómo funcionan los bloqueos

Si habéis llegado hasta este punto con el cerebro como un yogurt es normal. Esto no es fácil de entender, vamos a verlo con un ejemplo simple para entenderlo. Supongamos que en nuestra base de datos Animalitos tenemos un esquema MascotasAdorables y en ella una tabla Gatetes. Cuando nosotros queramos leer una fila de nuestra tabla, SQL Server generará un bloqueo compartido (S) sobre la fila de la tabla que vamos a leer, pero a la vez generará un intento de bloqueo compartido (IS) sobre la página que contiene esa fila y sobre la tabla MascotasAdorables y otro bloqueo (S) sobre la base de datos Animalitos.

Los dos bloqueos compartidos (S) están claros, como hemos dicho SQL siempre genera un bloqueo sobre la base de datos y al ser un select genera otro sobre la fila que estamos leyendo. Los bloqueos de intención compartidos (IS) nos garantizan que se podrán escalar a un bloqueo compartido (S) si alguien intenta borrar o modificar nuestra tabla o la página mientras estamos leyendo. 

Compatibilidad de los bloqueos

Como hemos visto, un recurso puede tener varios bloqueos compartidos simultáneos y sin embargo solo uno exclusivo (por eso sus nombres, tampoco se mataron pensando aquí). Ahora vamos a ver como de compatibles son los bloqueos entre ellos.

Deadlocks

Los deadlocks, en español interbloqueos o bloqueos permanentes, se producen cuando dos o más transacciones se bloquean mutuamente, impidiendo que ninguna de ellas pueda avanzar. 

Imagina que tenemos dos transacciones la A y la B. Nuestro proceso A ha leído una fila de la tabla gatetes y quiere escribirla en la tabla felinos. Es decir ha generado un bloqueo compartido (S) en la tabla gatetes y tiene que generar un bloqueo exclusivo (X) en la tabla felinos. Cuando va a generar este bloqueo se encuentra con que el proceso B ya ha leído de la tabla felinos y tiene sobre ella un bloqueo compartido (S) por lo que se tiene que esperar a que termine (según la tabla anterior S y X no son compatibles). Sin embargo el proceso B no va a terminar nunca porque está esperando a escribir en la tabla gatetes lo que ha leído. Esa escritura es un bloqueo compartido (X) que no puede hacerse porque ya hay un bloqueo compartido sobre esa tabla. 

SQL Server detecta estos escenarios y elige una de las transacciones como víctima, matándola y liberando los recursos que tenía bloqueados. Esto puede causar errores y pérdidas de datos en las aplicaciones que no están preparadas para manejar estos casos. 

¿Cómo podemos evitar o reducir los bloqueos e interbloqueos en SQL Server? 

Hay varias estrategias que podemos aplicar, dependiendo del contexto y el tipo de operaciones que realizamos. Algunas de estas estrategias son:

• Diseñar índices adecuados que permitan acceder a los datos con el menor número de lecturas y escrituras posibles.
• Utilizar transacciones cortas y simples que minimicen el tiempo que mantienen los bloqueos sobre los recursos.
• Elegir el nivel de aislamiento apropiado para cada transacción, teniendo en cuenta el equilibrio entre la consistencia de los datos y el rendimiento. En anteriores artículos hemos hablado de los diferentes niveles de aislamiento que ofrece SQL Server y cómo afectan a los bloqueos y a la visibilidad de los datos.
• Evitar el uso indiscriminado de la opción NOLOCK, que puede provocar lecturas sucias, perdidas o duplicadas. En este artículo explicamos los problemas del uso de NOLOCK.
• Monitorizar los bloqueos e interbloqueos que se producen en nuestras bases de datos, utilizando las herramientas y vistas que SQL Server nos proporciona. De esta forma podremos identificar las causas y las soluciones más adecuadas para cada caso.

¿Cómo podemos identificar los bloqueos e interbloqueos que se producen en nuestras bases de datos?

SQL Server nos ofrece varias herramientas y vistas para monitorizar y analizar estos fenómenos. Algunas de estas herramientas y vistas son:

• El Monitor de actividad, que nos permite ver en tiempo real las sesiones, procesos, bloqueos e interbloqueos que se están produciendo en nuestra instancia.
• La vista sys.dm_tran_locks, que nos muestra información detallada sobre los bloqueos actuales, como el tipo, el modo, el recurso, la sesión y la transacción asociados.
• La vista sys.dm_exec_requests, que nos muestra información sobre las solicitudes en ejecución, como el estado, el tiempo transcurrido, el tiempo esperado y la causa del tiempo de espera.
• La vista sys.dm_os_waiting_tasks, que nos muestra información sobre las tareas en espera, como la sesión, la solicitud, la duración y el tipo del tiempo de espera.
• El evento extendido deadlock graph, que nos muestra información sobre los interbloqueos ocurridos, como las transacciones involucradas, los recursos implicados y la víctima elegida.

Conclusión

Los bloqueos e interbloqueos son aspectos inevitables en cualquier sistema de gestión de bases de datos relacionales, pero no por ello debemos resignarnos a sufrir sus consecuencias negativas. Con un buen diseño, una correcta elección del nivel de aislamiento y una constante monitorización podremos mejorar el rendimiento y la fiabilidad de nuestras bases de datos. Esperamos que este artículo os haya sido útil y os animamos a compartir vuestras experiencias y dudas con nosotros en los comentarios.