Bloqueo optimizado

Se aplica a: Base de datos de Azure SQL

En este artículo se presenta la característica de bloqueo optimizado, una nueva capacidad del motor de base de datos que ofrece un mecanismo mejorado de bloqueo de transacciones que reduce el consumo de memoria de bloqueo y el bloqueo de transacciones simultáneas.

¿Qué es el bloqueo optimizado?

El bloqueo optimizado ayuda a reducir la memoria de bloqueo, ya que se mantienen muy pocos bloqueos incluso para transacciones grandes. Además, el bloqueo optimizado también evita escalaciones de bloqueo. Esto permite un acceso más simultáneo a la tabla.

El bloqueo optimizado se compone de dos componentes principales: bloqueo de identificador de transacción (TID) y bloqueo después de la calificación (LAQ).

• Un identificador de transacción (TID) es un identificador único de una transacción. Cada fila se etiqueta con el último TID que lo modificó. En lugar de tener potencialmente muchos bloqueos de identificador de clave o fila, se usa un único bloqueo en el TID. Para obtener más información, consulte Bloqueo del identificador de transacción (TID).
• El bloqueo después de la calificación (LAQ) es una optimización que evalúa predicados de consultas mediante la última versión confirmada de la fila sin adquirir un bloqueo, lo que mejora la simultaneidad. Para obtener más información, consulte Bloqueo después de la calificación (LAQ).

Por ejemplo:

• Sin bloqueo optimizado, la actualización de mil filas en una tabla podría requerir mil bloqueos de fila exclusivos (X) mantenidos hasta el final de la transacción.
• Con el bloqueo optimizado, la actualización de mil filas en una tabla podría requerir mil de bloqueos de fila X, pero cada bloqueo se libera en cuanto se actualiza cada fila y solo se mantiene un bloqueo de TID hasta el final de la transacción. Dado que los bloqueos se liberan rápidamente, se reduce el uso de memoria de bloqueo y es mucho menos probable que se produzca la extensión de bloqueo, lo que mejora la simultaneidad de la carga de trabajo.

Disponibilidad

El bloqueo optimizado solo está disponible en la base de datos de Azure SQL, en todos los niveles de servicio y tamaños de proceso.

Actualmente, el bloqueo optimizado no está disponible en Azure SQL Managed Instance ni en SQL Server.

¿Está habilitado el bloqueo optimizado?

El bloqueo optimizado está habilitado por base de datos de usuario. Conéctese a la base de datos y, a continuación, use la siguiente consulta para comprobar si el bloqueo optimizado está habilitado:

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

El bloqueo optimizado se basa en otras características de base de datos:

• El bloqueo optimizado requiere que se habilite la recuperación acelerada de bases de datos (ADR) en la base de datos.
• Para obtener la mayor ventaja del bloqueo optimizado, se debe habilitar el aislamiento de instantánea de lectura confirmada (RCSI) para la base de datos. El componente del LAQ del bloqueo optimizado solo está en vigencia si RCSI está habilitado.

Tanto ADR como RCSI están habilitados de forma predeterminada en Azure SQL Database. Para verificar que estas opciones están habilitadas para la base de datos actual, conéctese a la base de datos y ejecute la siguiente consulta T-SQL:

SELECT name,
       is_read_committed_snapshot_on,
       is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = DB_NAME();

Introducción al bloqueo

Este es un breve resumen del comportamiento cuando el bloqueo optimizado no está habilitado. Para obtener más información, revise la guía de control de versiones de fila y bloqueo de transacciones.

En el motor de base de datos, el bloqueo es un mecanismo que evita que varias transacciones actualicen simultáneamente los mismos datos, con el fin de garantizar las propiedades de ACID de las transacciones.

Cuando una transacción debe modificar los datos, solicita un bloqueo en los datos. Se concede el bloqueo si no se mantienen otros bloqueos conflictivos en los datos y la transacción puede continuar con la modificación. Si se mantiene otro bloqueo en conflicto en los datos, la transacción debe esperar a que se libere el bloqueo para poder continuar.

