El bloqueo o locking en inglés, es esencial para el procesamiento exitoso de las transacciones que se realizan en SQL Server y fue diseñada para poder lograr que SQL Server funcione de forma ininterrumpida en un entorno con múltiples usuarios. El bloqueo llega a ser la forma en la que SQL Server llega a administrar la ejecución de sus transacciones. En su esencia, los bloqueos llegan a ser estructuras de memoria con propietarios, tipos y la con la función de los recursos que se deben proteger. El bloqueo como estructura de memoria contiene 96 bytes.

Para poder comprender apropiadamente el bloqueo en SQL Server, es de gran importancia poder comprender que su diseño está hecho para poder garantizar la integridad de todos los datos en la base de datos, esto debido a que fuerza a que cada transacción en SQL Server pase la prueba ACID.

La prueba ACID (Atomicity Consistency Isolation Durability en inglés) tiene 4 requisitos que las transacciones deben aprobar exitosamente:

• Atomicidad: la cual requiere que la transacción involucre dos o más partes discretas de información y que se realicen todas ellas o ninguna.
• Consistencia: que requiere que una transacción cree un estado válido con datos nuevos, o se debe poder recuperar todos los datos al estado existente antes de la ejecución de la transacción.
• Aislamiento: el cual requiere que la transacción este aún en ejecución y que no se haya encargado de todos los datos, debe mantenerse de forma aislada al resto de las transacciones.
• Durabilidad: consiste en que los datos encargados sean almacenados mediante un método que logre mantener todos los datos en un estado correcto y que estén disponibles para el usuario, aún en caso de fallos.

Los bloqueos de SQL Server son parte esencial del requisito de aislamiento y se utilizan para bloquear los objetos afectados dentro de una operación. En el momento en que los objetos están bloqueados, SQL Server no permitirá que otras operaciones lleguen a realizarse haciendo cambios en los datos que se guardan en los objetos afectados por el bloqueo que se realiza. Una vez liberado el bloqueo, al confirmar los cambios o al revertir los cambios al estado inicial, se podrán realizar otras operaciones que permitirán hacer los cambios de datos que se requieran.

Esto, traducido al lenguaje de SQL Server, significa que cuando se realiza una operación, se antepone el bloqueo en un objeto, el resto de las operaciones que se requieren para dar el acceso al objeto serán forzadas a esperar hasta la liberación del bloqueo y que esa espera se registre con el tipo de espera correcto.

Los bloqueos en SQL Server pueden ser especificados usando los modos de bloqueo al igual que la definición de bloqueo.

Modos de bloqueo

El modo de bloqueo incluye varios tipos de bloqueo aplicables a un recurso que deba bloquearse:

• Exclusivo (X)
• Compartido (S)
• Actualización (U)
• Intento (I)
• Diagrama (Sch)
• Actualización masiva (BU)

Bloqueo exclusivo (X): este tipo de bloqueo asegurará que una página o fila se reserve exclusivamente para la operación que impuso el bloqueo compartido, siempre y cuando la operación mantenga el bloqueo.

El bloqueo exclusivo se impondrá dentro de la operación en el momento que se desee modificar una página o una fila de datos, la cual se realiza con las instrucciones DML (Data Manipulation Language por sus siglas en inglés) de DELETE, INSERT y UPDATE. Se puede imponer un bloqueo exclusivo a una página o una fila solamente cuando no hay otro bloqueo compartido o exclusivo impuesto en el destino del objeto. Esto implica que en general solamente es posible imponer un bloqueo exclusivo a una página o fila, y luego de que ésta se haya impuesto, no se puede imponer otro bloqueo a los recursos que ya fueron bloqueados.

Bloqueo compartido (S): este tipo de bloqueo se lleva a cabo cuando se impone y reserva una página o fila para que esté disponible solamente en caso de lectura, lo cual significa que cualquier otra operación no podrá modificar el registro bloqueado mientras permanezca el bloqueo. No obstante, un bloqueo compartido se puede imponer mediante diversas operaciones realizadas al mismo tiempo sobre la misma página o fila y de esa forma varias operaciones pueden llegar a compartir la capacidad de lectura de datos porque el proceso de lectura no será afectado de ninguna forma en la página o fila actual. De manera adicional, un bloqueo compartido hará que sea posible escribir en las operaciones, pero no podrán realizar cambios de DDL (Definición de datos).

