Free Press

El bloqueo es esencial para el éxito del procesamiento de las transacciones de SQL Server y está diseñado para permitir que SQL Server funcione sin problemas en un entorno multiusuario. El bloqueo es la forma en que SQL Server gestiona la concurrencia de las transacciones. Esencialmente, los bloqueos son estructuras en memoria que tienen propietarios, tipos y el hash del recurso que debe proteger. Un bloqueo como estructura en memoria tiene un tamaño de 96 bytes.

Para entender mejor el bloqueo en SQL Server, es importante comprender que el bloqueo está diseñado para asegurar la integridad de los datos en la base de datos, ya que obliga a cada transacción de SQL Server a pasar la prueba ACID.

El test ACID consta de 4 requisitos que toda transacción tiene que pasar con éxito:

• Atomicidad – requiere que una transacción que involucra dos o más partes discretas de información debe comprometer todas las partes o ninguna
• Consistencia – requiere que una transacción debe crear un estado válido de datos nuevos, o debe retroceder todos los datos al estado que existía antes de que se ejecutara la transacción
• Aislamiento – requiere que una transacción que todavía se está ejecutando y no comprometió todos los datos todavía, debe permanecer aislada de todas las demás transacciones
• Durabilidad – requiere que los datos consignados se almacenen utilizando un método que preserve todos los datos en un estado correcto y disponible para un usuario, incluso en caso de fallo

El bloqueo de SQL Server es la parte esencial del requisito de aislamiento y sirve para bloquear los objetos afectados por una transacción. Mientras los objetos están bloqueados, SQL Server impedirá que otras transacciones realicen cualquier cambio de los datos almacenados en los objetos afectados por el bloqueo impuesto. Una vez que se libere el bloqueo mediante la consignación de los cambios o la reversión de los cambios al estado inicial, se permitirá a otras transacciones realizar los cambios de datos necesarios.

Traducido al lenguaje de SQL Server, esto significa que cuando una transacción impone el bloqueo a un objeto, todas las demás transacciones que requieran el acceso a ese objeto se verán obligadas a esperar hasta que se libere el bloqueo y esa espera se registrará con el tipo de espera adecuado

Los bloqueos de SQL Server se pueden especificar a través de los modos de bloqueo y la granularidad del bloqueo

Modos de bloqueo

El modo de bloqueo considera varios tipos de bloqueo que se pueden aplicar a un recurso que tiene que ser bloqueado:

• Exclusivo (X)
• Compartido (S)
• Actualización (U)
• Intención (I)
• Esquema (Sch)
• Actualización masiva (BU)

Bloqueo exclusivo (X) – Este tipo de bloqueo, cuando se impone, garantizará que una página o fila se reservará exclusivamente para la transacción que impuso el bloqueo exclusivo, mientras la transacción mantenga el bloqueo.

El bloqueo exclusivo será impuesto por la transacción cuando quiera modificar los datos de la página o fila, que es en el caso de las sentencias DML DELETE, INSERT y UPDATE. Sólo se puede imponer un bloqueo exclusivo a una página o fila si no hay ningún otro bloqueo compartido o exclusivo impuesto ya en el objetivo. Esto significa prácticamente que sólo se puede imponer un bloqueo exclusivo a una página o fila, y una vez impuesto no se puede imponer ningún otro bloqueo en los recursos bloqueados

Bloqueo compartido (S) – este tipo de bloqueo, cuando se impone, reservará una página o fila para que esté disponible sólo para la lectura, lo que significa que cualquier otra transacción no podrá modificar el registro bloqueado mientras el bloqueo esté activo. Sin embargo, un bloqueo compartido puede ser impuesto por varias transacciones al mismo tiempo sobre la misma página o fila y de esa manera varias transacciones pueden compartir la capacidad de lectura de datos ya que el proceso de lectura en sí mismo no afectará de ninguna manera a los datos de la página o fila actual. Además, un bloqueo compartido permitirá operaciones de escritura, pero no se permitirán cambios DDL