Cuando varias transacciones intenta acceder simultáneamente a los mismos datos, el motor de base de datos debe resolver conflictos potencialmente complejos con lecturas y escrituras simultáneas. El bloqueo es uno de los mecanismos por los que el motor puede proporcionar la semántica para los niveles de aislamiento de transacción de SQL de ANSI. Aunque el bloqueo en las bases de datos es esencial, la reducción de la simultaneidad, los interbloqueos, la complejidad y la sobrecarga de bloqueo pueden afectar al rendimiento y la escalabilidad.

Bloqueo optimizado e identificador de transacción (TID)

Cuando el control de versiones de fila basado en niveles de aislamiento está en uso o cuando el ADR está habilitado, cada fila de la base de datos contiene internamente un identificador de transacción (TID). Este TID se conserva en el disco. Cada transacción que modifique una fila marcará esa fila con su TID.

Con el bloqueo de TID, en lugar de tomar el bloqueo en la clave de la fila, se toma un bloqueo en el TID de la fila. La transacción de modificación contendrá un bloqueo X en su TID. Otras transacciones adquieren un bloqueo S en el TID para esperar hasta que se complete la primera transacción. Con el bloqueo de TID, se siguen haciendo bloqueos de página y fila durante las modificaciones, pero cada bloqueo de página y fila se libera en cuanto se actualiza cada fila. El único bloqueo que se mantiene hasta el final de la transacción es el bloqueo X único en el recurso de TID, que reemplaza varios bloqueos de página y fila (clave).

Considere el ejemplo siguiente que muestra los bloqueos para la sesión actual mientras una transacción de escritura está activa:

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRANSACTION;

UPDATE t0
SET b = b + 10;

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
      AND
      resource_type IN ('PAGE','RID','KEY','XACT');

COMMIT TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

Si el bloqueo optimizado está habilitado, la solicitud solo contiene un único bloqueo X en el recurso (transacción) XACT.

Si el bloqueo optimizado no está habilitado, la misma solicitud contiene cuatro bloqueos: tres bloqueos de clave X en cada fila y un bloqueo (exclusivo de intención) IX en la página que contiene las filas:

La vista de administración dinámica (DMV) sys.dm_tran_locks es útil para examinar o solucionar problemas de bloqueo, como la observación del bloqueo optimizado en acción.

Bloqueo optimizado y bloqueo después de la calificación (LAQ)

Basado en la infraestructura de TID, el bloqueo optimizado cambia la forma en que las instrucciones DML, como INSERT, UPDATE, DELETE y MERGE adquieren bloqueos.

Sin bloqueo optimizado, los predicados de consultas se comprueban por fila en un examen y se hace primero un bloqueo de fila de actualización (U). Si se cumple el predicado, se toma un bloqueo de fila (X) exclusivo antes de actualizar la fila y se mantiene hasta el final de la transacción.

Con el bloqueo optimizado, y cuando se habilita el nivel de aislamiento de instantánea (RCSI) READ COMMITTED, los predicados se comprueban en la última versión confirmada de la fila sin tener que hacer bloqueos de fila. Si el predicado no cumple, la consulta se mueve a la siguiente fila del examen. Si se cumple el predicado, se toma un bloqueo de fila X para actualizar la fila. El bloqueo de fila X se libera en cuanto se completa la actualización de fila, antes del final de la transacción.

Dado que la evaluación del predicado se realiza sin adquirir bloqueos, las consultas simultáneas que modifican filas diferentes no se bloquean entre sí.

Por ejemplo:

CREATE TABLE t1
(
a int NOT NULL,
b int NULL
);

INSERT INTO t1
VALUES (1,10),(2,20),(3,30);
GO

Sin el bloqueo optimizado, la sesión 2 se bloquea porque la sesión 1 contiene un bloqueo U en la fila, la sesión 2 debe actualizarse. Sin embargo, con el bloqueo optimizado, la sesión 2 no se bloquea porque no se hacen los bloqueos U y porque en la versión confirmada más reciente de la fila 1, columna a es igual a 1, que no satisface el predicado de la sesión 2.

Dado que no se hacen bloqueos U LAQ, una transacción simultánea podría modificar la fila después de la evaluación el predicado. Si se cumple el predicado y no hay ninguna otra transacción activa en la fila (sin bloqueo de TID X), se modifica la fila. Si hay una transacción activa, el Motor de base de datos espera a que se complete y vuelva a evaluar el predicado en el momento de la modificación porque la otra transacción podría haber modificado la fila. Si el predicado aún se cumple, se modifica la fila.

