Compartir vía

Bloqueo optimizado

  • Artículo

Se aplica a:Azure SQL DatabaseBase de datos SQL de Microsoft Fabric

En este artículo se presenta el bloqueo optimizado, una funcionalidad del motor de base de datos que ofrece un mecanismo mejorado de bloqueo de transacciones para reducir 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 1000 filas en una tabla podría requerir 1000 bloqueos de fila exclusivos (X) hasta el final de la transacción.
  • Con el bloqueo optimizado, la actualización de 1000 filas en una tabla podría requerir 1000 X bloqueos de fila, pero cada bloqueo se libera en cuanto se actualiza cada fila y solo se mantiene un bloqueo 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.

Nota:

Habilitar el bloqueo optimizado reduce o elimina los bloqueos de fila y página adquiridos por las instrucciones del lenguaje de modificación de datos (DML), como INSERT, UPDATE, DELETE, MERGE. No afecta a ningún otro tipo de bloqueos de base de datos y objetos, como bloqueos de esquema.

Disponibilidad

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

El bloqueo optimizado no está disponible actualmente 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');
Resultado Descripción
0 El bloqueo optimizado está deshabilitado.
1 El bloqueo optimizado está habilitado.
NULL El bloqueo optimizado no está disponible.

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

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 impide que varias transacciones actualicen simultáneamente los mismos datos para garantizar las propiedades 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 intentan 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 del 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.

Captura de pantalla del conjunto de resultados de una consulta en sys.dm_tran_locks para una sola sesión muestra solo un bloqueo cuando el bloqueo optimizado está habilitado.

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

Captura de pantalla del conjunto de resultados de una consulta en sys.dm_tran_locks para una sola sesión muestra tres bloqueos cuando el bloqueo optimizado no está habilitado.

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 después de la calificación (LAQ)

Basándose en la infraestructura de TID, el bloqueo optimizado cambia la forma en que las instrucciones DML, como INSERT, UPDATEy DELETE 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 bloqueos optimizados, y cuando el READ COMMITTED nivel de aislamiento de instantáneas (RCSI) está habilitado, los predicados se pueden verificar de forma optimista en la versión confirmada más reciente de la fila sin tomar ningún bloqueo. Si el predicado no se cumple, la consulta pasa a la siguiente fila en el análisis. Si se cumple el predicado, se toma un bloqueo de fila X para actualizar la fila.

Es decir, el bloqueo se toma después de la calificación de la fila que se va a modificar. 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 ningún bloqueo, 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
Sesión 1 Sesión 2
BEGIN TRANSACTION;
UPDATE t1
SET b = b + 10
WHERE a = 1;
BEGIN TRANSACTION;
UPDATE t1
SET b = b + 10
WHERE a = 2;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

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 está bloqueada porque no se generan bloqueos U y porque en la versión confirmada más reciente de la fila 1, la columna a es igual a 1, lo que no satisface el predicado de la sesión 2.

LAQ se realiza de manera optimista con la suposición de que una fila no se modifica después de comprobar el predicado. Si se cumple el predicado y la fila no se ha modificado después de comprobarlo, es modificada por la transacción actual.

Dado que no se toman bloqueos U, una transacción simultánea podría modificar la fila después de que el predicado haya sido evaluado. Si hay una transacción activa que tiene un bloqueo X TID en la fila, el motor de base de datos espera a que esta se complete. Si la fila ha cambiado después de evaluar el predicado anteriormente, el motor de base de datos vuelve a evaluar (volver a calificar) el predicado antes de modificar la fila. Si el predicado aún se cumple, se modifica la fila.

La recalificación del predicado está soportada por un subconjunto de los operadores del motor de consulta. Si se necesita volver a evaluar el predicado, pero el plan de consulta usa un operador que no admite la recalificación del predicado, el motor de base de datos interrumpe internamente el procesamiento de la instrucción y lo reinicia sin LAQ. Cuando se produce una anulación de este tipo, el evento extendido lock_after_qual_stmt_abort se desencadena.

Algunas declaraciones, por ejemplo, las declaraciones UPDATE con asignación de variables y aquellas con la cláusula OUTPUT , no se pueden anular ni reiniciar sin cambiar su semántica. Para estas instrucciones, no se usa LAQ.