Bloqueo de actualización (U) – este bloqueo es similar a un bloqueo exclusivo pero está diseñado para ser más flexible en cierto modo. Un bloqueo de actualización puede imponerse a un registro que ya tiene un bloqueo compartido. En este caso, el bloqueo de actualización impondrá otro bloqueo compartido en la fila de destino. Una vez que la transacción que tiene el bloqueo de actualización está lista para cambiar los datos, el bloqueo de actualización (U) se transformará en un bloqueo exclusivo (X). Es importante entender que el bloqueo de actualización es asimétrico con respecto a los bloqueos compartidos. Mientras que el bloqueo de actualización puede imponerse en un registro que tiene el bloqueo compartido, el bloqueo compartido no puede imponerse en el registro que ya tiene el bloqueo de actualización

Bloqueos de intención (I) – este bloqueo es un medio utilizado por una transacción para informar a otra transacción sobre su intención de adquirir un bloqueo. El propósito de este bloqueo es asegurar que la modificación de los datos se ejecute correctamente impidiendo que otra transacción adquiera un bloqueo sobre el objeto siguiente en la jerarquía. En la práctica, cuando una transacción quiere adquirir un bloqueo en la fila, adquirirá un bloqueo de intención en una tabla, que es un objeto de jerarquía superior. Al adquirir el bloqueo de intención, la transacción no permitirá que otras transacciones adquieran el bloqueo exclusivo en esa tabla (de lo contrario, el bloqueo exclusivo impuesto por alguna otra transacción cancelaría el bloqueo de la fila).

Este es un tipo de bloqueo importante desde el punto de vista del rendimiento, ya que el motor de base de datos de SQL Server inspeccionará los bloqueos de intención sólo a nivel de tabla para comprobar si es posible que la transacción adquiera un bloqueo de forma segura en esa tabla, y por lo tanto el bloqueo de intención elimina la necesidad de inspeccionar cada bloqueo de fila/página en una tabla para asegurarse de que la transacción puede adquirir el bloqueo en toda la tabla

Hay tres bloqueos de intención regulares y tres llamados bloqueos de conversión:

Los bloqueos de intención regulares:

Intento exclusivo (IX) – cuando se adquiere un bloqueo de intento exclusivo (IX) indica a SQL Server que la transacción tiene la intención de modificar algunos de los recursos de jerarquía inferior adquiriendo bloqueos exclusivos (X) individualmente en esos recursos de jerarquía inferior

Intento compartido (IS) – cuando se adquiere un bloqueo de intento compartido (IS) indica a SQL Server que la transacción tiene la intención de leer algunos recursos de jerarquía inferior adquiriendo bloqueos compartidos (S) individualmente en esos recursos inferiores de la jerarquía

Intent update (IU) – cuando se adquiere un bloqueo compartido de intención (IS) indica a SQL Server que la transacción tiene la intención de leer algunos recursos de jerarquía inferior adquiriendo bloqueos compartidos (S) individualmente en esos recursos inferiores de la jerarquía. El bloqueo de actualización de intención (IU) sólo puede adquirirse a nivel de página y, en cuanto se produce la operación de actualización, se convierte en el bloqueo exclusivo de intención (IX)

Conversión de bloqueos:

Compartido con intención exclusiva (SIX) – cuando se adquiere, este bloqueo indica que la transacción tiene la intención de leer todos los recursos de jerarquía inferior y, por lo tanto, adquirir el bloqueo compartido en todos los recursos que están más abajo en la jerarquía y, a su vez, modificar parte de ellos, pero no todos. Al hacerlo, adquirirá un bloqueo de intención exclusiva (IX) sobre aquellos recursos de jerarquía inferior que deban ser modificados. En la práctica, esto significa que una vez que la transacción adquiere un bloqueo SIX sobre la tabla, adquirirá un bloqueo exclusivo de intención (IX) sobre las páginas modificadas y un bloqueo exclusivo (X) sobre las filas modificadas.

Sólo se puede adquirir un bloqueo compartido con intención exclusiva (SIX) sobre una tabla a la vez y bloqueará a otras transacciones para que no realicen actualizaciones, pero no impedirá que otras transacciones lean los recursos de jerarquía inferior que pueden adquirir el bloqueo compartido con intención (IS) sobre la tabla