Considere el ejemplo siguiente en el que se reintenta automáticamente la evaluación del predicado porque otra transacción ha cambiado la fila:

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO

Cambios de comportamiento de consulta con bloqueo optimizado y RCSI

Las cargas de trabajo simultáneas en el aislamiento de instantánea confirmada de lectura (RCSI) que dependen del orden de ejecución estricto de las transacciones podrían experimentar diferencias en el comportamiento de las consultas cuando se habilita el bloqueo optimizado.

Considere el ejemplo siguiente en el que la transacción T2 actualiza la tabla t4 en función de la columna b que se actualizó durante la transacción T1.

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

Vamos a evaluar el resultado del escenario anterior con y sin bloqueo después de la calificación (LAQ).

Sin LAQ

Sin LAQ, la instrucción UPDATE en la transacción T2 se bloquea y esperará a que se complete la transacción T1. Una vez que se completa la transacción T1, la transacción T2 actualiza la columna de configuración de fila b a 3 porque se cumple su predicado.

Después de confirmar ambas transacciones, la tabla t4 contiene las filas siguientes:

 a | b
 1 | 3

Con LAQ

Con LAQ, la transacción T2 usa la última versión confirmada de la fila donde la columna b es igual a 1 para evaluar su predicado (b = 2). La fila no cumple los requisitos; por lo tanto, se omite y la instrucción se completa sin que la haya bloqueado la transacción T1. En este ejemplo, LAQ quita el bloqueo, pero conduce a resultados diferentes.

Después de confirmar ambas transacciones, la tabla t4 contiene las filas siguientes:

 a | b
 1 | 2

Adiciones de diagnóstico para el bloqueo optimizado

Las siguientes mejoras ayudan a supervisar y solucionar los problemas de bloqueo y interbloqueos cuando se habilita el bloqueo optimizado:

• Tipos de espera para el bloqueo optimizado
  • Los tipos de espera XACT para el bloqueo S en el TID y las descripciones de recursos en sys.dm_os_wait_stats (Transact-SQL):
    • LCK_M_S_XACT_READ: se produce cuando una tarea está esperando un bloqueo compartido en un tipo XACT wait_resource, con una intención de leer.
    • LCK_M_S_XACT_MODIFY: se produce cuando una tarea está esperando un bloqueo compartido en un tipo XACT wait_resource, con una intención de modificar.
    • LCK_M_S_XACT: se produce cuando una tarea está esperando un bloqueo compartido en un tipo XACT wait_resource, donde no se puede deducir la intención. Esto no es habitual.
• Visibilidad de los recursos de bloqueo
  • Recursos de bloqueo de XACT. Para obtener más información, consulta resource_description en sys.dm_tran_locks (Transact-SQL).
• Visibilidad de los recursos de espera
  • Recursos de espera de XACT. Para más información, consulta wait_resource en sys.dm_exec_requests (Transact-SQL).
• Gráfico de interbloqueo
  • En cada recurso <resource-list> del informe de interbloqueo , cada elemento <xactlock> notifica los recursos subyacentes e información específica para los bloqueos de cada miembro de un interbloqueo. Para obtener más información y un ejemplo, consulta Bloqueos optimizados e interbloqueos.

Procedimientos recomendados con bloqueo optimizado

Habilitación del aislamiento de instantánea de lectura confirmada (RCSI)

Para maximizar las ventajas del bloqueo optimizado, se recomienda habilitar el aislamiento de instantánea confirmada de lectura (RCSI) en la base de datos y utilizar el aislamiento READ COMMITTED como nivel de aislamiento predeterminado. Si aún no está habilitado, habilite el RCSI mediante la conexión a la base de datos master y ejecute la instrucción siguiente:

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

En la base de datos de Azure SQL, el RCSI está habilitado de manera predeterminada y READ COMMITTED es el nivel de aislamiento predeterminado. Con el RCSI habilitado y cuando se usa el nivel de aislamiento READ COMMITTED, los lectores leen una versión de la fila de la instantánea tomada al inicio de la instrucción. Con LAQ, los escritores califican las filas por predicado en función de la versión confirmada más reciente de la fila y sin adquirir bloqueos U. Asimismo, con LAQ una consulta espera solo si la fila califica y hay una transacción de escritura activa en esa fila. Calificar según la versión confirmada más reciente y bloquear solo las filas calificadas reduce el bloqueo y aumenta la simultaneidad.