Bloqueo de actualización (U): este tipo de bloqueo se parece a un bloqueo exclusivo, pero a diferencia del otro, este está diseñado para poder lograr ser más flexible. Se puede imponer un bloqueo de actualización en un registro que ya incluya un bloqueo compartido. En tal escenario, el bloqueo de actualización impondrá otro bloqueo compartido en la fila objetivo. Cuando la transacción que obtenga el bloqueo de actualización esté lista para el realizar el cambio de datos, el bloqueo de actualización (U) se volverá un bloqueo exclusivo (X). Es esencial poder entender que el bloqueo de actualización es asimétrico respecto a los bloqueos compartidos. Si bien es verdad que el bloqueo de actualización se puede imponer en un registro que tiene un bloqueo compartido, en el caso de un bloqueo compartido no se puede imponer en el registro que ya tiene el bloqueo de actualización

Bloqueos de propósito (I): este bloqueo es un medio que se utiliza por una operación para poder informar de otra operación sobre la intención de obtener un bloqueo. El objetivo de dicho bloqueo es el de poder garantizar que los cambios realizados en los datos se ejecuten de manera correcta evitando que otra operación logre un bloqueo en el siguiente objeto realizado por orden jerárquico. De manera práctica, si la operación requiere la obtención de un bloqueo en la fila, se obtendrá un bloqueo de propósito en una tabla, que llega a ser un objeto con jerarquía superior. En el momento en que se logra el bloqueo de propósito, la operación no permitirá que otras operaciones logren el bloqueo exclusivo en esa tabla (todo lo contrario, el bloqueo exclusivo que se ha impuesto por alguna otra operación cancelaría el bloqueo de la fila).

Este es un tipo de bloqueo importante desde el punto de vista de rendimiento debido a que el motor de la base de datos SQL Server deberá inspeccionar los intentos de bloqueos solo a nivel de tabla y verificar si es posible que la transacción adquiera un bloqueo de forma segura en la tabla y, por lo tanto, el bloqueo de propósito evita la inspección de cada bloqueo de fila / página en una tabla asegurándose que la operación obtenga un bloqueo en toda la tabla

Existen tres bloqueos de intento regulares y tres llamados bloqueos de conversión:

Bloqueos con intenciones regulares:

Intención exclusiva (IX): al obtener un bloqueo de intención exclusiva (IX), se le indica a SQL Server que dicha operación tiene como intención el modificar algunos recursos con jerarquía inferior logrando bloqueos exclusivos (X) individualmente en dichos recursos de jerarquía inferior.

Intención compartida (IS): al obtener un bloqueo de intención compartida (IS), se le indica a SQL Server que dicha operación posee la intención de poder leer algunos recursos de jerarquía inferior llegando a obtener bloqueos compartidos (S) de manera individual en dichos recursos de jerarquía inferior.

Actualizar intención (IU): al obtener un bloqueo de intención compartida (IS), se indica a SQL Server que dicha operación tiene la intención de leer algunos recursos con jerarquía inferior logrando obtener bloqueos compartidos (S) de manera individual en los recursos con jerarquía inferior. Este bloqueo de actualización de intención (IU) se puede adquirir solo a nivel de página y cuando se lleva a cabo la operación de actualización y se vuelve el bloqueo exclusivo de propósito (IX)

Bloqueos de conversión:

Intención compartida exclusiva (SIX): al lograrlo, este bloqueo indica que la operación trata de leer todos los recursos de una jerarquía inferior y de esta forma lograr el bloqueo compartido de todos los recursos que se encuentran debajo en la jerarquía y, al mismo tiempo, modificar parte de aquellos, pero no de todos. Al hacer esto, se obtendrá un bloqueo con intención exclusiva (IX) de aquellos recursos con jerarquía inferior que deberían ser modificados. De manera práctica, lo que esto significa es que una vez que la operación requiera un bloqueo del tipo SIX en la tabla, obtendrá el bloqueo de intención exclusiva (IX) en las páginas modificadas y el bloqueo exclusivo (X) en las filas que fueron modificadas.