Compartido con intención de actualización (SIU) – este es un bloqueo un poco más específico ya que es una combinación de los bloqueos compartidos (S) y de intención de actualización (IU). Un ejemplo típico de este bloqueo es cuando una transacción está utilizando una consulta ejecutada con la sugerencia y consulta PAGELOCK, y luego la consulta de actualización. Después de que la transacción adquiere un bloqueo SIU en la tabla, la consulta con la sugerencia PAGELOCK adquirirá el bloqueo compartido (S) mientras que la consulta de actualización adquirirá el bloqueo de actualización de intención (IU)

Actualización con intención exclusiva (UIX) – cuando se adquieren simultáneamente los bloqueos de actualización (U) y de intención exclusiva (IX) en los recursos de jerarquía inferior de la tabla, el bloqueo de actualización con intención exclusiva se adquirirá en el nivel de la tabla como consecuencia

Bloqueos de esquema (Sch) – El motor de base de datos de SQL Server reconoce dos tipos de bloqueos de esquema: Bloqueo de modificación de esquema (Sch-M) y Bloqueo de estabilidad de esquema (Sch-S)

• Un bloqueo de modificación de esquema (Sch-M) se adquirirá cuando se ejecute una sentencia DDL, e impedirá el acceso a los datos del objeto bloqueado mientras se modifica la estructura del objeto. SQL Server permite un único bloqueo de modificación de esquema (Sch-M) en cualquier objeto bloqueado. Para modificar una tabla, una transacción debe esperar a adquirir un bloqueo Sch-M en el objeto de destino. Una vez que adquiere el bloqueo de modificación de esquema (Sch-M), la transacción puede modificar el objeto y, una vez finalizada la modificación, se liberará el bloqueo. Un ejemplo típico del bloqueo Sch-M es una reconstrucción de índice, ya que una reconstrucción de índice es un proceso de modificación de tabla. Una vez que se emite el ID de reconstrucción de índice, se adquirirá un bloqueo de modificación de esquema (Sch-M) en esa tabla y se liberará sólo después de que se complete el proceso de reconstrucción de índice (cuando se utiliza con la opción ONLINE, la reconstrucción de índice adquirirá el bloqueo Sch-M poco después del final del proceso)
• Se adquirirá un bloqueo de estabilidad de esquema (Sch-S) mientras se compila y ejecuta una consulta dependiente de esquema y se genera el plan de ejecución. Este bloqueo particular no bloqueará otras transacciones para acceder a los datos del objeto y es compatible con todos los modos de bloqueo excepto con el bloqueo de modificación de esquema (Sch-M). Esencialmente, los bloqueos de estabilidad del esquema serán adquiridos por cada consulta DML y select para asegurar la integridad de la estructura de la tabla (asegurar que la tabla no cambia mientras se ejecutan las consultas).

Bloqueos de actualización masiva (BU) – este bloqueo está diseñado para ser utilizado por las operaciones de importación masiva cuando se emite con un argumento/indicación TABLOCK. Cuando se adquiere un bloqueo de actualización masiva, otros procesos no podrán acceder a una tabla durante la ejecución de la carga masiva. Sin embargo, un bloqueo de actualización masiva no impedirá que se procese otra carga masiva en paralelo. Pero tenga en cuenta que el uso de TABLOCK en una tabla de índices agrupados no permitirá la importación masiva en paralelo. Hay más detalles sobre esto en Directrices para optimizar la importación masiva

Jerarquía de bloqueos

SQL Server ha introducido la jerarquía de bloqueos que se aplica cuando se realiza la lectura o el cambio de datos. La jerarquía de bloqueo comienza con la base de datos en el nivel jerárquico más alto y desciende a través de la tabla y la página hasta la fila en el nivel más bajo

