Bloqueo optimizado

Se aplica a:Azure SQL Database

En este artículo se presenta la característica de bloqueo optimizado, una nueva capacidad del motor de base de datos de SQL Server 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 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, revise la sección sobre 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 una consulta en la versión confirmada más reciente de la fila sin adquirir un bloqueo, lo que mejora la simultaneidad. Para obtener más información, revise la sección Bloqueo después de la calificación (LAQ).

Por ejemplo:

• Sin bloqueo optimizado, la actualización de 1 millón de filas en una tabla podría requerir 1 millón de bloqueos de fila exclusivos (X) mantenidos hasta el final de la transacción.
• Con el bloqueo optimizado, la actualización de 1 millón de filas en una tabla podría requerir 1 millón de bloqueos de fila X, pero cada bloqueo se libera en cuanto se actualiza cada fila y solo se mantendrá un bloqueo TID hasta el final de la transacción.

En este artículo se tratan estos dos conceptos básicos del bloqueo optimizado en detalle.

Disponibilidad

Actualmente, el bloqueo optimizado solo está disponible en Azure SQL Database. Para obtener más información, consulte ¿Dónde está disponible el bloqueo optimizado actualmente?

¿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 en la base de datos:

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

Si no está conectado a la base de datos especificada en DATABASEPROPERTYEX, el resultado será NULL. Debe recibir 0 (el bloqueo optimizado está deshabilitado) o 1 (habilitado).

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.

Tanto ADR como RCSI están habilitados de forma predeterminada en Azure SQL Database. Para comprobar que estas opciones están habilitadas para la base de datos actual, use la siguiente consulta de 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, con el fin de proteger la integridad y la coherencia de los datos.

Cuando una transacción necesita modificar los datos, puede solicitar 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 se permite que varias transacciones accedan 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 de base de datos puede proporcionar la semántica para los niveles de aislamiento de transacción DE ANSI SQL. 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)

Cada fila del motor de base de datos contiene internamente un identificador de transacción (TID) cuando el control de versiones de fila está en uso. 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 adquirirán un bloqueo S en el TID para comprobar si la primera transacción sigue activa. Con el bloqueo de TID, se siguen realizando bloqueos de página y fila para actualizaciones, 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 en el recurso TID, reemplazando los bloqueos de página y fila (clave), como se muestra en la siguiente demostración. (Otros bloqueos de objetos y bases de datos estándar no se ven afectados por el bloqueo optimizado).

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

Considere el siguiente escenario de ejemplo de T-SQL que busca bloqueos en la sesión actual del usuario:

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

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

BEGIN TRAN
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 TRAN
GO
DROP TABLE IF EXISTS t0;

La misma consulta sin la ventaja del bloqueo optimizado crea cuatro bloqueos:

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

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

Basándose en la infraestructura de TID, el bloqueo optimizado cambia la forma en que los predicados de consulta protegen los bloqueos.

Sin bloqueo optimizado, los predicados de las consultas se comprueban por fila por fila en un examen tomando primero un bloqueo de fila de actualización (U). Si se cumple el predicado, se toma un bloqueo de fila X antes de actualizar la fila.

Con el bloqueo optimizado, y cuando se habilita el nivel de aislamiento de instantánea de lectura confirmada (RCSI), los predicados se aplican en la versión confirmada más reciente sin tener que realizar 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 realmente 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 bloquearán entre sí.

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 TRAN UPDATE t1 SET b=b+10 WHERE a=1;
	BEGIN TRAN UPDATE t1 SET b=b+10 WHERE a=2;
COMMIT TRAN
	COMMIT TRAN

El comportamiento del bloqueo de cambios con bloqueo optimizado en el ejemplo anterior. Sin bloqueo optimizado, se bloqueará la sesión 2.

Sin embargo, con el bloqueo optimizado, la sesión 2 no se bloqueará porque la versión confirmada más reciente de la fila 1 contiene a=1, que no satisface el predicado de la sesión 2.

Si se cumple el predicado, esperamos a que finalice cualquier transacción activa de la fila. Si tuvimos que esperar al bloqueo de S TID, es posible que la fila haya cambiado y que la versión confirmada más reciente haya cambiado. En ese caso, en lugar de anular la transacción debido a un conflicto de actualización, el motor de base de datos volverá a intentar la evaluación del predicado en la misma fila. Si el predicado se califica al reintentar, se actualizará la fila.

Tenga en cuenta el ejemplo siguiente cuando se vuelva a intentar automáticamente un cambio de predicado:

CREATE TABLE t2
(a int not null
,b int null);

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

Sesión 1	Sesión 2
BEGIN TRAN UPDATE t2 SET b=b+10 WHERE a=1;
	BEGIN TRAN UPDATE t2 SET b=b+10 WHERE a=1;
COMMIT TRAN
	COMMIT TRAN

Cambios de comportamiento de consulta con bloqueo optimizado y RCSI

Los sistemas simultáneos en el nivel de aislamiento de instantánea confirmada de lectura (RCSI) con cargas de trabajo que dependen del orden de ejecución estricto de las transacciones pueden experimentar un comportamiento de consulta diferente cuando está habilitado el bloqueo optimizado.

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

CREATE TABLE t1 (a int not null, b int null);

INSERT INTO t1 VALUES (1,1);
GO

Sesión 1	Sesión 2
BEGIN TRAN T1 UPDATE t1 SET b=2 WHERE a=1;
	BEGIN TRAN T2 UPDATE t1 SET b=3 WHERE b=2;
COMMIT TRAN
	COMMIT TRAN

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

Sin LAQ

Sin LAQ, la transacción T2 se bloqueará y esperará a que se complete la transacción T1.