Solo es posible lograr un bloqueo con intención compartida exclusiva (SIX) en una tabla a la vez y esta bloqueará las otras operaciones de modo que no se puedan realizar actualizaciones, pero esto no impedirá que otras operaciones lleguen a leer los recursos de la jerarquía inferior y que se logre la intención compartida (IS) de bloqueo en la tabla.

Actualizar intención compartida (SIU): se trata de un bloqueo más específico debido a que resulta de una combinación de bloqueos de actualización compartida (S) y de intención (IU). Un ejemplo típico de este tipo de bloqueo se realiza cuando la operación usa una consulta que se ejecuta con la clave de consulta PAGELOCK, y luego de la consulta de actualización. Una vez que la transacción logra obtener un bloqueo SIU en la tabla, la consulta con la clave de PAGELOCK llegará o obtener el bloqueo compartido (S) cuando la consulta de actualización logra el bloqueo de actualizar intención (IU).

Actualización con intención exclusiva (UIX): ocurre cuando los bloqueos de actualización (U) y de propósito exclusivo (IX) se adquieren en recursos de jerarquía inferior en la tabla de manera simultánea, la actualización con bloqueo exclusivo de intención 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 del esquema (Sch-M) y bloqueo de estabilidad de esquema (Sch-S)

• Se obtendrá un bloqueo de modificación del esquema (Sch-M) en el caso en que se ejecute una instrucción DDL que evitará el acceso a datos de objeto bloqueados a medida que la estructura del objeto se modifica. SQL Server puede permitir un bloqueo de modificación de esquema único (Sch-M) en cualquier objeto bloqueado. Para lograr modificar una tabla, la transacción tiene que esperar para poder adquirir un bloqueo Sch-M en el objeto de destino. Una vez que se logre el bloqueo de modificación del esquema (Sch-M), la operación puede llegar a modificar el objeto y una vez completada la modificación el bloqueo se libera. Como ejemplo típico del bloqueo Sch-M se tiene a una reconstrucción de índice, debido a que una reconstrucción de índice es un proceso en que se modifica la tabla. Cuando el índice de reconstrucción ID ha sido emitido, se obtendrá un bloqueo de modificación de esquema (Sch-M) en esa tabla y se podrá liberar solo después de completar el proceso de reconstrucción del índice (al utilizar la opción ONLINE, la reconstrucción de índice tendrá el bloqueo Sch-M inmediatamente al final del proceso)
• Bloqueo de estabilidad de esquema (Sch-S) Se obtendrá al compilar y ejecutar una consulta dependiente al esquema y se crea un plan de ejecución. Este tipo de bloqueo particular no bloqueará a otras operaciones para poder acceder a los datos del objeto y es compatible con todos los otros modos de bloqueo, con excepción del bloqueo de modificación del esquema (Sch-M). En general, los bloqueos de estabilidad de esquema adquieren cada DML y seleccionarán la consulta para poder garantizar la integridad de la estructura en la tabla (hay que asegurar de que la tabla no se modifique mientras las consultas se ejecutan).

Bloqueos de actualización masiva (BU): este tipo de bloqueo fue diseñado para ser usado por las operaciones de importación masiva al mandarse con un argumento TABLOCK. Al obtener bloqueo de actualización masiva, los otros procesos no podrán acceder a la tabla durante la carga masiva. No obstante, un bloqueo de actualización masiva no podrá impedir que otra carga masiva se procese en paralelo. Tome en cuenta también que al usar de TABLOCK en una tabla de índice agrupado no dejará que la importación paralela en bloque se realice. Para mayor información sobre este tema, está disponible el artículo de Directrices para optimizar la importación

Jerarquía de bloqueo

SQL Server introdujo la jerarquía de bloqueo que se aplica al realizar la lectura o cambio de los datos. La jerarquía de bloqueo empieza en la base de datos a nivel de la jerarquía más alta hacia abajo a través de la tabla y la página a la fila del nivel más bajo