Esencialmente, siempre hay un bloqueo compartido en el nivel de la base de datos que se impone siempre que una transacción se conecta a una base de datos. El bloqueo compartido en el nivel de la base de datos se impone para evitar la caída de la base de datos o la restauración de una copia de seguridad de la base de datos sobre la base de datos en uso. Por ejemplo, cuando se emite una sentencia SELECT para leer algunos datos, se impondrá un bloqueo compartido (S) en el nivel de la base de datos, un bloqueo compartido de intención (IS) en la tabla y en el nivel de página, y un bloqueo compartido (S) en la propia fila

En el caso de una sentencia DML (es decir insertar, actualizar, eliminar) se impondrá un bloqueo compartido (S) en el nivel de la base de datos, un bloqueo exclusivo de intención (IX) o un bloqueo de actualización de intención (IU) en la tabla y en el nivel de la página, y un bloqueo exclusivo o de actualización (X o U) en la fila

Los bloqueos se adquirirán siempre de arriba a abajo, ya que de ese modo SQL Server está impidiendo que se produzca la llamada condición Race.

Ahora que se han explicado los modos de bloqueo y la jerarquía de bloqueos, vamos a profundizar en los modos de bloqueo y cómo se traducen en una jerarquía de bloqueos.

No todos los modos de bloqueo pueden aplicarse a todos los niveles.

A nivel de fila, se pueden aplicar los siguientes tres modos de bloqueo:

• Exclusivo (X)
• Compartido (S)
• Actualizar (U)

Para entender la compatibilidad de esos modos, consulte la siguiente tabla:

✓ – Compatible ✗ – Incompatible

A nivel de tabla, hay cinco tipos diferentes de bloqueos:

• Exclusivo (X)
• Compartido (S)
• Exclusivo con intención (IX)
• Compartido con intención (IS)
• Compartido con intención exclusiva (SIX)

La compatibilidad de estos modos puede verse en la siguiente tabla

✓ – Compatible ✗ – Incompatible

Un bloqueo de esquema (Sch) es también un bloqueo a nivel de tabla también, pero no es un bloqueo relacionado con los datos

Para entender mejor la compatibilidad entre estos tipos de bloqueo, consulte esta tabla:

Escalada de bloqueos

Para evitar una situación en la que el bloqueo esté utilizando demasiados recursos, SQL Server ha introducido la función de escalada de bloqueos.

Sin el escalamiento, los bloqueos podrían requerir una cantidad significativa de recursos de memoria. Tomemos un ejemplo en el que se debe imponer un bloqueo en las 30.000 filas de datos, donde cada fila tiene un tamaño de 500 bytes, para realizar la operación de borrado. Sin escalar, se impondrá un bloqueo compartido (S) en la base de datos, 1 bloqueo exclusivo de intención (IX) en la tabla, 1.875 bloqueos exclusivos de intención (IX) en las páginas (una página de 8KB contiene 16 filas de 500 bytes, lo que hace 1.875 páginas que contienen 30.000 filas) y 30.000 bloqueos exclusivos (X) en las propias filas. Como cada bloqueo tiene un tamaño de 96 bytes, 31.877 bloqueos ocuparán unos 3 MB de memoria para una sola operación de borrado. La ejecución de un gran número de operaciones en paralelo podría requerir algunos recursos significativos sólo para asegurar que el gestor de bloqueos pueda realizar la operación sin problemas

Para evitar tal situación, SQL Server utiliza la escalada de bloqueos. Esto significa que en una situación en la que se adquieren más de 5.000 bloqueos en un solo nivel, SQL Server escalará esos bloqueos a un único bloqueo de nivel de tabla. Por defecto, SQL Server siempre escalará al nivel de tabla directamente, lo que significa que la escalada al nivel de página nunca se produce. En lugar de adquirir numerosos bloqueos de filas y páginas, SQL Server escalará al bloqueo exclusivo (X) a nivel de tabla

Aunque esto reducirá la necesidad de recursos, los bloqueos exclusivos (X) en una tabla significan que ninguna otra transacción podrá acceder a la tabla bloqueada y todas las consultas que intenten acceder a esa tabla se bloquearán. Por lo tanto, esto reducirá la sobrecarga del sistema pero aumentará la probabilidad de contención de la concurrencia