Después de confirmar ambas transacciones, la tabla t1 contendrá las filas siguientes:

 a | b
 1 | 3

Con LAQ

Con LAQ, la transacción T2 usará la versión confirmada más reciente de la fila b (b=1 en el almacén de versiones) para evaluar su predicado (b=2). Esta fila no cumple los requisitos; por lo tanto, se omite y T2 se mueve a la siguiente fila 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 t1 contendrá las filas siguientes:

 a | b
 1 | 2

Importante

Incluso sin LAQ, las aplicaciones no deben suponer que SQL Server (en niveles de aislamiento de control de versiones) garantizará un orden estricto, sin usar sugerencias de bloqueo. Nuestra recomendación general para los clientes en sistemas simultáneos en RCSI con cargas de trabajo que dependen del orden de ejecución estricto de las transacciones (como se muestra en el ejercicio anterior) es usar niveles de aislamiento más estrictos.

Adiciones de diagnóstico para el bloqueo optimizado

Para permitir la supervisión y la solución de problemas de bloqueo y interbloqueo con bloqueo optimizado, busca las siguientes adiciones:

• Tipos de espera para el bloqueo optimizado
  • Tipos de espera XACT y 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 wait_resource XACT, con una intención de leer.
    • LCK_M_S_XACT_MODIFY: se produce cuando una tarea está esperando un bloqueo compartido en un elemento wait_resource XACT, con una intención de modificar.
    • LCK_M_S_XACT : se produce cuando una tarea está esperando un bloqueo compartido en un elemento wait_resource XACT, donde no se puede deducir la intención. Poco frecuente.
• 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 usar el aislamiento de lectura confirmada como nivel de aislamiento predeterminado. Si no está habilitado, habilite RCSI con el ejemplo siguiente:

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

En Azure SQL Database, RCSI está habilitado de forma predeterminada y la lectura confirmada es el nivel de aislamiento predeterminado. Con RCSI habilitado y cuando se usa el nivel de aislamiento de lectura confirmada, los lectores no bloquean los escritores y los escritores no bloquean los lectores. Los lectores leen una versión de la fila de la instantánea tomada al principio de la consulta. Con LAQ, los escritores calificarán las filas por predicado en función de la versión confirmada más reciente de la fila sin adquirir bloqueos U. Asimismo, con LAQ una consulta esperará 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 reducirá la memoria de bloqueo necesaria. Esto se debe a que los lectores no toman bloqueos y los escritores solo toman bloqueos de corta duración, en lugar de bloqueos que expiran al final de la transacción. Cuando se usan niveles de aislamiento más estrictos como lectura repetible o serializable, el motor de base de datos se ve obligado a mantener los bloqueos de fila y página hasta el final de la transacción para los lectores y escritores, lo que da lugar a un aumento del bloqueo y la memoria de bloqueo.

Evitar sugerencias de bloqueo

Aunque se respetan las sugerencias de tabla y consulta, estas reducen la ventaja del bloqueo optimizado. Las sugerencias de bloqueo como UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK, etc., en las consultas reducen las ventajas completas del bloqueo optimizado. Tener estas sugerencias de bloqueo en las consultas obliga al motor de base de datos a tomar bloqueos de fila o página y mantenerlas 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 select con UPDLOCK y, posteriormente, 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 usan sugerencias se beneficiarán de la mayoría de los bloqueos optimizados.

Una sugerencia de tabla en una tabla de una consulta no deshabilitará 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 UPDATE. Por ejemplo:

CREATE TABLE t3
(a int not null
, b int not null);

CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;

En el ejemplo de consulta anterior, solo la tabla t4 se verá afectada por la sugerencia de bloqueo, mientras que t3 todavía puede beneficiarse del bloqueo optimizado.

UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;

En el ejemplo de consulta anterior, solo la tabla t3 usará el nivel de aislamiento de lectura repetible y mantendrá bloqueos hasta el final de la transacción. Otras actualizaciones de t3 pueden seguir beneficiándose del bloqueo optimizado. Lo mismo se aplica a la sugerencia HOLDLOCK.

Preguntas más frecuentes

¿Dónde está disponible actualmente el bloqueo optimizado?

Actualmente, el bloqueo optimizado está disponible en Azure SQL Database.

El bloqueo optimizado está disponible en los siguientes niveles de servicio:

• todos los niveles de servicio de DTU
• todos los niveles de servicio de núcleo virtual, incluidos los aprovisionados y sin servidor

El bloqueo optimizado no está disponible actualmente en:

• Instancia administrada de Azure SQL
• SQL Server 2022 (16.x)

¿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.

¿Puedo deshabilitar el bloqueo optimizado?

Actualmente, los clientes pueden crear una solicitud de soporte técnico para deshabilitar el bloqueo optimizado.

Siga estos pasos para crear una nueva solicitud de soporte técnico a partir del Azure Portal para Azure SQL Database.

1. En primer lugar, compruebe que el bloqueo optimizado está habilitado para la base de datos.

2. En el menú de Azure Portal, seleccione Ayuda y soporte técnico.

   

3. Seleccione Ayuda y soporte técnico y Crear una solicitud de soporte.

   

4. En Tipo de problema, seleccione Técnico.

5. En Suscripción, Servicio y Recurso, seleccione el SQL Database deseado.

6. En Resumen, escriba "Deshabilitar bloqueo optimizado".

7. En Tipo de problema, elija Rendimiento y ejecución de consultas.

8. En Subtipo de problema, elija Bloqueo e interbloqueos.

9. En Detalles adicionales, proporcione la mayor cantidad de información posible sobre por qué desea deshabilitar el bloqueo optimizado. Nos interesa revisar los motivos y casos de uso para deshabilitar el bloqueo optimizado con usted.

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