En el ejemplo siguiente, el predicado se vuelve a evaluar 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
Sesión 1 Sesión 2
BEGIN TRANSACTION;
UPDATE t3
SET b = b + 10
WHERE a = 1;
BEGIN TRANSACTION;
UPDATE t3
SET b = b + 10
WHERE a = 1;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

Heurística LAQ

Como se describe en Bloqueo después de la calificación (LAQ), cuando se usa LAQ, algunas instrucciones pueden reiniciarse y procesarse internamente sin LAQ. Si esto sucede con frecuencia, la sobrecarga del procesamiento repetido podría ser significativa. Para mantener esta sobrecarga en un mínimo, el bloqueo optimizado usa un mecanismo heurístico para realizar un seguimiento del procesamiento repetido. Este mecanismo deshabilita LAQ para la base de datos si la sobrecarga supera un umbral.

Para los fines del mecanismo heurístico, el trabajo realizado por una instrucción se mide en el número de páginas que ha procesado (lecturas lógicas). Si el motor de base de datos está modificando una fila que ha sido modificada por otra transacción después de que se haya iniciado el procesamiento de la instrucción, el trabajo realizado por la instrucción se considera potencialmente desperdiciado porque la instrucción podría cancelarse y reiniciarse. El sistema controla el trabajo total potencialmente desperdiciado y el trabajo total realizado por todas las declaraciones de la base de datos.

LAQ está deshabilitado para la base de datos si el porcentaje del trabajo potencialmente desperdiciado supera un umbral. LAQ también se deshabilita si el número de instrucciones reiniciadas supera un umbral.

Si el trabajo desperdiciado y el número de instrucciones reiniciadas se encuentran por debajo de sus respectivos umbrales, LAQ se vuelve a habilitar para la base de datos.

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
Sesión 1 Sesión 2
BEGIN TRANSACTION T1;
UPDATE t4
SET b = 2
WHERE a = 1;
BEGIN TRANSACTION T2;
UPDATE t4
SET b = 3
WHERE b = 2;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

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 califica; por lo tanto, se omite y la sentencia se completa sin haber sido bloqueada por 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

Importante

Incluso sin LAQ, las aplicaciones no deben suponer que el motor de base de datos garantiza un orden estricto sin usar indicaciones de bloqueo cuando se utilizan niveles de aislamiento que se basan en el versionado de filas. Nuestra recomendación general para los clientes que ejecutan cargas de trabajo simultáneas en RCSI que dependen del orden de ejecución estricto de las transacciones (como se muestra en el ejemplo anterior) es utiliza niveles de aislamiento más estrictos como REPEATABLE READ y SERIALIZABLE.

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 XACTwait_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 XACTwait_resource, con una intención de modificar.
      • LCK_M_S_XACT: se produce cuando una tarea está esperando un bloqueo compartido en un tipo de XACTwait_resource, donde no se puede deducir la intención. Este escenario no es común.
  • Visibilidad de los recursos de bloqueo
  • Visibilidad de los recursos de espera
  • 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.
  • Eventos extendidos
    • El evento lock_after_qual_stmt_abort se desencadena cuando se anula una instrucción de manera interna y se reinicia debido a un conflicto con otra transacción. Para obtener más información, consulte Bloqueo después de la calificación (LAQ).

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. 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 usas niveles de aislamiento más estrictos, como REPEATABLE READ o SERIALIZABLE, el motor de base de datos mantiene bloqueos de fila y página hasta el final de la transacción, incluso con bloqueo optimizado habilitado, tanto para lectores como para escritores, lo que resulta en un aumento del bloqueo y en el uso de memoria para los bloqueos.

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. Los indicadores de bloqueo obligan al motor de base de datos a tomar bloqueos de fila o página y mantenerlos hasta el final de la transacción, para cumplir con la intención de los indicadores 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 es necesario reescribir las consultas. Las consultas que no utilizan indicaciones se benefician más del bloqueo optimizado.

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 sentencias DML no se pueden ejecutar en réplicas de solo lectura y no se toman los bloqueos de fila y de página correspondientes.

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

De momento, no.

Contenido relacionado