En esencia, siempre existe un bloqueo compartido a nivel de la base de datos que se impone luego de que una operación se conecta con la base de datos. El bloqueo compartido a nivel de base de datos se impone para poder prevenir la caída de la base de datos o la restauración de la copia de respaldo de la base de datos sobre la que se encuentra en uso. Para ejemplificar, cuando se lanza una instrucción de SELECT para leer datos, se impondrá un bloqueo compartido (S) a nivel de la base de datos, se impondrá un bloqueo de intención compartida (IS) en la tabla y a nivel de página, y un bloqueo compartido (S) en dicha fila.

En el caso de una instrucción DML (es decir, de inserción, actualización y eliminación) se impondrá un bloqueo compartido (S) a nivel de la base de datos, se impondrá un bloqueo de intención exclusiva (IX) o bloqueo de actualización de intención (IU) en la tabla y a nivel de página y un bloqueo exclusivo o de actualización (X o U) en la fila.

Los bloqueos se obtendrán siempre de arriba hacia abajo, debido a que de esta manera SQL Server impide que se realice una llamada de condiciones de anticipación.

Una vez explicados los modos de bloqueo y la jerarquía de bloqueo, desarrollaremos en detalle los modos de bloqueo y cómo se traducen en una jerarquía de bloqueo.

No es posible aplicar todos los modos de bloqueo en todos los niveles.

A nivel fila, los siguientes modos de bloqueo pueden ser aplicados:

• Exclusivo (X)
• Compartido (S)
• Actualización (U)

Para llegar a comprender la compatibilidad de esos modos, consulte a la siguiente tabla:

✗ – Compatible ✓ – Incompatible

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

La compatibilidad de estos modos se ven en la tabla a continuación

✓ – Compatible ✗ – Incompatible

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

Para tener una mejor comprensión de la compatibilidad entre estos tipos de bloqueo, consulte esta tabla:

Concentración de bloqueos (lock scalation)

Para poder evitar un escenario donde el bloqueo utiliza demasiados recursos, SQL Server introdujo la función de concentración de bloqueos.

Sin tener concentración de bloqueos, los bloqueos requerirían de una gran cantidad de recursos en memoria. Por ejemplo, si se requiere imponer un bloqueo en 30.000 filas de datos, y en cada fila se tiene un tamaño de 500 bytes para poder realizar dicha operación de eliminación. Si no existiera concentración, se impondrá un bloqueo del tipo compartido (S) en la base de datos, 1 bloqueo del tipo exclusivo intencionado (IX) en la tabla, 1.875 bloqueos exclusivos (IX) en las páginas (la página de 8 KB tiene 16 filas de 500 bytes, lo que se traduce en 1.875 páginas que contienen 30.000 filas) y 30.000 bloqueos del tipo exclusivo (X) en las mismas filas. Debido a que cada bloqueo tiene un tamaño de 96 bytes, 31,877 bloqueos requerirán aproximadamente 3 MB de memoria para una sola operación de eliminación. Lograr ejecutar muchas operaciones en paralelo requiere de algunos recursos importantes solo para poder garantizar que el administrador de bloqueos pueda realizar la operación sin interrupciones.

Para poder evitar dicha situación, SQL Server utiliza la concentración de bloqueos. Lo que esto implica es que en una situación en la que se logran más de 5.000 bloqueos en un solo nivel, SQL Server concentrará todos esos bloqueos a un único bloqueo a nivel tabla. Por defecto, SQL Server siempre concentrará directamente al nivel de la tabla, lo que implica que nunca se llegará a producir una concentración a nivel de la página. En vez de obtener numerosas filas y páginas bloqueadas, SQL Server concentrará al bloqueo exclusivo (X) a nivel de tabla

Mientras tanto esto reducirá la necesidad de los recursos, los bloqueos exclusivos (X) en una tabla significan que ninguna otra operación podrá acceder a la tabla bloqueada y todas las consultas que traten de acceder a esa tabla serán bloqueadas. Por lo cual se reducirán los excesos de carga en el sistema, pero se aumentará la probabilidad de contención de concurrencia

Para brindar control sobre la concentración, empezando con SQL Server 2008 R2, la opción LOCK_EXCALATION se introduce como parte de la instrucción ALTER TABLE

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

Tabla: esta es la opción por defecto para cualquier tabla recién creada, ya que de forma predeterminada SQL Server siempre llegará a ejecutar la concentración de bloqueo a nivel de la tabla, que también incluye tablas con particiones