Además de un bloqueo reducido, se reduce la memoria de bloqueo necesaria. Esto se debe a que los lectores no hacen bloqueos y los escritores solo hacen bloqueos de corta duración, en lugar de bloqueos que se mantienen hasta el final de la transacción. Cuando se usan niveles de aislamiento más estrictos como REPEATABLE READ o SERIALIZABLE, el motor de base de datos mantiene los bloqueos de fila y página hasta el final de la transacción, incluso con el bloqueo optimizado para los lectores y escritores, lo que da lugar a un aumento del bloqueo y del uso de memoria de bloqueo.

Evitar sugerencias de bloqueo

Aunque las sugerencias de tabla y consulta como UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK, etc. se respetan cuando el bloqueo optimizado está habilitado, reducen la ventaja del bloqueo optimizado. Las sugerencias de bloqueo obligan al motor de base de datos a hacer bloqueos de fila o página y mantenerlos hasta el final de la transacción para respetar la intención de las sugerencias de bloqueo. Algunas aplicaciones tienen lógica en la que se necesitan sugerencias de bloqueo, por ejemplo, al leer una fila con la sugerencia UPDLOCK y, luego, actualizarla. Se recomienda usar sugerencias de bloqueo solo cuando sea necesario.

Con el bloqueo optimizado, no hay restricciones en las consultas existentes y no hay necesidad de reescribir las consultas. Las consultas que no utilizan sugerencias se benefician de los bloqueos optimizados principalmente.

Una sugerencia de tabla en una tabla de una consulta no deshabilita el bloqueo optimizado para otras tablas de la misma consulta. Además, el bloqueo optimizado solo afecta al comportamiento de bloqueo de las tablas que actualiza una instrucción DML, como INSERT, UPDATE, DELETE o MERGE. Por ejemplo:

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

INSERT INTO t5 VALUES (1,10),(2,20),(3,30);
INSERT INTO t6 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

En el ejemplo de consulta anterior, solo la tabla t6 se ve afectada por la sugerencia de bloqueo, mientras que t5 todavía puede beneficiarse del bloqueo optimizado.

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

En el ejemplo de consulta anterior, solo la tabla t5 utiliza el nivel de aislamiento REPEATABLE READ mantiene los bloqueos hasta el final de la transacción. Otras actualizaciones de t5 pueden seguir beneficiándose del bloqueo optimizado. Lo mismo se aplica a la sugerencia HOLDLOCK.

Preguntas más frecuentes

¿Está optimizado el bloqueo de forma predeterminada en bases de datos nuevas y existentes?

En Azure SQL Database, sí.

¿Cómo puedo detectar si el bloqueo optimizado está habilitado?

Consulte ¿Está habilitado el bloqueo optimizado?

¿Qué ocurre cuando la recuperación acelerada de bases de datos (ADR) no está habilitada en mi base de datos?

Si ADR está deshabilitado, el bloqueo optimizado también se deshabilita automáticamente.

¿Qué ocurre si quiero forzar que las consultas se bloqueen a pesar del bloqueo optimizado?

Para los clientes que usan RCSI, para forzar el bloqueo entre dos consultas cuando se habilita el bloqueo optimizado, use la sugerencia de consulta READCOMMITTEDLOCK.

¿Se utiliza el bloqueo optimizado en las réplicas secundarias de solo lectura?

No, dado que las instrucciones DML no se pueden ejecutar en réplicas de solo lectura y no se hacen los bloqueos de fila y página correspondientes.

¿Se utiliza el bloqueo optimizado al modificar datos en tempdb y en tablas temporales?

De momento, no.

Contenido relacionado

• Guía de versiones de fila y bloqueo de transacciones
• Aislamiento de instantánea de lectura confirmada (RCSI)
• sys.dm_tran_locks (Transact-SQL)
• Recuperación acelerada de bases de datos en Azure SQL
• Recuperación acelerada de bases de datos