Con el fin de proporcionar un control sobre la escalada, a partir de SQL Server 2008 R2, la opción LOCK_EXCALATION se introduce como parte de la sentencia ALTER TABLE

Cada una de estas opciones se define para permitir un control específico sobre el proceso de escalado de bloqueos:

Table – Esta es la opción por defecto para cualquier tabla recién creada, ya que por defecto SQL Server siempre ejecutará el escalado de bloqueos a nivel de tabla, lo que también incluye a las tablas particionadas

Auto – Esta opción permite el escalado de bloqueos a nivel de partición cuando una tabla está particionada. Cuando se adquieren 5.000 bloqueos en una sola partición, la escalada de bloqueos adquirirá un bloqueo exclusivo (X) en esa partición, mientras que la tabla adquirirá un intento de bloqueo exclusivo (IX). En caso de que esa tabla no esté particionada, el escalamiento de bloqueos adquirirá el bloqueo en el nivel de la tabla (igual a la opción Tabla).

Aunque parece una opción muy útil, hay que usarla con mucho cuidado ya que puede provocar fácilmente un deadlock. En una situación en la que tenemos dos transacciones en dos particiones en las que se adquiere el bloqueo exclusivo (X), y las transacciones intentan acceder a la fecha de la partición utilizada por otra transacción, se encontrará un deadlock

Así que, es muy importante controlar cuidadosamente el patrón de acceso a los datos, si esta opción está habilitada, lo cual no es fácil de lograr, y esta es la razón por la que esta opción no es la configuración por defecto en SQL Server

Deshabilitar – Esta opción deshabilitará completamente la escalada de bloqueo para una tabla. De nuevo, esta opción debe utilizarse con cuidado para evitar que el gestor de bloqueos de SQL Server se vea obligado a utilizar una cantidad excesiva de memoria

Como puede verse, el escalado de bloqueos podría ser un reto para los DBA. Si el diseño de la aplicación requiere borrar o actualizar más de 5.000 filas a la vez, una solución para evitar el escalado de bloqueos, y los efectos resultantes, es dividir la transacción única en dos o más transacciones en las que cada una maneje menos de 5.000 filas, ya que de esta manera se podría evadir el escalado de bloqueos

Obtener información sobre los bloqueos activos de SQL Server

SQL Server proporciona la vista de gestión de dinámicas (DMV) sys.dm_tran_locks que devuelve información sobre los recursos del gestor de bloqueos que están actualmente en uso, lo que significa que mostrará todos los bloqueos «vivos» adquiridos por las transacciones. Se pueden encontrar más detalles sobre este DMV en el artículo sys.dm_tran_locks (Transact-SQL).

La columna más importante utilizada para la identificación del bloqueo son resource_type, request_mode y resource_description. Si es necesario, se pueden incluir más columnas como recurso adicional de información durante la resolución de problemas

Aquí está el ejemplo de la consulta

La cláusula where en esta consulta se utiliza como el filtro en el resource_type para eliminar. de los resultados, aquellos bloqueos generalmente compartidos adquiridos en la base de datos ya que estos siempre están presentes a nivel de la base de datos

Una breve explicación de las tres columnas que se presentan aquí:

resource_type – Muestra un recurso de la base de datos donde se están adquiriendo los bloqueos. La columna puede mostrar uno de los siguientes valores: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID

request_mode – muestra el modo de bloqueo que se adquiere en el recurso

resource_description – muestra una breve descripción del recurso y no se rellena para todos los modos de bloqueo. La mayoría de las veces la columna contiene el id de la fila, página, objeto, archivo, etc

• Autor
• Postes recientes

Nikola es un friki de la informática desde 1981 y un entusiasta de SQL con intención de convertirse en un friki. Especializado en auditoría de SQL Server, cumplimiento de normativas y monitorización del rendimiento.
Devoto de la aviación militar y modelista de aviones a escala. Aficionado a los deportes extremos; paracaidista e instructor de puenting. Una vez serio, ahora sólo un fotógrafo de tiempo libre
Ver todos los mensajes de Nikola Dimitrijevic