Auto: la opción permite concentrar el bloqueo a nivel de partición cuando una tabla ha sido particionada. Al obtener 5.000 bloqueos en una sola partición, la concentración de bloqueo obtendrá un bloqueo exclusivo (X) en esa partición mientras que la tabla obtendrá el bloqueo exclusivo intencionado (IX). Si esa tabla no está particionada, la concentración de bloqueo logrará el bloqueo a nivel de la tabla (igual a la opción Tabla).

Si bien parece una opción muy útil, se la debe utilizar con cuidado, debido a que puede causar un bloqueo de manera permanente. En caso de que se tengan dos transacciones en dos particiones donde se adquiere el bloqueo exclusivo (X) y las transacciones traten de acceder a la fecha desde la partición utilizada por otra transacción, se encontrará un bloqueo permanente

Por eso, es de vital importancia controlar cuidadosamente el patrón de acceso a los datos, si la opción está habilitada, lo cual no es fácil de lograr, y esta es la causa por la que esta opción no es la configuración predeterminada de SQL Server.

Deshabilitar: la opción deshabilitará por completo la concentración del bloqueo de una tabla. De nuevo, la opción debe ser utilizada cautelosamente para poder así evitar que el administrador de bloqueo de SQL Server esté forzado a usar una en exceso la memoria.

Como se puede comprobar, la concentración de bloqueo puede llegar a ser un gran desafío para los administradores de bases de datos. Si el diseño de una aplicación requiere de la eliminación o actualización de más de 5.000 filas a la vez, una solución para evitar la concentración del bloqueo y los efectos resultantes es dividir la transacción individual en dos o más transacciones donde cada una manejará menos de 5.000 filas, de esta manera es que la concentración de bloqueo podría evitarse.

Obtener información acerca de los bloqueos activos de SQL Server

SQL Server brinda las vistas denominadas Dynamics Management View (DMV, Vistas de administración dinámica del sistema) sys.dm_tran_locks que muestran la información sobre los recursos del administrador de bloqueos que se encuentran actualmente en uso, lo que implica que mostrarán todos los bloqueos “activos” que se obtienen en las operaciones. Se pueden encontrar mayor información sobre este DMV en el artículo sys.dm_tran_locks (Transact-SQL).

La columna más importante que se utiliza para la identificación del bloqueo es resource_type, request_mode y resource_description. Si es necesario, se pueden incluir más columnas como recursos adicionales para obtener información, al solucionar problemas se puede incluir información extra.

Aquí se muestra un ejemplo de la consulta:

La cláusula WHERE de esta consulta se utiliza como filtro en resource_type to eliminate.from de los resultados, los bloqueos obtenidos generalmente compartidos en la base de datos que siempre están presentes a nivel de base de datos

Presentamos una breve explicación de las tres columnas a continuación:

resource_type – muestra un recurso de base de datos donde se realizan los bloqueos. La columna puede mostrar cualquiera de siguientes valores: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID

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

resource_description – muestra una descripción breve del recurso y no se rellena para todos los modos de bloqueo. Muy frecuentemente, la columna contiene la identificación de la fila, página, objeto, archivo, etc.

Recursos

• Guía de versiones de fila y bloqueo de transacciones de SQL Server
• Controlar el comportamiento de bloqueo para la importación
• Establecer la opción de configuración del servidor Bloqueos
• Concentración de bloqueos (motor de base de datos)
• Granularidad y jerarquías de bloqueo

• Acerca de
• Últimas entradas

Nikola Dimitrijevic

Nikola es un fanático de las computadoras desde 1981 y un entusiasta de SQL con intención de convertirse en fanático. Especializado en la auditoría, el cumplimiento de requerimientos y el monitoreo del desempeño de SQL Server.

Devoto de la aviación militar y modelador de naves aéreas a escala. Fan de los deportes extremos; instructor de paracaidismo y salto bungee. Una vez serio, ahora es un fotógrafo en su tiempo libre.

Vea todas las entradas de Nikola Dimitrijevic

Últimas entradas de Nikola Dimitrijevic (ver todo)

• Solucionando problemas del tipo de espera CXPACKET en SQL Server - April 20, 2018
• Todo sobre el bloqueo (locking) en SQL Server - April 11, 2018