Guía de versiones de fila y bloqueo de transacciones

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

En cualquier base de datos, la falta de administración de las transacciones a menudo produce problemas de contención y de rendimiento en sistemas con muchos usuarios. A medida que aumenta el número de usuarios que obtienen acceso a los datos, adquiere importancia el que las aplicaciones utilicen las transacciones eficazmente. En esta guía se describen los mecanismos de versiones de fila y bloqueo que el motor de base de datos de SQL Server utiliza para garantizar la integridad física de cada transacción y proporciona información acerca de cómo las aplicaciones pueden controlar las transacciones de manera eficaz.

Nota:

El bloqueo optimizado es una característica del motor de base de datos que se introdujo en 2023 que reduce drásticamente la memoria de bloqueo y el número de bloqueos necesarios simultáneamente para las escrituras. Este artículo se ha actualizado para describir el motor de base de datos de SQL Server con y sin bloqueo optimizado. Actualmente, el bloqueo optimizado solo está disponible en Azure SQL Database.

• Para obtener más información y saber dónde está disponible el bloqueo optimizado, consulte Bloqueo optimizado.
• Para determinar si el bloqueo optimizado está habilitado en la base de datos, consulte ¿Está habilitado el bloqueo optimizado?

El bloqueo optimizado ha actualizado significativamente algunas secciones de este artículo, entre las que se incluyen:

• Bloqueo del motor de base de datos
• Operación de eliminación
• Operación de inserción
• Extensión de bloqueo
• Reducción de bloqueos y escalaciones
• Comportamiento durante la modificación de datos
• Resumen del comportamiento
• Sugerencias de bloqueo

Conceptos básicos sobre las transacciones

Una transacción es una secuencia de operaciones realizadas como una sola unidad lógica de trabajo. Una unidad lógica de trabajo debe exhibir cuatro propiedades, conocidas como propiedades de atomicidad, coherencia, aislamiento y durabilidad (ACID), para ser calificada como transacción.

Atomicidad
Una transacción debe ser una unidad atómica de trabajo, tanto si se realizan todas sus modificaciones en los datos, como si no se realiza ninguna de ellas.

Coherencia
Cuando finaliza, una transacción debe dejar todos los datos en un estado coherente. En una base de datos relacional, se deben aplicar todas las reglas a las modificaciones de la transacción para mantener la integridad de todos los datos. Todas las estructuras internas de datos, como índices de árbol B o listas doblemente vinculadas, deben ser correctas al final de la transacción.

Nota:

La documentación de SQL Server utiliza el término árbol B generalmente en referencia a los índices. En los índices del almacén de filas, SQL Server implementa un árbol B+. Esto no se aplica a los índices de almacén de columnas ni a los almacenes de datos en memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.

Aislamiento
Las modificaciones realizadas por transacciones simultáneas se deben aislar de las modificaciones llevadas a cabo por otras transacciones simultáneas. Una transacción reconoce los datos en el estado en que estaban antes de que otra transacción simultánea los modificara, o bien después de que la segunda transacción haya concluido, pero no reconoce un estado intermedio. Esto se conoce como seriabilidad, ya que deriva en la capacidad de volver a cargar los datos iniciales y reproducir una serie de transacciones para finalizar con los datos en el mismo estado en que estaban después de realizar las transacciones originales.

Durabilidad
Una vez concluida una transacción totalmente durable, sus efectos son permanentes en el sistema. Las modificaciones persisten aún en el caso de producirse un error del sistema. SQL Server 2014 (12.x) y versiones posteriores permiten transacciones duraderas retrasadas. Las transacciones durables diferidas se confirman antes de que la entrada de registro de transacciones se guarde en el disco. Para más información sobre la durabilidad de las transacciones diferidas, vea el artículo Control de la durabilidad de transacciones.

Los programadores de SQL son los responsables de iniciar y finalizar las transacciones en puntos que exijan la coherencia lógica de los datos. El programador debe definir la secuencia de modificaciones de datos que los dejan en un estado coherente en relación con las reglas de negocios de la organización. El programador incluye estas instrucciones de modificación en una sola transacción de forma que el motor de base de datos de SQL Server Database puede hacer cumplir la integridad física de la misma.

Es responsabilidad de un sistema de base de datos corporativo, como una instancia del motor de base de datos de SQL Server, proporcionar los mecanismos que aseguren la integridad física de cada transacción. El motor de base de datos de SQL Server brinda:

• Servicios de bloqueo que preservan el aislamiento de la transacción.

• Los servicios de registro garantizan la durabilidad de la transacción. Para las transacciones totalmente durables, la entrada de registro se graba en el disco antes de la confirmación de las transacciones. Por tanto, aunque se produzca un error en el hardware del servidor, el sistema operativo o la instancia del motor de base de datos de SQL Server, la instancia usa los registros de transacciones durante el reinicio para revertir automáticamente las transacciones incompletas al punto en que se haya producido el error del sistema. Las transacciones durables diferidas se confirman antes de que la entrada del registro de transacciones se grabe en el disco. Este tipo de transacciones se puede perder si se produce un error del sistema antes de que la entrada del registro se grabe en el disco. Para más información sobre la durabilidad de las transacciones diferidas, vea el artículo Control de la durabilidad de transacciones.

• Características de administración de transacciones que exigen la atomicidad y coherencia de la transacción. Una vez iniciada una transacción, debe completarse correctamente (confirmarse); en caso contrario, la instancia del motor de base de datos de SQL Server deshará todas las modificaciones de datos realizadas desde que se inició la transacción. Nos referimos a esta operación como revertir una transacción porque devuelve los datos al estado en el que estaban antes de esos cambios.

Control de las transacciones

Las aplicaciones controlan las transacciones principalmente al especificar cuándo se inicia y finaliza una transacción. Se pueden especificar mediante instrucciones Transact-SQL o funciones de la interfaz de programación de aplicaciones (API) de bases de datos. El sistema también debe ser capaz de controlar correctamente los errores que terminan una transacción antes de que se concluya. Para más información, vea Transacciones, Realización de transacciones en ODBC y Transacciones en SQL Server Native Client.

De manera predeterminada, las transacciones se administran en las conexiones. Cuando se inicia una transacción en una conexión, todas las instrucciones Transact-SQL ejecutadas en esa conexión forman parte de la transacción hasta que ésta finaliza. No obstante, en una sesión de conjunto de resultados activos múltiples (MARS), una transacción de Transact-SQL explícita o implícita se convierte en una transacción de lote que se administra en los lotes. Cuando se termina el lote, si la transacción con ámbito de lote no se confirma ni se revierte, SQL Server la revierte automáticamente. Para obtener más información, vea Utilizar conjuntos de resultados activos múltiples (MARS).

Inicio de las transacciones

Mediante funciones de la API e instrucciones Transact-SQL, puede iniciar transacciones en una instancia del motor de base de datos de SQL Server como transacciones explícitas, de confirmación automática o implícitas.

Transacciones explícitas
En una transacción explícita se define explícitamente tanto el inicio como el final de la transacción a través de una función API o emitiendo las instrucciones BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION o ROLLBACK WORK de Transact-SQL. Cuando la transacción termina, la conexión vuelve al modo de transacción en que estaba antes de iniciar la transacción explícita, es decir, el modo implícito o el modo de confirmación automática.

En una transacción explícita se pueden utilizar todas las instrucciones Transact-SQL, excepto las siguientes:

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX …
• CREATE FULLTEXT INDEX …
• BACKUP
• RESTORE
• RECONFIGURE
• Procedimientos almacenados de la búsqueda de texto completo
• sp_dboption para establecer opciones de base de datos ni utilizar ningún procedimiento del sistema que modifique la base de datos master en transacciones explícitas o implícitas.

Nota:

UPDATE STATISTICS se puede utilizar dentro de una transacción explícita. Sin embargo, UPDATE STATISTICS se confirma independientemente de la transacción que la incluye y no se puede revertir.

Transacciones de confirmación automática
El modo de confirmación automática es el modo de administración de transacciones predeterminado de SQL Server Database Engine. Cada instrucción Transact-SQL se confirma o se revierte cuando finaliza. Si una instrucción termina correctamente, se confirma; si encuentra un error, se revierte. Una conexión a una instancia del motor de base de datos de SQL Server funciona en modo de confirmación automática siempre que no se suplante este modo predeterminado mediante transacciones explícitas o implícitas. El modo de confirmación automática es también el modo predeterminado para ADO, OLE DB, ODBC y DB-Library.

Transacciones implícitas
Cuando una conexión funciona en modo de transacciones implícitas, la instancia del motor de base de datos de SQL Server inicia automáticamente una nueva transacción después de confirmar o revertir la transacción actual. No tiene que realizar ninguna acción para delinear el inicio de una transacción, solo tiene que confirmar o revertir cada transacción. El modo de transacciones implícitas genera una cadena continua de transacciones. Establezca el modo de transacción implícita a través de una función de la API o la instrucción SET IMPLICIT_TRANSACTIONS ON de Transact-SQL. Este modo también se denomina Autocommit OFF. Vea Método setAutoCommit (SQLServerConnection).

Tras establecer el modo de transacciones implícitas en una conexión, la instancia del motor de base de datos de SQL Server inicia automáticamente una transacción la primera vez que ejecuta una de estas instrucciones:

• ALTER TABLE

• CREATE

• Delete

• DROP

• FETCH

• GRANT

• INSERT

• OPEN

• REVOKE

• SELECT

• TRUNCATE TABLE

• UPDATE

• Transacciones con ámbito de loteSolo aplicable a MARS (conjuntos de resultados activos múltiples), una transacción implícita o explícita de Transact-SQL que se inicia en una sesión de MARS se convierte en una transacción de ámbito de lote. Si no se confirma o revierte una transacción de ámbito de lote cuando se completa un lote, SQL Server la revierte automáticamente.

• Transacciones distribuidas Abarcan dos o más servidores conocidos como administradores de recursos. La administración de la transacción debe ser coordinada entre los administradores de recursos mediante un componente de servidor llamado administrador de transacciones. Cada instancia del motor de base de datos de SQL Server puede funcionar como administrador de recursos en las transacciones distribuidas que coordinan los administradores de transacciones, como el Coordinador de transacciones distribuidas de Microsoft (MS DTC) u otros administradores que admitan la especificación Open Group XA del procesamiento de transacciones distribuidas. Para obtener más información, consulte la documentación de MS DTC.

  Una transacción en una única instancia del motor de base de datos de SQL Server que se extiende en dos o más bases de datos es una transacción distribuida de facto. La instancia administra la transacción distribuida internamente; para el usuario funciona como una transacción local.

  En la aplicación, una transacción distribuida se administra de forma muy parecida a una transacción local. Al final de la transacción, la aplicación solicita que se confirme o se revierta la transacción. El administrador de transacciones debe administrar una confirmación distribuida de forma diferente para reducir al mínimo el riesgo de que, si se produce un error en la red, algunos administradores de recursos realicen confirmaciones mientras los demás revierten la transacción. Esto se consigue mediante la administración del proceso de confirmación en dos fases (la fase de preparación y la fase de confirmación), que se conoce como confirmación en dos fases (2PC).

  • Fase de preparación Cuando el administrador de transacciones recibe una solicitud de confirmación, envía un comando de preparación a todos los administradores de recursos implicados en la transacción. Cada administrador de recursos hace lo necesario para que la transacción sea duradera y todos los búferes que contienen imágenes del registro de la transacción se pasan a disco. A medida que cada administrador de recursos completa la fase de preparación, notifica si la preparación ha tenido éxito o no al administrador de transacciones. SQL Server 2014 (12.x) introdujo durabilidad diferida de transacciones. Las transacciones durables diferidas se confirman antes de que las imágenes del registro de la transacción se vacíen en el disco. Para más información sobre la durabilidad de las transacciones diferidas, vea el artículo Control de la durabilidad de transacciones.

  • Fase de confirmación Si el administrador de transacciones recibe la notificación de que todas las preparaciones son correctas por parte de todos los administradores de recursos, envía comandos de confirmación a cada administrador de recursos. A continuación, los administradores de recursos pueden completar la confirmación. Si todos los administradores de recursos indican que la confirmación ha sido correcta, el administrador de transacciones envía una notificación de éxito a la aplicación. Si algún administrador de recursos informó de un error al realizar la preparación, el administrador de transacciones envía un comando para revertir la transacción a cada administrador de recursos e indica a la aplicación que se ha producido un error de confirmación.

    Las aplicaciones del motor de base de datos de SQL Server pueden administrar transacciones distribuidas a través de Transact-SQL o de la API de base de datos. Para obtener más información, vea BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Finalización de transacciones

Puede finalizar las transacciones con una instrucción COMMIT o ROLLBACK, o mediante una función de la API correspondiente.

• COMMIT Si una transacción es correcta, confírmela. La instrucción COMMIT garantiza que todas las modificaciones de la transacción se conviertan en una parte permanente de la base de datos. La instrucción COMMIT también libera recursos que utiliza la transacción como, por ejemplo, los bloqueos.

• ROLLBACK Si se produce un error en una transacción o el usuario decide cancelar la transacción, reviértala. La instrucción ROLLBACK revierte todas las modificaciones realizadas en la transacción al devolver los datos al estado en que estaban al inicio de la transacción. La instrucción ROLLBACK también libera los recursos que mantiene la transacción.

Nota:

En conexiones habilitadas para admitir varios conjuntos de resultados activos (MARS), una transacción explícita que se haya iniciado mediante una función de la API no se puede confirmar mientras haya solicitudes de ejecución pendientes. Cualquier intento de confirmación de una transacción de este tipo mientras se ejecutan operaciones pendientes tendrá como resultado un error.

Errores al procesar la transacción

Si un error impide la terminación correcta de una transacción, SQL Server revierte automáticamente la transacción y libera todos los recursos que mantiene la transacción. Si se interrumpe la conexión de red del cliente con una instancia del motor de base de datos de SQL Server, las transacciones pendientes de la conexión revierten al estado anterior cuando la red notifica la interrupción a la instancia. Si la aplicación cliente falla o si el equipo cliente se bloquea o se reinicia, también se interrumpe la conexión y la instancia del motor de base de datos de SQL Server revierte las conexiones pendientes cuando la red le notifica la interrupción. Si el cliente cierra la aplicación, las transacciones pendientes se revierten.

Si se produce el error de una instrucción en tiempo de ejecución (como una infracción de restricciones) en un archivo por lotes, el comportamiento predeterminado del motor de base de datos de SQL Server consiste en revertir solamente la instrucción que generó el error. Puede modificar este comportamiento con la instrucción SET XACT_ABORT. Una vez ejecutada la instrucción SET XACT_ABORT, los errores de instrucciones en tiempo de ejecución hacen que se revierta automáticamente la transacción actual. Los errores de compilación, como los de sintaxis, no se ven afectados por SET XACT_ABORT. Para obtener más información, vea SET XACT_ABORT (Transact-SQL).

Cuando se producen errores, la acción correctora (COMMIT o ROLLBACK) debería incluirse en el código de aplicación. Una herramienta eficaz para controlar errores, incluidos los de transacciones, es la construcción TRY…CATCH de TRY...CATCH de Transact-SQL. Para obtener más información y ejemplos que incluyan transacciones, vea TRY...CATCH (Transact-SQL). A partir de SQL Server 2012 (11.x) , puede usar la instrucción THROW para generar una excepción y transferir la ejecución a un bloque CATCH o a una construcción TRY...CATCH. Para obtener más información, vea THROW (Transact-SQL).

Errores de compilación y en tiempo de ejecución del modo de confirmación automática

En el modo de confirmación automática, a veces parece que el motor de base de datos de SQL Server ha revertido un proceso por lotes completo en vez de revertir solamente una instrucción SQL. Esto sucede si se trata de un error de compilación, no en el caso de un error en tiempo de ejecución. Los errores de compilación impiden que el motor de base de datos de SQL Server genere un plan de ejecución, por lo que no se ejecuta ninguna instrucción del proceso por lotes. Aunque parezca que se han revertido todas las instrucciones anteriores a la que generó el error, el error impidió que se ejecutara ninguna instrucción del proceso por lotes. En el ejemplo siguiente, no se ejecutó ninguna de las instrucciones INSERT del tercer proceso por lotes debido a un error de compilación. Parece que se han revertido las dos primeras instrucciones INSERT cuando, en realidad, nunca se ejecutaron.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

En el ejemplo siguiente, la tercera instrucción INSERT genera un error de clave principal duplicada en tiempo de ejecución. Las dos primeras instrucciones INSERT eran correctas y se han confirmado, por lo que permanecen después de producirse el error en tiempo de ejecución.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

El motor de base de datos de SQL Server utiliza la resolución demorada de nombres, en la que no se resuelven los nombres de los objetos hasta la ejecución. En el ejemplo siguiente, se han ejecutado y confirmado las dos primeras instrucciones INSERT y esas dos filas permanecen en la tabla TestBatch después de que la tercera instrucción INSERT generara un error en tiempo de ejecución al hacer referencia a una tabla que no existe.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Conceptos básicos sobre las versiones de fila y bloqueo

El motor de base de datos de SQL Server utiliza los siguientes mecanismos para garantizar la integridad de las transacciones y mantener la coherencia de las bases de datos cuando varios usuarios obtienen acceso a los datos al mismo tiempo:

• Bloqueo

  Cada transacción solicita diferentes tipos de bloqueo en los recursos como, por ejemplo, filas, páginas o tablas de los que depende la transacción. Estos bloqueos impiden que otras transacciones puedan modificar los recursos de forma que esto provoque problemas para la transacción que solicita el bloqueo. Cada transacción libera sus bloqueos cuando ya no depende de los recursos bloqueados.

• Versiones de fila

  Cuando un nivel de aislamiento basado en versiones de fila está habilitado, el motor de base de datos de SQL Server mantiene versiones de cada fila que se ha modificado. Las aplicaciones pueden especificar que una transacción utilice las versiones de fila para ver los datos tal como eran al empezar la transacción o la consulta en lugar de proteger todas las lecturas con bloqueos. Mediante las versiones de fila, las probabilidades de que una operación de lectura bloquee otras transacciones se reduce notablemente.

El bloqueo y las versiones de fila impiden a los usuarios leer los datos no confirmados así como cualquier intento de cambiar los mismos datos a la vez. Sin el bloqueo o el control de versiones de fila, las consultas ejecutadas para esos datos podrían generar resultados inesperados al devolver datos que todavía no se han confirmado en la base de datos.

Las aplicaciones pueden elegir los niveles de aislamiento de las transacciones, que definen el nivel de protección de la transacción frente a las modificaciones efectuadas por otras transacciones. Las sugerencias de nivel de tabla pueden especificarse para instrucciones Transact-SQL concretas para personalizar el comportamiento con el fin de que se ajuste a los requisitos de la aplicación.

Administrar el acceso simultáneo a datos

En ocasiones, los usuarios tienen acceso a un recurso al mismo tiempo, es decir, simultáneamente. El acceso simultáneo a los datos requiere la utilización de mecanismos para impedir efectos negativos cuando varios usuarios intentan modificar recursos que otros usuarios están utilizando.

Efectos de la simultaneidad

Los usuarios que modifican datos pueden afectar a otros usuarios que leen o modifican los mismos datos a la vez. Se dice que estos usuarios tienen acceso a los datos de forma simultánea. Si un sistema de almacenamiento de datos no dispone de control de simultaneidad, los usuarios se pueden encontrar con los siguientes efectos secundarios:

• Actualizaciones perdidas

  Este problema surge cuando dos o más transacciones seleccionan la misma fila y, a continuación, la actualizan de acuerdo con el valor seleccionado originalmente. Ninguna transacción es consciente de las otras transacciones. La última actualización sobrescribe las actualizaciones realizadas por las otras transacciones y, en consecuencia, se pierden datos.

  Por ejemplo, dos editores realizan una copia electrónica del mismo documento. Cada editor modifica la copia de forma independiente y después la guarda, sobrescribiendo el documento original. El editor que guarda la copia modificada en último lugar sobrescribe las modificaciones que realizó el otro editor. Este problema se puede evitar si un editor no tiene acceso al archivo hasta que el otro finaliza y confirma la transacción.

• Dependencia no confirmada (lectura no actualizada)

  Este problema se produce cuando una transacción selecciona una fila que está siendo actualizada por otra transacción. La segunda transacción lee datos que no han sido confirmados aún y pueden ser modificados por la transacción que actualiza la fila.

  Por ejemplo, un editor realiza cambios en un documento electrónico. Durante las modificaciones, un segundo editor toma una copia del documento que contiene todas las modificaciones realizadas hasta el momento y la distribuye a los destinatarios. El primer editor decide que los cambios realizados son erróneos, así que los elimina y guarda el documento. El documento distribuido contiene modificaciones que ya no existen y deben tratarse como si nunca hubieran existido. Este problema se puede evitar si nadie lee el documento modificado hasta que el primer editor realiza el almacenamiento final de las modificaciones y confirma la transacción.

• Análisis contradictorios (lectura irrepetible)

  Este problema se produce cuando una transacción obtiene acceso a la misma fila varias veces y en cada ocasión lee datos diferentes. El análisis incoherente es similar a la dependencia no confirmada en tanto que una transacción está modificando los datos que está leyendo una segunda transacción. Sin embargo, en el caso del análisis incoherente, los datos que lee la segunda transacción están confirmados por la transacción que realizó el cambio. Además, el análisis incoherente comprende varias lecturas (dos o más) de la misma fila y las transacciones modifican la información cada vez; de ahí el término de lectura irrepetible.

  Por ejemplo, un editor lee el mismo documento dos veces pero, entre cada lectura, el escritor vuelve a escribir el documento. Cuando el editor lee el documento por segunda vez, éste ha cambiado. La lectura original no era repetible. Este problema se puede evitar si el escritor no cambia el documento hasta que el editor finaliza la lectura por última vez.

• Lecturas fantasma

  Una lectura fantasma es una situación que se produce cuando se ejecutan dos consultas idénticas y la recopilación de filas devuelta por la segunda consulta es diferente. En el siguiente ejemplo se muestra cómo se puede producir esto. Suponga que las dos transacciones siguientes se están ejecutando al mismo tiempo. Las dos instrucciones SELECT de la primera transacción pueden devolver resultados diferentes porque la instrucción INSERT de la segunda transacción cambia los datos utilizados por ambas.

  --Transaction 1
  BEGIN TRAN;
  SELECT ID FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  --The INSERT statement from the second transaction occurs here.
  SELECT ID FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee
    (Id, Name) VALUES(6 ,'New');
  COMMIT;
  

• Dobles lecturas o lecturas que faltan por causa de las actualizaciones de las filas

  • No se encuentra una fila actualizada o una fila actualizada aparece varias veces

    Las transacciones que se ejecutan en el nivel READ UNCOMMITTED no emiten bloqueos compartidos para impedir que otras transacciones modifiquen los datos leídos por la transacción actual. Las transacciones que se están ejecutando en el nivel de READ COMMITTED emiten bloqueos compartidos, pero los bloqueos de fila o página se liberan una vez leída la fila. En cualquier caso, al recorrer un índice, si otro usuario cambia la columna de clave de índice de la fila mientras usted lo está leyendo, la fila podría aparecer de nuevo si el cambio en la clave movió la fila a una posición situada por delante de su punto de recorrido. De igual forma, la fila podría no aparecer si el cambio en la clave movió la fila a una posición en el índice que ya había sido leída. Para evitarlo, utilice la sugerencia SERIALIZABLE o HOLDLOCK, o bien las versiones de fila. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).

  • Faltan una o más filas que no eran objeto de la actualización

    Cuando utilice READ UNCOMMITTED, si su consulta lee filas mediante recorrido del orden de asignación (uso de páginas IAM), podría perder filas si otra transacción está produciendo una división de página. Esto no puede suceder si utiliza la lectura confirmada porque la tabla se mantiene bloqueada durante la división de la página y no pasa si la tabla no tiene un índice agrupado, porque las actualizaciones no producen divisiones de página.

Tipos de simultaneidad

Cuando varias personas intentan modificar los datos de una base de datos al mismo tiempo, debe implementarse un sistema de controles de forma que las modificaciones realizadas por una persona no afecten negativamente a las de otra. Esto se denomina control de simultaneidad.

La teoría del control de simultaneidad tiene dos clasificaciones para los métodos que establecen dicho control:

• Control de simultaneidad pesimista

  Un sistema de bloqueos impide que los usuarios modifiquen los datos de forma que afecte a otros usuarios. Cuando un usuario lleve a cabo una acción que da lugar a que se aplique un bloqueo, los demás usuarios no podrán realizar acciones que crearían conflictos con el bloqueo hasta que el propietario lo libere. Esto se denomina control pesimista porque se utiliza principalmente en entornos donde hay muchos conflictos por la obtención de datos, y en los que el coste de la protección de datos con bloqueos es menor que el de revertir las transacciones si se producen conflictos de simultaneidad.

• Control de simultaneidad optimista

  En el control de simultaneidad optimista, los usuarios no bloquean los datos cuando los leen. Cuando un usuario realiza una actualización de datos, el sistema comprueba si otro usuario ha cambiado los datos después de la lectura. Si otro usuario actualizó los datos, se produce un error. Normalmente, el usuario que recibe el error revierte la transacción y comienza de nuevo. Este tipo de control se denomina optimista porque se utiliza principalmente en entornos donde hay pocos problemas de contención por la obtención de datos y en los que el coste de revertir ocasionalmente una transacción es menor que el de bloquear los datos cuando se leen.

SQL Server permite el uso de una serie de controles de simultaneidad. Los usuarios especifican el tipo de control de simultaneidad seleccionando niveles de aislamiento de transacción para las conexiones u opciones de simultaneidad en cursores. Estos atributos se pueden definir mediante instrucciones Transact-SQL o bien mediante las propiedades y los atributos de interfaces de programación de aplicaciones (API) de bases de datos como ADO, ADO.NET, OLE DB y ODBC.

Niveles de aislamiento del motor de base de datos de SQL Server

Las transacciones especifican un nivel de aislamiento que define el grado en que se debe aislar una transacción de las modificaciones de recursos o datos realizadas por otras transacciones. Los niveles de aislamiento se describen en cuanto a los efectos secundarios de la simultaneidad que se permiten, como las lecturas desfasadas o fantasma.

Control de los niveles de aislamiento de transacción:

• Controla si se activan bloqueos cuando se leen los datos y qué tipos de bloqueos se solicitan.
• Duración de los bloqueos de lectura.
• Si una operación de lectura que hace referencia a filas modificadas por otra transacción:
  • Se bloquea hasta que se libera el bloqueo exclusivo de la fila.
  • Recupera la versión confirmada de la fila que existía en el momento en el que empezó la instrucción o la transacción.
  • Lee la modificación de los datos no confirmados.

Importante

La elección de un nivel de aislamiento de transacción no afecta a los bloqueos adquiridos para proteger la modificación de datos. Siempre se obtiene un bloqueo exclusivo en los datos modificados de una transacción, bloqueo que se mantiene hasta que se completa la transacción, independientemente del nivel de aislamiento seleccionado para la misma. En el caso de las operaciones de lectura, los niveles de aislamiento de transacción definen básicamente el nivel de protección contra los efectos de las modificaciones que realizan otras transacciones.

Un nivel de aislamiento menor significa que los usuarios tienen un mayor acceso a los datos simultáneamente, con lo que aumentan los efectos de simultaneidad que pueden experimentar, como las lecturas desfasadas o la pérdida de actualizaciones. Por el contrario, un nivel de aislamiento mayor reduce los tipos de efectos de simultaneidad, pero requiere más recursos del sistema y aumenta las posibilidades de que una transacción bloquee otra. El nivel de aislamiento apropiado depende del equilibrio entre los requisitos de integridad de los datos de la aplicación y la sobrecarga de cada nivel de aislamiento. El nivel de aislamiento superior, que es serializable, garantiza que una transacción recuperará exactamente los mismos datos cada vez que repita una operación de lectura, aunque para ello aplicará un nivel de bloqueo que puede afectar a los demás usuarios en los sistemas multiusuario. El nivel de aislamiento inferior, de lectura sin confirmar, puede recuperar datos modificados pero no confirmados por otras transacciones. En este nivel se pueden producir todos los efectos secundarios de simultaneidad, pero no hay bloqueos ni versiones de lectura, por lo que se minimiza la sobrecarga.

Niveles de aislamiento del motor de base de datos

El estándar ISO define los niveles de aislamiento siguientes, todos ellos compatibles con el motor de base de datos de SQL Server:

Nivel de aislamiento	Definición
Lectura pendiente de confirmación	El nivel más bajo de aislamiento donde se aíslan las transacciones lo suficiente como para garantizar que no se leen datos físicamente dañados. En este nivel, se permiten las lecturas no actualizadas por lo que es posible que una transacción vea cambios que no se han confirmado aún efectuados por otras transacciones.
Lectura confirmada	Permite que una transacción lea los datos previamente leídos (no modificados) por otra transacción, sin tener que esperar a que la primera transacción finalice. El motor de base de datos de SQL Server mantiene los bloqueos de lectura (adquiridos en datos seleccionados) hasta el final de la transacción, pero los bloqueos de lectura se liberan tan pronto se efectúa la operación SELECT. Este es el nivel de bloqueo predeterminado del motor de base de datos de SQL Server.
Lectura repetible	El motor de base de datos de SQL Server mantiene los bloqueos de lectura y escritura adquiridos en datos seleccionados hasta el final de la transacción. Sin embargo, puesto que los bloqueos de rangos no están administrados, pueden darse lecturas fantasma.
Serializable	El nivel más alto, en el que se aíslan completamente las transacciones entre sí. El motor de base de datos de SQL SQL Server mantiene los bloqueos de lectura y escritura adquiridos en datos seleccionados y que se liberarán al final de la transacción. Los bloqueos de rangos se adquieren cuando una operación SELECT utiliza una cláusula WHERE con rango, especialmente para evitar lecturas fantasma. Nota: Al solicitar el nivel de aislamiento serializable se puede producir un error en las operaciones DDL y las transacciones de tablas replicadas. La causa es que en las consultas de replicación se utilizan sugerencias que pueden ser incompatibles con el nivel de aislamiento serializable.

SQL Server también admite dos niveles de aislamiento de transacción adicionales que utilizan versiones de fila. Uno es una implementación de aislamiento de READ COMMITED y el otro un nivel de aislamiento de transacción, la instantánea.

Nivel de aislamiento de versiones de fila	Definición
Instantánea de lectura confirmada (RCSI)	Cuando el valor de la opción de base de datos READ_COMMITTED_SNAPSHOT es ON, el aislamiento de READ COMMITED utiliza las versiones de fila para proporcionar una coherencia de lectura en las instrucciones. Las operaciones de lectura solo requieren bloqueos de tablas SCH-S, pero no bloqueos de páginas ni filas. Es decir, el motor de base de datos de SQL Server utiliza versiones de fila para presentar a cada instrucción una instantánea coherente, desde el punto de vista transaccional, de los datos tal como se encontraban al comenzar la instrucción. No se emplean bloqueos para impedir que otras transacciones actualicen los datos. Una función definida por el usuario puede devolver datos confirmados después del inicio de la instrucción que contiene que la UDF. Cuando la opción de base de datos READ_COMMITTED_SNAPSHOT está establecida en OFF, que es el valor de configuración predeterminado, el aislamiento de READ COMMITED utiliza bloqueos compartidos para evitar que otras transacciones modifiquen filas mientras la transacción actual está ejecutando una operación de lectura. Los bloqueos compartidos impiden también que la instrucción lea las filas modificadas por otras transacciones hasta que la otra transacción haya finalizado. Ambas implementaciones cumplen la definición ISO del aislamiento de READ COMMITED.
Instantánea	El nivel de aislamiento de instantánea utiliza las versiones de fila para proporcionar una coherencia de lectura en las transacciones. No se adquiere ningún bloqueo de páginas ni filas en las operaciones de lectura, solo los bloqueos de tabla SCH-S. Cuando se leen filas modificadas por otras transacciones, se recupera la versión de la fila que existía cuando empezó la transacción. El aislamiento de instantánea solo se puede utilizar en una base de datos cuando la opción de base de datos ALLOW_SNAPSHOT_ISOLATION está establecida en ON. De forma predeterminada, el valor de esta opción es OFF para las bases de datos de usuarios. Nota: SQL Server no admite el control de versiones de los metadatos. Por ello, hay restricciones en qué operaciones de DDL se puede realizar en una transacción explícita que se está ejecutando bajo el aislamiento de instantánea. Las instrucciones de DDL siguientes no se admiten bajo el aislamiento de instantánea después de una instrucción BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, ni ninguna instrucción de DDL de Common Language Runtime (CLR). Estas instrucciones se permiten cuando usa el aislamiento de la instantánea en transacciones implícitas. Una transacción implícita, por definición, es una instrucción única que permite aplicar la semántica del aislamiento de instantánea, incluso con instrucciones de DDL. Las infracciones de este principio pueden producir el error 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

En la tabla siguiente se muestran los efectos secundarios de la simultaneidad habilitados por los distintos niveles de aislamiento.

Nivel de aislamiento	Lectura desfasada	Lectura no repetible	Fantasma
Lectura pendiente de confirmación	Sí	Sí	Sí
Lectura confirmada	No	Sí	Sí
Lectura repetible	No	No	Sí
Instantánea	No	N.º	No
Serializable	No	N.º	No

Para obtener más información sobre los tipos de bloqueo específicos o las versiones de fila que controlan cada nivel de aislamiento de transacción, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Se pueden establecer los niveles de aislamiento de transacción con Transact-SQL o mediante una API de bases de datos.

Transact-SQL
Los scripts de Transact-SQL usan la instrucción SET TRANSACTION ISOLATION LEVEL.

ADO
Las aplicaciones de ADO establecen la propiedad IsolationLevel del objeto Connection en adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead o adXactReadSerializable.

ADO.NET
ADO.NET aplicaciones que usan el espacio de nombres administrado System.Data.SqlClient pueden llamar al método SqlConnection.BeginTransaction y establecer la opción IsolationLevel en Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable o Snapshot.

OLE DB
Al iniciar una transacción, las aplicaciones que usan la llamada ITransactionLocal::StartTransaction OLE DB con isoLevel establecida en ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT o ISOLATIONLEVEL_SERIALIZABLE.

Al especificar el nivel de aislamiento de transacción en modo de confirmación automática, las aplicaciones OLE DB pueden establecer la propiedad DBPROPSET_SESSIONDBPROP_SESS_AUTOCOMMITISOLEVELS en DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED o DBPROPVAL_TI_SNAPSHOT.

ODBC
Las aplicaciones ODBC llaman a SQLSetConnectAttr con Attribute establecido en SQL_ATTR_TXN_ISOLATION y ValuePtr se establecen en SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ o SQL_TXN_SERIALIZABLE.

En el caso de las transacciones de instantáneas, las aplicaciones llaman a SQLSetConnectAttr con atributo establecido en SQL_COPT_SS_TXN_ISOLATION y ValuePtr se establecen en SQL_TXN_SS_SNAPSHOT. Se puede recuperar una transacción de instantánea mediante SQL_COPT_SS_TXN_ISOLATION o SQL_ATTR_TXN_ISOLATION.

Bloqueo del motor de base de datos

El bloqueo es el mecanismo que utiliza el motor de base de datos de SQL Server para sincronizar el acceso por parte de varios usuarios al mismo elemento de datos simultáneamente.

Antes de que una transacción obtenga una dependencia del estado actual de un elemento de datos, como la lectura o modificación de los datos, debe protegerse de los efectos de otra transacción que modifica los mismos datos. Para ello, la transacción solicita un bloqueo en el elemento de datos. Los bloqueos disponen de diferentes modos, como compartido o exclusivo. El modo del bloqueo indica el nivel de dependencia que la transacción tiene sobre los datos. No se puede conceder a una transacción un bloqueo que genere un conflicto con el modo de un bloqueo ya concedido para unos datos a otra transacción. Si una transacción solicita un modo de bloqueo que cree un conflicto con otro bloqueo ya concedido sobre los mismos datos, la instancia deL motor de base de datos de SQL Server pausará la transacción que realiza la solicitud hasta que se libere el primer bloqueo.

Si una transacción modifica un elemento de datos, conserva ciertos bloqueos que protegen la modificación hasta el final de la transacción. El tiempo que una transacción conserva los bloqueos obtenidos para proteger operaciones de lectura depende de la configuración del nivel de aislamiento de transacción y de si el bloqueo optimizado está habilitado o no.

• Cuando el bloqueo optimizado no está habilitado, los bloqueos de fila y página necesarios para las escrituras se mantienen hasta el final de la transacción.

• Cuando el bloqueo optimizado está habilitado, solo se mantiene un bloqueo de identificador de transacción (TID) mientras dure la transacción. En el nivel de aislamiento predeterminado, las transacciones no contendrán bloqueos de fila y página necesarios para las escrituras hasta el final de la transacción. Esto reduce la memoria de bloqueo necesaria y reduce la necesidad de extensión de bloqueos. Además, cuando se habilita el bloqueo optimizado, la optimización del bloqueo después de la calificación (LAQ) evalúa los predicados de una consulta en la versión confirmada más reciente de la fila sin adquirir un bloqueo, lo que mejora la simultaneidad.

Todos los bloqueos de una transacción se liberan cuando ésta finaliza (se confirma o se revierte).

Por regla general, las aplicaciones no solicitan los bloqueos directamente. Una parte del motor de base de datos de SQL Server, denominada administrador de bloqueos, es la que se encarga de administrar los bloqueos de forma interna. Cuando una instancia del motor de base de datos de SQL Server procesa una instrucción Transact-SQL, el procesador de consultas del motor de base de datos de SQL Server determina a qué recursos se debe tener acceso. El procesador de consultas determina también qué tipos de bloqueos se necesitan para proteger cada recurso, basándose en el tipo de acceso y en la configuración del nivel de aislamiento de la transacción. A continuación, el procesador de consultas solicita los bloqueos adecuados al administrador de bloqueos. Éste concede los bloqueos si no existen bloqueos en conflicto de otras transacciones.

Granularidad y jerarquías de bloqueo

El motor de base de datos de SQL Server admite bloqueo multigranular. Esta función permite que una transacción bloquee diferentes tipos de recursos. Para minimizar el costo del bloqueo, el motor de base de datos de SQL Server bloquea automáticamente los recursos en el nivel apropiado para la tarea. Los bloqueos de menor granularidad, como es el caso de las filas, aumentan la simultaneidad. Sin embargo, se produce una sobrecarga mayor porque cuantas más filas se bloquean, más bloqueos se deben mantener. Los bloqueos realizados en una granularidad alta, por ejemplo en tablas, reducen la simultaneidad porque el bloqueo de toda una tabla restringe el acceso de otras transacciones a cualquier parte de la tabla. Sin embargo, produce una sobrecarga menor debido a que se mantienen menos bloqueos.

El motor de base de datos de SQL Server a menudo se ve en la obligación de adquirir bloqueos en distintos niveles de granularidad para brindar una protección completa a un recurso. Este grupo de bloqueos en distintos niveles de granularidad se denomina jerarquía de bloqueos. Por ejemplo, para brindar protección completa a la lectura de un índice, probablemente sea necesario que una instancia del motor de base de datos de SQL Server adquiera bloqueos preventivos en filas y bloqueos con intención compartida en las páginas y la tabla.

En la siguiente tabla se muestran los recursos que el motor de base de datos de SQL Server puede bloquear.

Resource	Descripción
RID	Identificador de fila que se utiliza para bloquear una sola fila de un montón.
KEY	Bloqueo de fila dentro de un índice que se utiliza para proteger intervalos de claves en transacciones serializables.
PAGE	Página de 8 kilobytes (KB) de una base de datos, como páginas de datos o de índices.
EXTENT	Grupo contiguo de ocho páginas, como páginas de datos o de índices.
HoBT 1	Montón o árbol b. Bloqueo que protege un árbol B (índice) o las páginas de datos del montón en una tabla que no tiene un índice agrupado.
TABLE 1	Tabla completa, con todos los datos e índices.
ARCHIVO	Archivos de la base de datos.
APPLICATION	Recurso especificado por la aplicación.
METADATOS	Bloqueos de metadatos.
ALLOCATION_UNIT	Unidad de asignación.
DATABASE	Base de datos completa.
XACT 2	Bloqueo de id. de transacción (TID) que se usa en el bloqueo optimizado. Consulte Bloqueo del identificador de transacción (TID).

¹ La opción LOCK_ESCALATION de ALTER TABLE puede afectar a los bloqueos HoBT y TABLE.

² Hay recursos de bloqueo adicionales disponibles para los recursos de bloqueo XACT, vea Adiciones de diagnóstico para el bloqueo optimizado.

Modos de bloqueo

El motor de base de datos de SQL Server bloquea los recursos con diferentes modos de bloqueo que determinan el modo en que las transacciones simultáneas pueden tener acceso a los recursos.

En la siguiente tabla se muestran los recursos que los modos de bloqueo de recursos del motor de base de datos de SQL Server usa.

Modo de bloqueo	Descripción
Compartido (S)	Se utiliza para operaciones de lectura que no cambian ni actualizan datos, como la instrucción SELECT.
Actualizado (U)	Se utiliza en recursos que se pueden actualizar. Evita una forma común de interbloqueo que se produce cuando varias sesiones leen, bloquean y actualizan recursos.
Exclusivo (X)	Se utiliza para operaciones de modificación de datos, como INSERT, UPDATE o DELETE. Garantiza que no puedan realizarse varias actualizaciones simultáneamente en el mismo recurso.
Intención	Se utiliza para establecer una jerarquía de bloqueos. Los tipos de bloqueo de intención son: intención compartido (IS), intención exclusivo (IX) y compartido con intención exclusivo (SIX).
Esquema	Se utiliza cuando se ejecuta una operación que depende del esquema de una tabla. Hay dos tipos de bloqueo de esquema: modificación del esquema (Sch-M) y modificación de estabilidad (Sch-S).
Actualización masiva (BU)	Se usa cuando se copian datos de forma masiva en una tabla y se especifica la sugerencia TABLOCK.
Intervalo de claves	Protege el intervalo de filas que lee una consulta cuando se utiliza el nivel de aislamiento de transacciones serializables. Garantiza que otras transacciones no puedan insertar filas que podrían incluirse como respuesta de las consultas de la transacción serializable si las consultas se volvieran a ejecutar.

Bloqueos compartidos

Los bloqueos compartidos (S) permiten que varias transacciones simultáneas lean (SELECT) un recurso en situaciones de control de simultaneidad pesimista. Ninguna otra transacción podrá modificar los datos mientras el bloqueo compartido (S) exista en el recurso. Los bloqueos compartidos (S) en un recurso se liberan tan pronto como finaliza la operación de lectura, a menos que se haya establecido el nivel de aislamiento de la transacción como REPEATABLE READ o más alto, o bien se utilice una sugerencia de bloqueo para mantener los bloqueos compartidos (S) durante la transacción.

Bloqueos de actualización

El motor de base de datos coloca bloqueos de actualización (U) a medida que se prepara para ejecutar una actualización. Los bloqueos de U son compatibles con bloqueos S, pero solo una transacción puede contener un bloqueo de U a la vez en un recurso determinado. Esta es la clave: muchas transacciones simultáneas pueden contener bloqueos S, pero solo una transacción puede contener un bloqueo de U en un recurso. Los bloqueos de actualización (U) se actualizan finalmente a bloqueos exclusivos (X) para actualizar una fila.

Las consultas que no realizan una actualización pueden realizar bloqueos de actualización (U) cuando se especifica la sugerencia de tabla UPDLOCK en la consulta. Es habitual que las aplicaciones usen un patrón "seleccionar una fila y, a continuación, actualizar la fila", donde las lecturas y escrituras se separan explícitamente dentro de la transacción. En este caso, si el nivel de aislamiento es de lectura repetible o serializable, es probable que las actualizaciones simultáneas se interbloqueen. En su lugar, las aplicaciones podrían seguir un patrón "seleccionar una fila con la sugerencia UPDLOCK y, a continuación, actualizar la fila".

• En una transacción de lectura repetible o serializable, la transacción lee los datos, adquiere un bloqueo compartido (S) en el recurso y, a continuación, modifica los datos, lo que requiere una conversión del bloqueo en un bloqueo exclusivo (X). Si dos transacciones adquieren bloqueos compartidos (S) en un recurso y, a continuación, intentan actualizar los datos simultáneamente, una de ellas intenta convertir el bloqueo en un bloqueo exclusivo (X). La conversión de bloqueo compartido en exclusivo debe esperar, ya que el bloqueo exclusivo de una transacción no es compatible con el bloqueo compartido (S) de la otra. Por tanto, se produce una espera de bloqueos. La segunda transacción intenta adquirir un bloqueo exclusivo (X) para realizar su actualización. Debido a que ambas transacciones intentan convertir los bloqueos en exclusivos (X) y cada una espera a que la otra libere su bloqueo de modo compartido (S), se produce un interbloqueo.

• En el nivel de aislamiento predeterminado de lectura confirmada, los bloqueos S son de corta duración, liberados en cuanto se usan. Es poco probable que los bloqueos de duración corta produzcan interbloqueos.

• Si se usa la sugerencia UPDLOCK en una escritura, la transacción debe tener acceso a la última versión de la fila. Si la última versión ya no está visible, se espera que sea posible recibir Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict cuando el aislamiento SNAPSHOT esté en uso. Para ver un ejemplo, consulte Trabajar con aislamiento de instantánea.

Bloqueos exclusivos

Los bloqueos exclusivos (X) evitan que transacciones simultáneas tengan acceso a un recurso. Al utilizar un bloqueo exclusivo (X), el resto de las transacciones no pueden modificar los datos; las operaciones de lectura solo se pueden realizar si se utiliza la sugerencia NOLOCK o el nivel de aislamiento de lectura no confirmada.

Las instrucciones para modificar datos, como INSERT, UPDATE y DELETE combinan las operaciones de modificación con las de lectura. En primer lugar, la instrucción lleva a cabo operaciones de lectura para adquirir los datos antes de proceder a ejecutar las operaciones de modificación necesarias. Por tanto, las instrucciones de modificación de datos suelen solicitar bloqueos compartidos y exclusivos. Por ejemplo, una instrucción UPDATE puede modificar las filas de una tabla a partir de una combinación con otra tabla. En este caso, la instrucción UPDATE solicita bloqueos compartidos para la filas leídas en la tabla de combinación, además de bloqueos exclusivos para las filas actualizadas.

Bloqueos preventivos

El motor de base de datos de SQL Server utiliza bloqueos preventivos para proteger la aplicación de un bloqueo compartido (S) o exclusivo (X) en un recurso inferior en la jerarquía de bloqueos. Los “bloqueos con intención” se denominan así porque se adquieren antes que los bloqueos de los niveles inferiores y, por lo tanto, señalan la intención de aplicar bloqueos en un nivel inferior.

Los bloqueos con intención se utilizan con dos fines:

• Para evitar que otras transacciones modifiquen el recurso de nivel superior de forma que invaliden el bloqueo del nivel inferior.
• Para mejorar la eficacia del motor de base de datos de SQL Server para detectar conflictos de bloqueo en el nivel superior de granularidad.

Por ejemplo, un bloqueo con intención compartida para el nivel de tabla se solicita antes que los bloqueos compartidos (S) para las páginas o filas de la tabla. Establecer un bloqueo con intención en una tabla evita que otra transacción adquiera un bloqueo exclusivo (X) para la tabla que contiene esa página. Los bloqueos con intención mejoran el rendimiento, porque el motor de base de datos de SQL Server examina los bloqueos con intención solo en el nivel de tabla para determinar si una transacción puede adquirir un bloqueo de dicha tabla de forma segura. Esto elimina la necesidad de examinar cada bloqueo de fila o de página de la tabla para determinar si una transacción puede bloquear toda la tabla.

Los bloqueos con intención incluyen: Intención compartida (IS), Intención exclusiva (IX) e Intención compartida exclusiva (SIX).

Modo de bloqueo	Descripción
Intención compartida (IS)	Protege los bloqueos compartidos solicitados o adquiridos de algunos recursos (aunque no todos) situados en un nivel inferior de la jerarquía.
Intención exclusiva (IX)	Protege los bloqueos exclusivos solicitados o adquiridos de algunos recursos (aunque no todos) situados en un nivel inferior de la jerarquía. IX es un superconjunto de IS, y protege las solicitudes de bloqueos compartidos en recursos de niveles inferiores.
Compartido con intención exclusiva (SIX)	Protege los bloqueos compartidos solicitados o adquiridos de todos los recursos situados en un nivel inferior de la jerarquía y los bloqueos con intención exclusiva de algunos (aunque no todos) los recursos de niveles inferiores. Se permiten los bloqueos IS simultáneos en el recurso de nivel superior. Por ejemplo, al adquirir un bloqueo SIX para una tabla, también se adquieren bloqueos con intención exclusiva de las páginas que se modifican y bloqueos exclusivos de las filas modificadas. Solo puede haber un bloqueo SIX simultáneo por recurso, para impedir que otras transacciones lo actualicen, aunque otras transacciones pueden leer los recursos inferiores de la jerarquía obteniendo bloqueos IS en el nivel de tabla.
Actualizar intención (IU)	Protege los bloqueos de actualización solicitados o adquiridos de todos los recursos de niveles inferiores de la jerarquía. Los bloqueos IU solo se utilizan para los recursos de página. Los bloqueos IU se convierten en bloqueos IX cuando se ejecutan operaciones de actualización.
Actualizar intención compartida (SIU)	Combinación de bloqueos S e IU que resulta de adquirir estos bloqueos por separado y de mantenerlos simultáneamente. Por ejemplo, sería el caso de una transacción que ejecuta una consulta con la sugerencia PAGLOCK y luego ejecuta una operación de actualización. La consulta con la sugerencia PAGLOCK adquiere el bloqueo S y la operación de actualización, el bloqueo IU.
Actualizar intención exclusiva (UIX)	Combinación de bloqueos U e IX que resulta de adquirir estos bloqueos por separado y de mantenerlos simultáneamente.

Bloqueos de esquema

El motor de base de datos de SQL Server utiliza bloqueos de modificación del esquema (Sch-M) cuando se realiza una operación de lenguaje de definición de datos (DDL) en tablas como, por ejemplo, agregar una columna o quitar una tabla. Mientras se conserva, el bloqueo Sch-M evita el acceso simultáneo a la tabla. Esto significa que el bloqueo Sch-M bloquea todas las operaciones externas hasta que el bloqueo se libera.

Algunas operaciones del lenguaje de manipulación de datos (DML), como el truncamiento de tablas, utilizan los bloqueos Sch-M para impedir el acceso a las tablas afectadas por operaciones simultáneas.

El motor de base de datos de SQL Server usa bloqueos de estabilidad del esquema (Sch-S) al compilar y ejecutar consultas. Los bloqueos Sch-S no impiden los bloqueos de transacciones, incluidos los bloqueos exclusivos (X). Por tanto, otras transacciones, incluidas las que tienen bloqueos X de una tabla, pueden seguir ejecutándose mientras se compila una consulta. No obstante, en la tabla no se pueden realizar operaciones DDL simultáneas ni operaciones DML simultáneas que adquieren bloqueos Sch-M.

Bloqueos de actualización masiva

Los bloqueos de actualización masiva (BU) permiten que varios subprocesos copien datos de forma masiva y simultánea en la misma tabla, pero impiden que otros procesos que no están copiando datos de forma masiva tengan acceso a la tabla. El motor de base de datos de SQL Server utiliza bloqueos de actualización masiva cuando las dos condiciones siguientes son verdaderas.

• Está usando la instrucción Transact-SQL BULK INSERT o la función OPENROWSET(BULK), o está usando uno de los comandos de API Bulk Insert como .NET SqlBulkCopy, las API de carga rápida de OLEDB o las API de copia masiva de ODBC para copiar de forma masiva datos en una tabla.
• Se especifica la sugerencia TABLOCK o se establece la opción de tabla table lock on bulk load con sp_tableoption.

Sugerencia

A diferencia de la instrucción BULK INSERT, que contiene un bloqueo Bulk Update (BU) menos restrictivo, INSERT INTO…SELECT con la sugerencia TABLOCK retiene un bloqueo de intención exclusiva (IX) en la tabla. Esto significa que no se pueden insertar filas mediante operaciones de inserción en paralelo.

Bloqueos de intervalo de claves

Los bloqueos de rangos con clave protegen un intervalo de filas incluido implícitamente en un conjunto de registros que se lee con una instrucción Transact-SQL mientras se utiliza el nivel de aislamiento de transacción serializable. El bloqueo de intervalos con clave impide las lecturas fantasma. Al proteger los intervalos de claves entre filas, también se evitan inserciones o eliminaciones fantasma en los conjuntos de registros a los que obtienen acceso las transacciones.

Compatibilidad de bloqueos

La compatibilidad de bloqueos controla si varias transacciones pueden adquirir bloqueos sobre el mismo recurso a la vez. Si un recurso ya está bloqueado por otra transacción, solo se puede conceder una nueva solicitud de bloqueo si el bloqueo solicitado es compatible con el modo del bloqueo existente. Si el modo del bloqueo solicitado no es compatible con el bloqueo existente, la transacción que solicita el nuevo bloqueo espera a que se libere el existente o a que expire el intervalo de tiempo de espera del bloqueo. Por ejemplo, ningún modo de bloqueo es compatible con bloqueos exclusivos. Mientras se mantiene un bloqueo exclusivo (X), ninguna otra transacción puede adquirir un bloqueo de ninguna clase (compartido, de actualización o exclusivo) en dicho recurso hasta que se libere el bloqueo exclusivo. Como alternativa, si se ha aplicado un bloqueo compartido (S) a un recurso, otras transacciones también pueden adquirir un bloqueo compartido o de actualización (U) en el elemento, aunque la primera transacción no haya terminado. Sin embargo, otras transacciones no pueden adquirir un bloqueo exclusivo si no se anula el bloqueo compartido.

En la tabla siguiente se muestra la compatibilidad de los modos de bloqueo más frecuentes.

Modo concedido existente	IS	S	U	IX	SIX	X
Modo solicitado
Intención compartida (IS)	Sí	Sí	Sí	Sí	Sí	No
Compartido (S)	Sí	Sí	Sí	No	N.º	No
Actualizado (U)	Sí	Sí	No	N.º	N.º	No
Intención exclusiva (IX)	Sí	No	No	Sí	No	No
Compartido con intención exclusiva (SIX)	Sí	No	N.º	N.º	N.º	No
Exclusivo (X)	No	N.º	N.º	N.º	N.º	N.º

Nota:

Un bloqueo con intención exclusivo (IX) es compatible con un modo de bloqueo IX, porque IX indica la intención de actualizar solamente algunas de las filas, no todas. También se permite que otras transacciones intenten leer o actualizar algunas filas, siempre y cuando no se trate de las mismas filas que están actualizando las demás transacciones. Además, si dos transacciones intentan actualizar la misma fila, se permitirá a ambas transacciones un bloqueo IX en el nivel de tabla y de página. Sin embargo, un bloqueo X en el nivel de fila solo se permitirá a una transacción. La otra transacción deberá esperar a que se quite el bloqueo en el nivel de fila.

Utilice la siguiente tabla para determinar la compatibilidad de todos los modos de bloqueo disponibles en SQL Server.

Bloqueo de intervalos con clave

Los bloqueos de rangos con clave protegen un intervalo de filas incluido implícitamente en un conjunto de registros que se lee con una instrucción Transact-SQL mientras se utiliza el nivel de aislamiento de transacción serializable. El nivel de aislamiento serializable requiere que las consultas ejecutadas durante una transacción deben obtener el mismo conjunto de filas cada vez que se ejecutan en la transacción. El bloqueo de intervalos con clave protege este requisito, ya que impide que otras transacciones inserten nuevas filas cuyas claves se incluirían en el intervalo de claves leído por la transacción serializable.

El bloqueo de intervalos con clave impide las lecturas fantasma. La protección de los intervalos de claves entre filas también impide las inserciones fantasma en un conjunto de registros a los que tiene acceso una transacción.

El bloqueo de intervalos con clave se incluye en un índice, especificando los valores de clave inicial y final. Este bloqueo impide la inserción, actualización o eliminación de filas con un valor de clave incluido en el intervalo, ya que estas operaciones deben obtener en primer lugar un bloqueo en el índice. Por ejemplo, una transacción serializable podría emitir una instrucción SELECT que lee todas las filas cuyos valores clave coincidan con la condición BETWEEN 'AAA' AND 'CZZ'. El bloqueo de intervalos con clave en los valores de clave del intervalo 'AAA' a 'CZZ' impide que otras transacciones inserten filas con valores de clave situados en dicho intervalo, como 'ADG', 'BBD' o 'CAL'.

Modos de bloqueo de intervalos con clave

Los bloqueos de intervalos con clave incluyen dos componentes, una fila y un intervalo, especificados con el formato intervalo-fila:

• El intervalo representa el modo de bloqueo que protege el intervalo entre dos entradas de índice consecutivas.
• La fila representa el modo de bloqueo que protege la entrada de índice.
• El modo representa el modo de bloqueo combinado que se utiliza. Los modos de bloqueo del intervalo de claves constan de dos partes. La primera representa el tipo de bloqueo que se utiliza para bloquear el intervalo del índice (RangeT) y la segunda representa el tipo de bloqueo que se utiliza para bloquear una clave específica (K). Ambas partes se conectan con un guion (-), como RangeT-K.

Intervalo	Row	Mode	Descripción
RangeS	S	RangeS-S	Intervalo compartido, bloqueo de recurso compartido; recorrido de intervalo serializable.
RangeS	U	RangeS-U	Intervalo compartido, bloqueo de recurso de actualización; recorrido de actualización serializable.
RangeI	Null	RangeI-N	Intervalo de inserción, bloqueo de recurso nulo; se utiliza para comprobar los intervalos antes de insertar una nueva clave en un índice.
RangeX	X	RangeX-X	Intervalo exclusivo, bloqueo de recurso exclusivo; se utiliza al actualizar una clave de un intervalo.

Nota:

El modo de bloqueo Null interno es compatible con los demás modos de bloqueo.

Los modos de bloqueo de intervalos con clave tienen una matriz de compatibilidad que muestra los bloqueos que son compatibles con otros bloqueos obtenidos en claves e intervalos superpuestos.

Modo concedido existente	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Modo solicitado
Compartido (S)	Sí	Sí	No	Sí	Sí	Sí	No
Actualizado (U)	Sí	No	No	Sí	No	Sí	No
Exclusivo (X)	No	N.º	N.º	N.º	No	Sí	No
RangeS-S	Sí	Sí	No	Sí	Sí	No	No
RangeS-U	Sí	No	No	Sí	No	N.º	No
RangeI-N	Sí	Sí	Sí	No	No	Sí	No
RangeX-X	No	N.º	N.º	N.º	N.º	N.º	No

Bloqueos de conversión

Los bloqueos de conversión se crean cuando un bloqueo de intervalos con clave se superpone a otro bloqueo.

Bloqueo 1	Bloqueo 2	Bloqueo de conversión
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

Los bloqueos de conversión se producen durante breves períodos de tiempo en circunstancias diversas y complejas, y en ocasiones mientras se ejecutan procesos simultáneos.

Recorrido de intervalo serializable, captura de singleton, eliminación e inserción

El bloqueo de intervalos con clave garantiza que las siguientes operaciones son serializables:

• Consulta de recorrido de intervalos
• Captura de singleton de fila inexistente
• Operación de eliminación
• Operación de inserción

Para que el bloqueo de intervalos con clave se produzca, es necesario que se cumplan las condiciones siguientes:

• El nivel de aislamiento de las transacciones se debe establecer en SERIALIZABLE.
• El procesador de consultas debe utilizar un índice para implementar el predicado del filtro de intervalo. Por ejemplo, la cláusula WHERE de una instrucción SELECT puede establecer una condición de intervalo con este predicado: ColumnX BETWEEN N**'AAA'** AND N**'CZZ'**. El bloqueo de intervalos con clave solo se puede adquirir si una clave de índice abarca ColumnX.

Ejemplos

La tabla y el índice siguientes se utilizan como base para los ejemplos de bloqueo de intervalos con clave que se muestran a continuación.

Consulta de recorrido de intervalos

Para poder asegurar que una consulta de recorrido de intervalos es serializable, la misma consulta debe devolver los mismos resultados cada vez que se ejecuta en la misma transacción. Otras transacciones no deben insertar nuevas filas en la consulta de recorrido de intervalos; de lo contrario, se convierten en inserciones fantasma. Por ejemplo, la siguiente consulta utiliza la tabla y el índice de la ilustración anterior:

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

Los bloqueos de intervalos con clave se colocan en las entradas de índice que se corresponden al intervalo de filas de datos cuyo nombre se encuentra entre los valores Adam y Dale, lo que impide que se agreguen o eliminen nuevas filas obtenidas en la consulta anterior. Aunque el primer nombre del intervalo es Adam, el bloqueo de intervalos con clave RangeS-S en esta entrada de índice garantiza que no se pueden agregar nombres nuevos que empiecen por la letra A delante de Adam, como Abigail. De forma similar, el bloqueo de intervalos con clave RangeS-S en la entrada de índice de Dale garantiza que no se van a agregar nombres nuevos que empiecen por la letra C detrás de Carlos, como Clive.

Nota:

El número de bloqueos RangeS-S que se mantiene es n+1, siendo n el número de filas que satisfacen la consulta.

Captura de singleton de datos inexistentes

Si una consulta de una transacción intenta seleccionar una fila que no existe, la ejecución de la consulta en un punto posterior de la misma transacción tiene que devolver el mismo resultado. No se puede permitir a otra transacción insertar la fila inexistente. Por ejemplo, con esta consulta:

SELECT name
FROM mytable
WHERE name = 'Bill';

Se aplica un bloqueo de intervalos con clave a la entrada de índice correspondiente al intervalo de nombres comprendido entre Ben y Bing, ya que se podría insertar el nombre Bill entre estas dos entradas de índice adyacentes. El bloqueo de intervalos con clave del modo RangeS-S se coloca en la entrada de índice Bing. Esto impide que otra transacción inserte valores, como Bill, entre las entradas de índice Ben y Bing.

Operación de eliminación, sin bloqueo optimizado

Cuando se elimina un valor en una transacción, el intervalo en el que entra el valor no debe estar bloqueado mientras se ejecuta la transacción que realiza la operación de eliminación. Para mantener la seriabilidad basta con bloquear el valor de la clave eliminada hasta el final de la transacción. Por ejemplo, con esta instrucción DELETE:

DELETE mytable
WHERE name = 'Bob';

Se ha colocado un bloqueo exclusivo (X) en la entrada de índice correspondiente al nombre Bob. Otras transacciones pueden insertar o eliminar valores antes o después del valor eliminado Bob. Sin embargo, cualquier transacción que intente leer, insertar o eliminar el valor Bob se bloqueará hasta que la transacción de eliminación se confirme o se revierta. (La opción de base de datos READ_COMMITTED_SNAPSHOT y el nivel de aislamiento SNAPSHOT también permiten lecturas de una versión de fila del estado confirmado previamente).

La eliminación del intervalo se puede ejecutar con tres modos de bloqueo básicos: bloqueo de fila, de página o de tabla. El optimizador de consultas decide la estrategia de bloqueo de página, tabla o fila, o bien la especifica el usuario mediante sugerencias del optimizador como ROWLOCK, PAGLOCK o TABLOCK. Cuando se utiliza PAGLOCK o TABLOCK, el motor de base de datos de SQL Server anula de forma inmediata la asignación de una página de índice si se eliminan todas sus filas. Por el contrario, cuando se utiliza ROWLOCK, todas las filas eliminadas se marcan solo como eliminadas, y se quitan de la página de índice posteriormente mediante una tarea en segundo plano.

Operación de eliminación, con bloqueo optimizado

Al eliminar un valor dentro de una transacción, los bloqueos de fila y página se adquieren y liberan incrementalmente, y no se mantienen durante la duración de la transacción. Por ejemplo, con esta instrucción DELETE:

DELETE mytable
WHERE name = 'Bob';

Un bloqueo TID se coloca en todas las filas modificadas durante la transacción. Se adquiere un bloqueo en el TID de las entradas de índice correspondientes al nombre Bob. Con el bloqueo optimizado, los bloqueos de páginas y filas siguen siendo adquiridos para las actualizaciones, pero cada página y bloqueo de fila se liberan en cuanto se actualiza cada fila. El bloqueo TID protege las filas de que se actualizan hasta que se completa la transacción. Cualquier transacción que intente leer, insertar o eliminar el valor Bob se bloqueará hasta que la transacción de eliminación se confirme o se revierta. (La opción de base de datos READ_COMMITTED_SNAPSHOT y el nivel de aislamiento SNAPSHOT también permiten lecturas de una versión de fila del estado confirmado previamente).

De lo contrario, la mecánica de bloqueo de una operación de eliminación es la misma que sin bloqueo optimizado.

Operación de inserción sin bloqueo optimizado

Cuando se inserta un valor en una transacción, el intervalo en el que entra el valor no debe estar bloqueado mientras se ejecuta la transacción que realiza la operación de inserción. Basta con bloquear el valor de clave insertado hasta el final de la transacción para mantener la seriabilidad. Por ejemplo, con esta instrucción INSERT:

INSERT mytable VALUES ('Dan');

El bloqueo de intervalos con clave de modo RangeI-N se coloca en la entrada de índice correspondiente al nombre David para probar el intervalo. Si se concede el bloqueo, se inserta Dan y se coloca un bloqueo exclusivo (X) en el valor Dan. El bloqueo de intervalos con clave de modo RangeI-N solo es necesario para probar el intervalo y no se mantiene mientras se ejecuta la transacción que realiza la operación de inserción. Otras transacciones pueden insertar o eliminar valores antes o después del valor insertado Dan. Sin embargo, cualquier transacción que intente leer, insertar o eliminar el valor Dan se bloqueará hasta que se confirme o se revierta la transacción de inserción.

Operación de inserción con bloqueo optimizado

Cuando se inserta un valor en una transacción, el intervalo en el que entra el valor no debe estar bloqueado mientras se ejecuta la transacción que realiza la operación de inserción. Los bloqueos de fila y página rara vez se adquieren, solo cuando hay una recompilación de índices en línea en curso o cuando hay transacciones serializables en la instancia. Si se adquieren bloqueos de fila y página, se liberan rápidamente y no se mantienen durante la transacción. Basta con colocar un bloqueo TID exclusivo en el valor de clave insertado hasta el final de la transacción para mantener la seriabilidad. Por ejemplo, con esta instrucción INSERT:

INSERT mytable VALUES ('Dan');

Con el bloqueo optimizado, solo se adquiere un bloqueo RangeI-N si hay al menos una transacción que usa el nivel de aislamiento SERIALIZABLE en la instancia. El bloqueo de intervalos con clave de modo RangeI-N se coloca en la entrada de índice correspondiente al nombre David para probar el intervalo. Si se concede el bloqueo, se inserta Dan y se coloca un bloqueo exclusivo (X) en el valor Dan. El bloqueo de intervalos con clave de modo RangeI-N solo es necesario para probar el intervalo y no se mantiene mientras se ejecuta la transacción que realiza la operación de inserción. Otras transacciones pueden insertar o eliminar valores antes o después del valor insertado Dan. Sin embargo, cualquier transacción que intente leer, insertar o eliminar el valor Dan se bloqueará hasta que se confirme o se revierta la transacción de inserción.

Extensión de bloqueo

El proceso de extensión de bloqueo consiste en convertir muchos bloqueos específicos en un número menor de bloqueos más generales, lo que reduce la sobrecarga del sistema además de mejorar la probabilidad de contención de simultaneidad.

La extensión de bloqueo se comporta de forma diferente en función de si el bloqueo optimizado está habilitado.

Extensión de bloqueos sin bloqueo optimizado

A medida que el motor de base de datos de SQL Server adquiere bloqueos de bajo nivel, también coloca bloqueos preventivos en los objetos que contienen los objetos de nivel más bajo:

• Al bloquear filas o intervalos de clave de índice, el motor de base de datos coloca un bloqueo preventivo en las páginas que contienen las filas o claves.
• Al bloquear páginas, el motor de base de datos coloca un bloqueo preventivo en los objetos de más alto nivel que contienen las páginas. Además del bloqueo preventivo en el objeto, se solicitan bloqueos de página de intención en los objetos siguientes:
  • Páginas de nivel hoja de índices no agrupados
  • Páginas de datos de índices agrupados
  • Páginas de datos del montón

Es posible que el motor de base de datos realice bloqueos de fila y página para una misma instrucción a fin de minimizar el número de bloqueos y reducir la probabilidad de que sea necesario realizar la extensión de bloqueo. Por ejemplo, el Motor de base de datos podría colocar bloqueos de página en un índice no agrupado (si se seleccionan suficientes claves contiguas del nodo del índice como para satisfacer la consulta) y bloqueos de fila en los datos.

Para extender los bloqueos, el motor de base de datos intenta cambiar el bloqueo preventivo de la tabla por el correspondiente bloqueo completo; por ejemplo, cambiar un bloqueo preventivo exclusivo (IX) por un bloqueo exclusivo (X), o bien un bloqueo preventivo compartido (IS) por un bloqueo compartido (S). Si el intento de extensión de bloqueo se realiza correctamente y se adquiere el bloqueo de tabla completa, se liberan todos los bloqueos de montículo o árbol B, página (PAGE) o de nivel de fila (RID) mantenidos por la transacción en el montón o índice. Si no se puede adquirir el bloqueo completo, no se produce ninguna extensión de bloqueo en el momento y el Motor de base de datos continúa para adquirir bloqueos de fila, clave o página.

El motor de base de datos no escala los bloqueos de fila o de intervalo de claves a bloqueos de páginas, sino que lo hace directamente a bloqueos de tabla. Del mismo modo, los bloqueos de página siempre se escalan a los bloqueos de tabla. El bloqueo de tablas con particiones se puede extender al nivel HoBT para la partición asociada en lugar de hacerlo al bloqueo de la tabla. Un bloqueo de nivel de HoBT no bloquea necesariamente los HoBt alineados para la partición.

Nota:

Los bloqueos de nivel de HoBt suelen aumentar la simultaneidad, pero presentan la posibilidad de interbloqueos cuando las transacciones que bloquean diferentes particiones cada una quieren expandir sus bloqueos exclusivos a las demás particiones. En raras ocasiones, la granularidad de bloqueo de TABLA podría funcionar mejor.

Si no se produce un intento de extensión de bloqueo debido a conflictos de bloqueo retenidos por transacciones simultáneas, el motor de base de datos intentará realizar de nuevo la extensión de bloqueo por cada 1 250 bloqueos adicionales adquiridos por la transacción.

Cada evento de extensión funciona principalmente en una sola instrucción Transact-SQL. Cuando el evento se inicia, EL motor de base de datos intenta extender todos los bloqueos propiedad de la transacción actual que se encuentren en alguna de las tablas a las que se hace referencia en la instrucción activa, siempre que cumpla los requisitos de umbral de extensión. Si el evento de escalación se inicia antes de que la instrucción haya accedido a una tabla, no se intentará escalar los bloqueos de esa tabla. Si la extensión de bloqueo se realiza correctamente, los bloqueos adquiridos por la transacción en una instrucción anterior y se mantienen en el momento en que se inicia el evento se escalarán si la instrucción actual hace referencia a la tabla y se incluye en el evento de escalación.

Por ejemplo, supongamos que una sesión realiza estas operaciones:

• Inicia una transacción.
• Actualiza TableA. Esto genera bloqueos de fila exclusivos en TableA que se mantienen hasta que se completa la transacción.
• Actualiza TableB. Esto genera bloqueos de fila exclusivos en TableB que se mantienen hasta que se completa la transacción.
• Realiza una operación SELECT que combina TableA con TableC. El plan de ejecución de la consulta llama a las filas que se van a recuperar de TableA antes de que se recuperen las de TableC.
• La instrucción SELECT desencadena la extensión de bloqueo mientras recupera filas de TableA y antes de acceder a TableC.

Si la extensión de bloqueo se realiza correctamente, solo se extienden los bloqueos retenidos por la sesión en TableA. Esto incluye los bloqueos compartidos de la instrucción SELECT y los bloqueos exclusivos de la instrucción UPDATE anterior. Aunque para determinar si se debe realizar la extensión de bloqueo solo se cuentan los bloqueos que la sesión ha adquirido en TableA para la instrucción SELECT, cuando la extensión se realiza correctamente, todos los bloqueos retenidos por la sesión en TableA se extienden a un bloqueo exclusivo en la tabla y se liberan los demás bloqueos de granularidad inferior, incluidos los bloqueos preventivos, de TableA.

No se intenta extender los bloqueos de TableB porque no había una referencia activa a TableB en la instrucción SELECT. De manera similar, no se intenta extender los bloqueos de TableC, ya que no están extendidos porque todavía no se había accedido a esta tabla cuando se produjo la extensión.

Extensión de bloqueos con bloqueo optimizado

El bloqueo optimizado ayuda a reducir la memoria de bloqueo, ya que se mantienen muy pocos bloqueos durante la transacción. A medida que el motor de base de datos de SQL Server adquiere bloqueos de fila y página, la extensión de bloqueos puede producirse de forma similar, pero con mucha menos frecuencia. Normalmente, el bloqueo optimizado se realiza correctamente para evitar extensiones de bloqueo, lo que reduce el número de bloqueos y la cantidad de memoria de bloqueo necesaria.

Cuando el bloqueo optimizado está habilitado y, en el nivel de aislamiento READ COMMITED predeterminado, el motor de base de datos libera los bloqueos de fila y página en cuanto se completa la escritura. No se mantienen bloqueos de fila y página durante la transacción, excepto un único bloqueo de identificador de transacción (TID). Esto reduce la probabilidad de una extensión de bloqueo.

Umbrales de extensión de bloqueo

La extensión de bloqueo se desencadena cuando esa operación no se ha deshabilitado en la tabla mediante la opción ALTER TABLE SET LOCK_ESCALATION y cuando se da cualquiera de las condiciones siguientes:

• Una sola instrucción Transact-SQL adquiere al menos 5 000 bloqueos en un solo índice o tabla sin particiones.
• Una sola instrucción Transact-SQL adquiere al menos 5 000 bloqueos en una sola partición de una tabla o índice con particiones, y la opción ALTER TABLE SET LOCK_ESCALATION está establecida en AUTO.
• El número de bloqueos en una instancia del motor de base de datos supera los umbrales de memoria o de configuración.

Si los bloqueos no se pueden extender debido a conflictos de bloqueo, el motor de base de datos desencadena periódicamente la extensión de bloqueo cada 1 250 nuevos bloqueos adquiridos.

Umbral de escalación para una instrucción de Transact-SQL

Cuando el motor de base de datos comprueba si hay posibles extensiones cada 1 250 bloqueos recién adquiridos, se producirá una extensión de bloqueo solo si una instrucción Transact-SQL ha adquirido al menos 5 000 bloqueos en una única referencia de una tabla. La extensión de bloqueo se desencadena cuando una instrucción Transact-SQL adquiere al menos 5 000 bloqueos en una única referencia de una tabla. Por ejemplo, la extensión de bloqueo no se desencadena si una instrucción adquiere 3 000 bloqueos en un índice y 3 000 bloqueos en otro índice de la misma tabla. Del mismo modo, la extensión de bloqueo no se desencadena si una instrucción tiene una autocombinación en una tabla y cada referencia a la tabla solo adquiere 3 000 bloqueos en la tabla.

La extensión de bloqueo solo se produce para las tablas a las que se ha accedido en el momento en que se desencadena la extensión. Imagine que una única instrucción SELECT es una combinación que accede a tres tablas por este orden: TableA, TableB y TableC. La instrucción adquiere 3 000 bloqueos de fila del índice agrupado de TableA y, al menos, 5 000 bloqueos de fila del índice agrupado de TableB, pero todavía no ha accedido a TableC. Cuando el motor de base de datos detecta que la instrucción ha adquirido al menos 5 000 bloqueos de fila de TableB, intenta extender todos los bloqueos retenidos por la transacción actual en TableB. También intenta extender todos los bloqueos retenidos por la transacción actual en TableA, pero como el número de bloqueos de TableA es inferior a 5 000, no se realiza la extensión. No se intenta ninguna extensión de bloqueo en TableC porque todavía no se había accedido a ella cuando se produjo la extensión.

Umbral de escalación para una instancia del motor de base de datos

Siempre que el número de bloqueos sea mayor que el umbral de memoria para la extensión de bloqueo, el motor de base de datos desencadena la extensión de bloqueo. El umbral de memoria depende del valor de la opción de configuración locks:

• Si la opción locks se establece en el valor predeterminado de 0, el umbral de extensión de bloqueo se alcanza cuando la memoria que usan los objetos del bloqueo es un 24 % de la que usa el Motor de base de datos, sin contar la memoria AWE. La estructura de datos utilizada para representar un bloqueo es de aproximadamente 100 bytes de longitud. Este umbral es dinámico debido a que el Motor de base de datos adquiere y libera memoria de forma dinámica para ajustarse a las variaciones de las cargas de trabajo.

• Si la opción locks es un valor distinto de 0, el umbral de extensión de bloqueo es 40 % (o menos si existe presión de memoria) del valor de la opción.

El motor de base de datos puede elegir acción cualquier instrucción activa de cualquier sesión para la extensión; además, por cada 1 250 nuevos bloqueos, elegirá instrucciones para la extensión siempre y cuando la memoria de bloqueo utilizada en la instancia se mantenga por encima del umbral.

Escalar tipos de bloqueo mixtos

Cuando se produce la extensión de bloqueo, el bloqueo seleccionado para el montón o el índice es lo suficientemente seguro como para cumplir los requisitos del bloqueo de nivel inferior más restrictivo.

Por ejemplo, supongamos una sesión:

• Inicia una transacción.
• Actualiza una tabla que contiene un índice agrupado.
• Emite una instrucción SELECT que hace referencia a la misma tabla.

La instrucción UPDATE adquiere estos bloqueos:

• Bloqueos exclusivos (X) en las filas de datos actualizadas.
• Bloqueos exclusivos de intención (IX) en las páginas de índice agrupadas que contienen esas filas.
• Un bloqueo IX en el índice agrupado y otro en la tabla.

La instrucción SELECT adquiere estos bloqueos:

• Los bloqueos compartidos (S) en todas las filas de datos que lee, a menos que la fila ya esté protegida por un bloqueo X de la instrucción UPDATE.
• El boqueo preventivo actúa en todas las páginas de índice agrupadas que contienen esas filas, a menos que la página ya esté protegida por un bloqueo IX.
• No hay ningún bloqueo en el índice agrupado o la tabla porque ya están protegidos por bloqueos IX.

Si la instrucción SELECT adquiere suficientes bloqueos para desencadenar la extensión de bloqueo y la extensión se realiza correctamente, el bloqueo IX de la tabla se convierte en un bloqueo X y se liberan todos los bloqueos de fila, página e índice. Tanto las actualizaciones como las lecturas están protegidas por el bloqueo X de la tabla.

Reducción de bloqueos y escalaciones

En la mayoría de los casos, el motor de base de datos presta el mejor rendimiento cuando funciona con la configuración predeterminada de bloqueo y extensión de bloqueo.

• Aproveche el bloqueo optimizado.

  • El bloqueo optimizado ofrece un mecanismo de bloqueo mejorado que reduce el consumo de memoria de bloqueo y el bloqueo para transacciones simultáneas. Es mucho menos probable que se produzca una extensión de bloqueo cuando el bloqueo optimizado está habilitado.
  • Evite usar sugerencias de tabla con bloqueo optimizado. Las sugerencias de tabla pueden reducir la eficacia del bloqueo optimizado.
  • Habilite READ_COMMITTED_SNAPSHOT en la base de datos para obtener la mayor ventaja del bloqueo optimizado. Es el nivel de aislamiento predeterminado en Azure SQL Database.
  • El bloqueo optimizado requiere la recuperación acelerada de bases de datos (ADR) para habilitarse en la base de datos.

Si una instancia del motor de base de datos genera gran cantidad de bloqueos y se producen extensiones de bloqueo frecuentes, considere la posibilidad de reducir la cantidad de bloqueos con las estrategias siguientes:

• Use un nivel de aislamiento que no genere bloqueos compartidos para las operaciones de lectura:

  • El nivel de aislamiento READ COMMITED con la opción de base de datos READ_COMMITTED_SNAPSHOT establecida en ON.

  • Nivel de aislamiento SNAPSHOT.

  • Nivel de aislamiento READ UNCOMMITTED. Esto solo se puede usar para sistemas que pueden funcionar con lecturas de datos sucios.

    Nota:

    Cambiar el nivel de aislamiento afecta a todas las tablas en la instancia del motor de base de datos.

• Use las sugerencias de tabla PAGLOCK o TABLOCK para que el motor de base de datos utilice bloqueos de página, montón o índice en lugar de bloqueos de bajo nivel. Sin embargo, el uso de esta opción aumenta los problemas de los usuarios que bloquean a otros usuarios que intentan acceder a los mismos datos y no deben usarse en sistemas con más de unos pocos usuarios simultáneos.

• Cuando el bloqueo optimizado no está habilitado, para las tablas con particiones, use la opción LOCK_ESCALATION de ALTER TABLE para escalar los bloqueos al nivel HoBT en lugar de hacerlo al nivel de la tabla, o bien para deshabilitar la extensión de bloqueo.

• Divida las operaciones en lote grandes en varias operaciones más pequeñas. Por ejemplo, imagine que ha ejecutado la consulta siguiente para quitar varios cientos de miles de registros antiguos de una tabla de auditoría y, después, ha comprobado que se ha producido una extensión de bloqueo que ha bloqueado a otros usuarios:

  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  

  Si quita varios cientos de estos registros cada vez, puede reducir drásticamente el número de bloqueos que se acumulan por transacción y evitar la extensión de bloqueo. Por ejemplo:

  SET ROWCOUNT 500
  delete_more:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  IF @@ROWCOUNT > 0 GOTO delete_more
  SET ROWCOUNT 0
  

• Reduzca la superficie de bloqueo de una consulta; para ello, intente que la consulta sea lo más eficaz posible. Los recorridos grandes o un gran número de búsquedas de marcadores pueden aumentar la posibilidad de que se necesite la extensión de bloqueo; además, aumenta la posibilidad de los interbloqueos y, por lo general, afecta de manera negativa a la simultaneidad y el rendimiento. Después de encontrar la consulta que provoca la extensión de bloqueo, busque oportunidades para crear índices o agregar columnas a un índice existente a fin de quitar los recorridos de índice o de tabla, y maximizar la eficacia de las búsquedas de índice. Considere la posibilidad de usar el Asistente para la optimización de motor de base de datos para realizar un análisis automático de índices en la consulta. Para obtener más información, vea Tutorial: Asistente para la optimización de motor de base de datos. Un objetivo de esta optimización es hacer que las búsquedas de índice devuelvan el menor número posible de filas para minimizar el costo de las búsquedas de marcadores (se maximiza la selectividad del índice para esa consulta concreta). Si el motor de base de datos estima que un operador lógico de búsqueda de marcadores puede devolver muchas filas, puede usar una instrucción PREFETCH para realizar la búsqueda de marcadores. Si el motor de base de datos usa PREFETCH para una búsqueda de marcadores, debe aumentar el nivel de aislamiento de transacción de una parte de la consulta para leer de forma repetida una parte de la consulta. Esto significa que lo que puede parecer similar a una instrucción SELECT en un nivel de aislamiento de lectura confirmada puede adquirir muchos miles de bloqueos de clave (tanto en el índice agrupado como en el no agrupado), lo que puede hacer que esa consulta supere los umbrales de extensión de bloqueo. Esto es especialmente importante si observa que el bloqueo escalado es un bloqueo de tabla compartido que no se suele ver en el nivel de aislamiento predeterminado de lectura confirmada.

  Si una búsqueda de marcadores con una cláusula PREFETCH es el origen de la extensión, considere la posibilidad de agregar columnas adicionales al índice no agrupado que aparece en el operador lógico de Index Seek o Index Scan debajo del operador lógico de Bookmark Lookup en el plan de consulta. Puede crear un índice de cobertura (que incluya todas las columnas de una tabla que se han usado en la consulta), o al menos un índice que abarque las columnas que se han usado para los criterios de combinación o en la cláusula WHERE si no es práctico incluir todo en la lista de columnas de selección. Una combinación de bucle anidado también puede usar PREFETCH, lo que provoca el mismo comportamiento de bloqueo.

• No se puede realizar la extensión de bloqueo si otro SPID contiene un bloqueo de tabla no compatible. La extensión de bloqueo siempre se extiende a un bloqueo de tabla y nunca a bloqueos de página. Además, si se produce un error en un intento de extensión de bloqueo porque otro SPID contiene un bloqueo TAB no compatible, la consulta que ha intentado la extensión no se bloquea mientras espera un bloqueo TAB. En su lugar, continúa con la adquisición de bloqueos en su nivel original y más granular (fila, clave o página), realizando periódicamente más intentos de extensión. Por tanto, un método para evitar la extensión de bloqueo en una tabla concreta consiste en adquirir y mantener un bloqueo en otra conexión que no sea compatible con el tipo de bloqueo escalado. Un bloqueo IX (preventivo exclusivo) en el nivel de tabla no bloquea ninguna fila o página, pero todavía no es compatible con un bloqueo TAB S (compartido) o X (exclusivo). Por ejemplo, imagine que tiene que ejecutar un trabajo por lotes que modifique un gran número de filas en la tabla mytable y que haya provocado el bloqueo debido a la extensión de bloqueo. Si este trabajo se completa siempre en menos de una hora, puede crear un trabajo de Transact-SQL que contenga el código siguiente y programar el nuevo trabajo para que se inicie varios minutos antes del trabajo por lotes:

  BEGIN TRAN
  SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1=0
  WAITFOR DELAY '1:00:00'
  COMMIT TRAN
  

  Esta consulta adquiere y mantiene un bloqueo IX en mytable durante una hora, lo que evita la extensión de bloqueo en la tabla durante ese tiempo. Este lote no modifica ningún dato ni bloquea otras consultas (a menos que la otra consulta fuerce un bloqueo de tabla con la sugerencia TABLOCK, o bien si un administrador ha deshabilitado los bloqueos de página o fila mediante un procedimiento almacenado sp_indexoption).

• También puede usar marcas de seguimiento 1211 y 1224 para deshabilitar todas o algunas extensiones de bloqueo. Pero estas marcas de seguimiento deshabilitan toda la extensión de bloqueo globalmente para la totalidad del motor de base de datos. La extensión de bloqueo tiene una finalidad muy útil en el motor de base de datos, ya que maximiza la eficacia de las consultas que de otro modo se ralentizan por la sobrecarga de la adquisición y liberación de varios miles de bloqueos. La extensión de bloqueo también ayuda a minimizar la memoria necesaria para realizar el seguimiento de los bloqueos. La memoria que el motor de base de datos puede asignar de forma dinámica para las estructuras de bloqueo es finita, por lo que si deshabilita la extensión de bloqueo y la memoria de bloqueo crece lo suficiente, se pueden producir errores en los intentos de asignar bloqueos adicionales a cualquier consulta y se produce el siguiente error: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  Nota:

  Cuando se produce el error MSSQLSERVER_1204, se detiene el procesamiento de la instrucción actual y se provoca la reversión de la transacción activa. La propia reversión puede bloquear a los usuarios o generar un tiempo de recuperación largo de la base de datos si se reinicia el servicio de base de datos.

  Nota:

  El uso de una sugerencia de bloqueo como ROWLOCK solo altera el plan de bloqueo inicial. Las sugerencias de bloqueo no impiden la extensión de bloqueo.

Supervisión de la extensión de bloqueo

Supervise la extensión de bloqueo mediante el evento extendido lock_escalation (xEvent), como en el ejemplo siguiente:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation(SET collect_database_name=(1),collect_statement=(1)
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.    username))
ADD TARGET package0.histogram(SET source=N'sqlserver.database_id')
GO

Importante

El evento extendido lock_escalation (xEvent) se debe usar en lugar de la clase de eventos Lock:Escalation en Seguimiento de SQL o SQL Profiler.

Bloqueo dinámico

La utilización de bloqueos de bajo nivel, como los de fila, aumenta la simultaneidad reduciendo la probabilidad de que dos transacciones soliciten bloqueos de los mismos datos al mismo tiempo. También aumenta el número de bloqueos y los recursos necesarios para administrarlos. Los bloqueos de alto nivel de tabla o página producen una sobrecarga menor, pero a costa de reducir la simultaneidad.

El motor de base de datos de SQL Server utiliza una estrategia de bloqueo dinámico para determinar los bloqueos que son más eficaces. El motor de base de datos de SQL Server determina automáticamente los bloqueos más apropiados cuando se ejecuta la consulta, basándose en las características del esquema y de la consulta. Por ejemplo, para reducir la sobrecarga de bloqueos, el optimizador puede decidir la realización de bloqueos de página en un índice al realizar un recorrido del índice.

El bloqueo dinámico presenta las ventajas siguientes:

• Administración simplificada de la base de datos. Los administradores de bases de datos no tienen que preocuparse de ajustar los umbrales de extensión de bloqueo.
• Mayor rendimiento. El motor de base de datos de SQL Server minimiza la sobrecarga del sistema al utilizar los bloqueos apropiados para la tarea.
• Los programadores de aplicaciones se pueden concentrar en la programación. El motor de base de datos de SQL Server ajusta el bloqueo automáticamente.

A partir de SQL Server 2008 (10.0.x), el comportamiento de la extensión de bloqueo ha cambiado con la introducción de la opción LOCK_ESCALATION. Para obtener más información, vea la opción LOCK_ESCALATION de ALTER TABLE.

Partición de bloqueos

Para los grandes sistemas, los bloqueos en los objetos a los que se hace referencia asiduamente pueden convertirse en un cuello de botella para el rendimiento, puesto que la adquisición y liberación de los bloqueos genera contención en los recursos de bloqueo internos. La partición de bloqueos mejora el rendimiento porque divide un solo recurso de bloqueo entre varios recursos de bloqueo más. Esta característica solo está disponible para los sistemas con 16 o más CPU, se habilita automáticamente y no se puede deshabilitar. Solo se pueden crear particiones de bloqueos de objetos. Los bloqueos de objeto que tienen un subtipo no tienen particiones. Para obtener más información, vea sys.dm_tran_locks (Transact-SQL).

Descripción de partición de bloqueos

Las tareas de bloqueo obtienen acceso a varios recursos compartidos, dos de los cuales se optimizan mediante la partición de bloqueos:

• Spinlock. Controla el acceso a un recuso de bloqueo, como una fila o una tabla.

  Sin la partición de bloqueos, un spinlock administra todas las solicitudes de bloqueo para un solo recurso de bloqueo. En los sistemas con un gran volumen de actividad, puede producirse contención a medida que las solicitudes de bloqueo esperan a que un spinlock esté disponible. En esta situación, la adquisición de bloqueos puede generar un cuello de botella que puede afectar negativamente al rendimiento.

  Para reducir la contención en un solo recurso de bloqueo, la partición de bloqueos divide un recurso de bloqueo en varios recursos de bloqueo para repartir la carga entre varios spinlock.

• Memory. Se utiliza para almacenar las estructuras de los recursos de bloqueo.

  Cuando ya se ha adquirido el spinlock, las estructuras de bloqueo se almacenan en memoria para que, a continuación, estén disponibles para el acceso y realizar modificaciones. La distribución del acceso a los bloqueos entre varios recursos ayuda a eliminar la necesidad de transferir bloqueos de memoria entre CPU, lo que ayuda a mejorar el rendimiento.

Implementación y supervisión de particiones de bloqueo

La partición de bloqueos está activada de forma predeterminada para los sistemas con 16 CPU o más. Cuando la partición de bloqueos está habilitada, se registra un mensaje informativo en el registro de errores de SQL Server.

Al adquirir bloqueos en un recurso con particiones:

• Solo los modos de bloqueo NL, SCH-S, IS, IU e IX se adquieren en una sola partición.

• Los bloqueos compartidos (S), exclusivos (X) y otros bloqueos en modos que no sean NL, SCH-S, IS, IU e IX deben adquirirse en todas las particiones empezando por el Id. de partición 0 seguido del resto de Id. en orden. Estos bloqueos en un recurso con particiones utilizarán más memoria que los bloqueos del mismo modo en un recurso sin particiones puesto que cada partición es de hecho un bloqueo independiente. El número de particiones determina los incrementos de memoria. Los contadores de bloqueo de SQL Server en el Monitor de rendimiento de Windows mostrarán información acerca de la memoria utilizada por los bloqueos con y sin particiones.

Una transacción se asigna a una partición cuando se inicia la transacción. Para la transacción, todas las solicitudes de bloqueo que pueden dividirse utilizan la partición asignada a esa transacción. Con este método, el acceso por parte de diferentes transacciones a los recursos de bloqueo del mismo objeto se distribuye a través de diferentes particiones.

La columna resource_lock_partition de la vista de administración dinámica sys.dm_tran_locks proporciona el identificador de la partición de bloqueo para un recurso con particiones de bloqueo. Para obtener más información, vea sys.dm_tran_locks (Transact-SQL).

Trabajo con la partición de bloqueos

En los siguientes ejemplos de código se muestra la partición de bloqueos. En estos ejemplos se ejecutan dos transacciones en dos sesiones diferentes para mostrar el comportamiento de la partición de bloqueos en sistemas grandes con 16 CPU.

Estas instrucciones Transact-SQL crean objetos de prueba que se utilizan en los siguientes ejemplos.

-- Create a test table.
CREATE TABLE TestTable  (col1 int);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable
    ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable VALUES (1);
GO

Ejemplo A

Sesión 1:

Una instrucción SELECT se ejecuta en una transacción. Debido a la sugerencia de bloqueo HOLDLOCK, esta instrucción adquirirá y retendrá un bloqueo Intención compartida (IS) en una tabla (en esta ilustración, los bloqueos de fila y página se pasan por alto). El bloqueo IS solo se adquirirá en la partición asignada a la transacción. Para este ejemplo, se supone que el bloqueo IS se adquiere en el id. 7 de la partición.

-- Start a transaction.
BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
    FROM TestTable
    WITH (HOLDLOCK);

Sesión 2:

Se inicia una transacción y la instrucción SELECT que se ejecuta bajo esta transacción adquirirá y retendrá un bloqueo compartido (S) en la tabla. El bloqueo S se adquirirá en todas las particiones que tengan como resultado varios bloqueos de tabla, uno para cada partición. Por ejemplo, en un sistema de 16 cpu, 16 bloqueos S se emitirán por el bloqueo en los id. 0-15 de la partición. Dado que el bloqueo S es compatible con el bloqueo IS que se retiene en el id. 7 de la partición por la transacción de la sesión 1, no hay ningún bloqueo entre las transacciones.

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCK, HOLDLOCK);

Sesión 1:

La siguiente instrucción SELECT se ejecuta bajo la transacción que todavía está activa bajo la sesión 1. Debido a la sugerencia de bloqueo de tabla (X) exclusiva, la transacción intentará adquirir un bloqueo X en la tabla. Sin embargo, el bloqueo S que retiene la transacción en la sesión 2 bloqueará el bloqueo X en el id. 0 de la partición.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Ejemplo B

Sesión 1:

Una instrucción SELECT se ejecuta en una transacción. Debido a la sugerencia de bloqueo HOLDLOCK, esta instrucción adquirirá y retendrá un bloqueo Intención compartida (IS) en una tabla (en esta ilustración, los bloqueos de fila y página se pasan por alto). El bloqueo IS solo se adquirirá en la partición asignada a la transacción. Para este ejemplo, se supone que el bloqueo IS se adquiere en el id. 6 de la partición.

-- Start a transaction.
BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
    FROM TestTable
    WITH (HOLDLOCK);

Sesión 2:

Una instrucción SELECT se ejecuta en una transacción. Debido a la sugerencia de bloqueo de TABLOCKX, la transacción intenta adquirir un bloqueo exclusivo (X) en la tabla. Recuerde que el bloqueo X se debe adquirir en todas las particiones comenzando en el id. 0 de la partición. El bloqueo X se adquirirá en los id. 0-5 de todas las particiones pero se bloqueará por el bloqueo IS adquirido por el id. 6 de la partición.

En los id. de partición 7 a 15 que el bloqueo X aún no ha alcanzado, otras transacciones todavía pueden adquirir bloqueos.

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCKX, HOLDLOCK);

Niveles de aislamiento basado en versiones de fila del motor de base de datos de SQL Server

Desde SQL Server 2005 (9.x), el motor de base de datos de SQL Server ofrece una implementación de un nivel de aislamiento de transacciones existente, con confirmación de lectura, que proporciona una instantánea de nivel de instrucción con versiones de fila. El motor de base de datos de SQL Server también ofrece un nivel de aislamiento de transacciones, instantánea, que proporciona una instantánea de nivel de transacción que también usa versiones de fila.

Las versiones de fila es un marco general en SQL Server que invoca un mecanismo de copia por escritura cuando se modifica o elimina una fila. Esto requiere que, mientras se ejecuta la transacción, la versión anterior de la fila debe estar disponible para las transacciones que requieran un estado anterior transaccionalmente coherente. Las versiones de fila hacen lo siguiente:

• Crear las tablas inserted y deleted en desencadenadores. Se crean versiones de las filas modificadas por el desencadenador. Esto incluye las filas modificadas por la instrucción que activó el desencadenador, así como las modificaciones de datos realizadas por el desencadenador.
• Compatibilidad con los conjuntos de resultados activos múltiples (MARS). Si una sesión MARS emite una instrucción de modificación de datos (como INSERT, UPDATE o DELETE) en un momento en el que hay un conjunto de resultados activos, se crean versiones de las filas afectadas por la instrucción de modificación.
• Compatibilidad con las operaciones de índice que especifican la opción ONLINE.
• Compatibilidad con los niveles de aislamiento de transacción basados en versiones de fila:
  • Nueva implementación del nivel de aislamiento de READ COMMITTED que utiliza las versiones de fila para proporcionar una coherencia de lectura en las instrucciones.
  • Nuevo nivel de aislamiento de instantánea que proporciona una coherencia de lectura en las transacciones.

La base de datos tempdb debe tener espacio suficiente para el almacén de versiones. Cuando tempdb esté llena, las operaciones de actualización dejarán de generar versiones y se continuarán funcionando correctamente, pero es posible que las operaciones de lectura provoquen errores porque es necesaria una determinada versión de fila que ya no existe. Esto afecta a las operaciones como los desencadenadores, MARS y los índices en línea.

La utilización de versiones de fila para las transacciones de lectura confirmada e instantáneas es un proceso de dos pasos:

1. Establezca una o dos de las opciones de base de datos (READ_COMMITTED_SNAPSHOT y ALLOW_SNAPSHOT_ISOLATION) en ON.

2. Seleccione el nivel de aislamiento de transacción apropiado en una aplicación:

   • Cuando el valor de la opción de base de datos READ_COMMITTED_SNAPSHOT sea ON, las transacciones que establezcan el nivel de aislamiento de READ COMMITTED utilizarán las versiones de fila.
   • Cuando el valor de la opción de base de datos ALLOW_SNAPSHOT_ISOLATION sea ON, las transacciones podrán establecer el nivel de aislamiento de instantánea.

Cuando el valor de la opción de base de datos READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION está establecido en ON, el motor de base de datos de SQL Server asignará un número de secuencia de la transacción (XSN) a cada transacción que manipule datos que utilicen las versiones de fila. Las transacciones empiezan en el momento en que se ejecuta una instrucción BEGIN TRANSACTION. No obstante, el número de secuencia de la transacción empieza con la primera operación de lectura/escritura después de la instrucción BEGIN TRANSACTION. El número de secuencia de la transacción aumenta en incrementos de uno cada vez que se asigna.

Cuando el valor de las opciones de base de datos READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION, se mantienen las copias lógicas (versiones) para todas las modificaciones de datos realizadas en la base de datos. Cada vez que se modifica una fila mediante una transacción determinada, la instancia del motor de base de datos de SQL Server almacena una versión de la imagen previamente confirmada de la fila en tempdb. Cada versión se marca con el número de secuencia de la transacción que realizó el cambio. Las versiones de filas modificadas se encadenan mediante una lista de vínculos. El valor de fila más reciente se almacena siempre en la base de datos actual y se encadena a las filas de versiones almacenadas en tempdb.

Nota:

En los casos de modificación de objetos grandes (LOB), solo se copia el fragmento cambiado al almacén de versiones de tempdb.

Las versiones de fila se conservan durante un tiempo suficiente para cumplir los requisitos de las transacciones ejecutadas con niveles de aislamiento basados en versiones de fila. El motor de base de datos de SQL Server realiza un seguimiento del número de secuencia de la transacción útil más antiguo y elimina periódicamente todas las versiones de filas marcadas con números de secuencia de la transacción anteriores al número de secuencia útil más antiguo.

Cuando el valor de ambas opciones de base de datos es OFF, solo se crean versiones de las filas modificadas por desencadenadores o sesiones MARS, o bien leídas por operaciones de índice ONLINE. Estas versiones de filas se liberan cuando dejan de ser necesarias. Un subproceso en segundo plano se ejecuta periódicamente para eliminar las versiones de filas obsoletas.

Nota:

En el caso de las transacciones de ejecución breve, puede que se almacene en caché una versión de una fila modificada en el grupo de búferes sin que se escriba en los archivos de disco de la base de datos tempdb. Si la fila versionada no va a ser necesaria durante mucho tiempo, simplemente se eliminará del grupo de búferes y puede que no provoque una sobrecarga de E/S.

Comportamiento durante la lectura de datos

Cuando las transacciones que se ejecutan con niveles de aislamiento basados en versiones de fila leen datos, las operaciones de lectura no adquieren bloqueos compartidos (S) para los datos que se leen, por lo que no bloquean las transacciones que están modificando datos. Asimismo, se minimiza la sobrecarga de los recursos de bloqueo, ya que se reduce el número de bloqueos adquiridos. El aislamiento de lectura confirmada mediante las versiones de fila y el aislamiento de instantánea están diseñados para proporcionar una coherencia de lectura de datos con versiones en las instrucciones o las transacciones.

Todas las consultas, incluidas las transacciones que se ejecutan en niveles de aislamiento basados en versiones de fila, adquieren bloqueos de estabilidad del esquema (Sch-S) durante la compilación y la ejecución. Debido a ello, las consultas se bloquean cuando una transacción simultánea aloja un bloqueo de modificación del esquema (Sch-M) en la tabla. Por ejemplo, una operación de lenguaje de definición de datos (DDL) adquiere un bloqueo Sch-M antes de modificar la información del esquema de la tabla. Las transacciones de consulta, incluidas las que se ejecutan en un nivel de aislamiento basado en versiones de fila, se bloquean cuando se intenta adquirir un bloqueo Sch-S. A la inversa, una consulta que mantiene un bloqueo Sch-S bloquea una transacción simultánea que intenta adquirir un bloqueo Sch-M.

Cuando se inicia una transacción con el nivel de aislamiento de instantánea, la instancia del motor de base de datos de SQL Server registra todas las transacciones actualmente activas. Cuando la transacción de instantánea lee una fila que tiene una cadena de versiones, el motor de base de datos de SQL Server sigue la cadena y recupera la fila en la que el número de secuencia de la transacción cumple las condiciones siguientes:

• Es el más cercano al número de secuencia de la transacción de instantánea que lee la fila, pero inferior al mismo.

• No se encuentra en la lista de transacciones activas cuando se inició la transacción de instantánea.

Las operaciones de lectura realizadas por una transacción de instantánea recuperan la versión más reciente de cada fila confirmada en el momento en el que empezó la transacción de instantánea. De este modo se consigue una instantánea coherente con las transacciones de los datos tal como existían en el momento de inicio de la transacción.

Las transacciones de lectura confirmada que utilizan versiones de fila funcionan de forma muy parecida. La diferencia es que las transacciones de lectura confirmada no utilizan su propio número de secuencia de transacción cuando eligen versiones de filas. Cada vez que se inicia una instrucción, la transacción de lectura confirmada lee el número de secuencia de la transacción más reciente emitido para esa instancia del motor de base de datos de SQL Server. Éste es el número de secuencia de la transacción utilizado para seleccionar las versiones de filas correctas para esa instrucción. Esto permite a las transacciones de lectura confirmada ver una instantánea de los datos tal como existían en el momento de inicio de cada instrucción.

Nota:

Aunque las transacciones de lectura confirmada que utilizan las versiones de fila proporcionan una vista de los datos en el nivel de instrucciones coherente desde el punto de vista de las transacciones, las versiones de fila que se generan o a las que se tiene acceso con este tipo de transacción se mantienen hasta que la transacción finaliza.

Comportamiento durante la modificación de datos

El comportamiento de las escrituras de datos es significativamente diferente con y sin bloqueo optimizado presente.

Modificación de datos sin bloqueo optimizado

En las transacciones de lectura confirmada que utilizan las versiones de fila, la selección de las filas que se deben actualizar se realiza mediante un recorrido de bloqueo en el que se obtiene un bloqueo de actualización (U) en la fila de datos cuando se leen los valores de datos. Es lo mismo que una transacción de lectura confirmada que no utiliza el control de versiones de fila. Si la fila de datos no cumple los criterios de actualización, se libera el bloqueo de actualización en esa fila y se bloquea y examina la siguiente.

Las transacciones que se ejecutan con aislamiento de instantánea obtienen un enfoque optimista de la modificación de datos mediante la adquisición de bloqueos de datos antes de realizar la modificación solo para forzar restricciones. De lo contrario, los bloqueos no se adquieren en los datos hasta que se van a modificar los datos. Cuando una fila de datos cumple los criterios de actualización, la transacción de instantánea comprueba que la fila de datos no haya sido modificada por una transacción simultánea confirmada después de que empezara la transacción de instantánea. Si se ha modificado la fila de datos fuera de la transacción de instantánea, se producirá un conflicto de actualizaciones y se finalizará la transacción de instantánea. El motor de base de datos de SQL Server controla el conflicto de actualizaciones; no se puede deshabilitar su detección.

Nota:

Las operaciones de actualización que se ejecutan con aislamiento de instantánea se ejecutan internamente con aislamiento de READ COMMITTED cuando la transacción de instantánea tiene acceso a cualquiera de los elementos siguientes:

Una tabla con una restricción FOREIGN KEY.

Una tabla a la que se hace referencia en la restricción FOREIGN KEY de otra tabla.

Una vista indizada que hace referencia a más de una tabla.

No obstante, incluso en estas condiciones, la operación de actualización seguirá comprobando que los datos no hayan sido modificados por otra transacción. Si se han modificado, la transacción de instantánea detectará un conflicto de actualización y terminará. La aplicación debe controlar y reintentar manualmente los conflictos de actualización.

Modificación de datos con bloqueo optimizado

Con el bloqueo optimizado habilitado y con la opción de base de datos READ_COMMITTED_SNAPSHOT (RCSI) habilitada, y con el nivel de aislamiento READ COMMITTED predeterminado, los lectores no adquieren ningún bloqueo y los escritores adquieren bloqueos de bajo nivel de duración corta, en lugar de bloqueos que expiran al final de la transacción.

Se recomienda habilitar RCSI para la mayor eficiencia con bloqueo optimizado. Cuando se usan niveles de aislamiento más estrictos como lectura repetible o serializable, el motor de base de datos se ve obligado a contener bloqueos de fila y página hasta el final de la transacción, tanto para lectores como para escritores, lo que da lugar a un aumento del bloqueo y la memoria de bloqueo.

Con RCSI habilitado y cuando se usa el nivel de aislamiento READ COMMITTED predeterminado, los escritores califican las filas por predicado en función de la versión confirmada más reciente de la fila, sin adquirir bloqueos U. Una consulta esperará solo si la fila se califica y hay una transacción de escritura activa en esa fila o página. Calificar en función de la versión confirmada más reciente y bloquear solo las filas calificadas reduce el bloqueo y aumenta la simultaneidad.

Si se detectan conflictos de actualización con RCSI y en el nivel de aislamiento READ COMMITTED predeterminado, se controlan y reintentan automáticamente sin ningún impacto en las cargas de trabajo del cliente.

Con el bloqueo optimizado habilitado, con el nivel de aislamiento SNAPSHOT, el comportamiento de los conflictos de actualización es el mismo. La aplicación debe controlar y reintentar manualmente los conflictos de actualización.

Nota:

Para obtener más información sobre los cambios de comportamiento con el bloqueo después de la característica de calificación (LAQ) del bloqueo optimizado, consulte Cambios de comportamiento de consulta con bloqueo optimizado y RCSI.

Resumen del comportamiento

En la tabla siguiente se resumen las diferencias entre el aislamiento de instantánea y el aislamiento READ COMMITTED mediante las versiones de fila.

Propiedad	Nivel de aislamiento de lectura confirmada mediante las versiones de fila	Nivel de aislamiento de instantánea
La opción de base de datos cuyo valor debe ser ON para habilitar la compatibilidad necesaria.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
Forma en la que una sesión solicita el tipo específico de versiones de fila.	Utilice el nivel de aislamiento de lectura confirmada predeterminado o ejecute la instrucción SET TRANSACTION ISOLATION LEVEL para especificar el nivel de aislamiento READ COMMITTED. Se puede hacer una vez iniciada la transacción.	Requiere la ejecución de SET TRANSACTION ISOLATION LEVEL para especificar el nivel de aislamiento SNAPSHOT antes de que se inicie otra transacción.
La versión de los datos leídos por las instrucciones.	Todos los datos confirmados antes del inicio de cada instrucción.	Todos los datos confirmados antes del inicio de cada transacción.
Modo de control de las actualizaciones.	Sin bloqueo optimizado: Vuelve desde las versiones de filas a los datos reales para seleccionar las filas que se actualizarán y utiliza bloqueos de actualización en las filas de datos seleccionadas. Adquiere bloqueos exclusivos en las filas de datos reales que se modificarán. Sin detección de conflictos de actualizaciones. Con el bloqueo optimizado: las filas se seleccionan en función de la última versión confirmada sin que se adquieran bloqueos. Si las filas califican para la actualización, se adquieren bloqueos exclusivos de fila o página. Si se detectan conflictos de actualización, se controlan y se reintentan automáticamente.	Utiliza las versiones de filas para seleccionar las filas que se actualizarán. Intenta adquirir un bloqueo exclusivo en la fila de datos real que se modificará y, si otra transacción ha modificado los datos, se producirá un conflicto de actualizaciones y se finalizará la transacción de instantánea.
Detección de conflictos de actualizaciones	Sin bloqueo optimizado: ninguno. Con el bloqueo optimizado: si se detectan conflictos de actualización, se controlan y se reintentan automáticamente.	Compatibilidad integrada. No se puede deshabilitar.

Uso de recursos de versiones de fila

El marco de las versiones de fila admite las siguientes características disponibles en SQL Server:

• Desencadenadores
• Conjuntos de resultados activos múltiples (MARS)
• Índices en línea

El marco de las versiones de fila también admite los siguientes niveles de aislamiento de transacción basado en las versiones de fila que, de manera predeterminada, no están habilitados:

• Cuando la opción de base de datos READ_COMMITTED_SNAPSHOT es ON, las transacciones READ_COMMITTED proporcionan coherencia de lectura de nivel de instrucciones con versiones de fila.
• Cuando la opción de base de datos ALLOW_SNAPSHOT_ISOLATION es ON, las transacciones SNAPSHOT proporcionan coherencia de lectura de nivel de transacciones con versiones de fila.

Los niveles de aislamiento basado en las versiones de fila reducen el número de bloqueos adquiridos por transacción mediante la eliminación del uso de bloqueos compartidos en operaciones de lectura. Esto aumenta el rendimiento del sistema al reducir los recursos utilizados para administrar bloqueos. El rendimiento también aumenta al reducir el número de veces que una transacción se bloquea mediante bloqueos adquiridos por otras transacciones.

Los niveles de aislamiento basados en las versiones de fila aumentan los recursos necesarios para la modificación de datos. Al habilitar estas opciones se crean versiones de filas de todas las modificaciones de datos para la base de datos. Se guarda una copia de los datos sin modificar en tempdb aunque no haya ninguna transacción activa que utilice el aislamiento basado en las versiones de fila. Los datos modificados incluyen un puntero a los datos con versiones almacenados en tempdb. En el caso de objetos grandes, solo se copia en tempdb la parte del objeto modificada.

Espacio utilizado en tempdb

En cada instancia del motor de base de datos de SQL Server, tempdb debe disponer de espacio suficiente para contener las versiones de fila generadas por todas las bases de datos de la instancia. El administrador de la base de datos debe asegurarse de que tempdb cuenta con espacio más que suficiente para dar cabida al almacén de versiones. Existen dos almacenes de versiones en tempdb:

• El almacén de versiones de generación de índices en línea se utiliza para generar índices en línea en todas las bases de datos.
• El almacén de versiones común se utiliza en las demás operaciones de modificación de datos de todas las bases de datos.

Las versiones de filas deben estar almacenadas mientras una transacción activa necesite tener acceso a ella. Cada minuto, un subproceso en segundo plano elimina las versiones de fila que ya no se necesitan y libera el espacio de versiones en tempdb. Una transacción de larga duración impide que se libere el espacio del almacén de versiones si se cumple alguna de las siguientes condiciones:

• Se utiliza el aislamiento basado en las versiones de fila.
• Se utilizan desencadenadores, MARS u operaciones de generación de índices en línea.
• Se generan versiones de filas.

Nota:

Cuando se invoca un desencadenador dentro de una transacción, las versiones de filas creadas por el desencadenador se mantienen hasta el final de la transacción, incluso cuando las versiones de filas dejan de necesitarse una vez completado el desencadenador. Esto también se aplica a las transacciones de lectura confirmada que usan las versiones de fila. Con este tipo de transacción, solo se necesita una vista de la base de datos transaccionalmente coherente para cada instrucción de la transacción. De este modo, las versiones de filas creadas para una instrucción de la transacción dejan de necesitarse una vez completada la instrucción. No obstante, las versiones de filas creadas por cada instrucción de la transacción se mantienen hasta que se complete la transacción.

Cuando tempdb se queda sin espacio, e motor de base de datos de SQL Server fuerza la reducción de los almacenes de versiones. Durante el proceso de reducción, las transacciones de mayor duración que todavía no han generado versiones de filas se marcan como sujetos. Se genera el mensaje 3967 en el registro de errores para cada transacción marcada como sujeto. Si una traducción se marca como sujeto, no podrá leer las versiones de filas del almacén de versiones. Cuando intenta leer versiones de filas, se genera el mensaje 3966 y la transacción se revierte. Si el proceso de reducción se realiza correctamente, pasa a quedar espacio disponible en tempdb. Si no, tempdb se queda sin espacio y se produce lo siguiente:

• Las operaciones de escritura continúan, pero no generan versiones. Aparece un mensaje informativo (3959) en el registro de errores, pero la transacción que escribe datos no se ve afectada.

• Las transacciones que intentan obtener acceso a versiones de filas que no se generaron debido a una reversión completa de tempdb terminan en un error 3958.

Espacio utilizado en filas de datos

Cada fila de base de datos puede utilizar hasta 14 bytes al final de la fila para información de las versiones de fila. La información de versiones de fila contiene el número de secuencia de la transacción que confirmó la versión y el puntero a la fila cuya versión se ha creado. Estos 14 bytes se agregan la primera vez que se modifica una fila o se inserta una nueva fila, si se cumple alguna de las siguientes condiciones:

• Las opciones READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION están en ON.
• La tabla tiene un desencadenador.
• Se utilizan conjuntos de resultados activos múltiples (MARS)
• En la actualidad, se ejecutan en la tabla operaciones de compilación de índices en línea.

Estos 14 bytes se eliminan de la fila de base de datos la primera vez que se modifica la fila si se cumplen todas estas condiciones:

• Las opciones READ_COMMITTED_SNAPSHOT y ALLOW_SNAPSHOT_ISOLATION están en OFF.
• El desencadenador ya no existe en la tabla.
• No se utiliza MARS.
• No se ejecutan en ese momento operaciones de generación de índices en línea.

Si se utiliza alguna de las características de versiones de fila, puede que sea necesario asignar espacio de disco adicional para que la base de datos dé cabida a los 14 bytes por fila de base de datos. Al agregar información de control de versiones de fila puede provocarse la división de la página de índices o la asignación de una nueva página de datos si no hay suficiente espacio disponible en la página actual. Por ejemplo, si la longitud media de fila es 100 bytes, los 14 bytes adicionales hacen que una tabla existente crezca hasta un 14 por ciento.

Si se reduce el factor de relleno, se puede impedir o reducir la fragmentación de las páginas de índice. Para ver información sobre la fragmentación de los datos e índices de una tabla o vista, puede usar sys.dm_db_index_physical_stats.

Espacio utilizado en objetos grandes

El motor de base de datos de SQL Server admite seis tipos de datos que pueden contener cadenas grandes de hasta dos gigabytes (GB) de longitud: nvarchar(max), varchar(max), varbinary(max), ntext, text y image. Las cadenas grandes almacenadas con estos tipos de datos se almacenan en una serie de fragmentos de datos que se vinculan a la fila de datos. La información de versiones de fila se almacena en cada uno de los fragmentos utilizados para almacenar estas cadenas grandes. Los fragmentos de datos son una colección de páginas dedicadas a objetos grandes en una tabla.

A medida que se agregan nuevos valores grandes a una base de datos, se asignan utilizando un máximo de 8.040 bytes de datos por fragmento. En versiones anteriores del motor de base de datos de SQL Server se almacenaban hasta 8 080 bytes de datos ntext, text o image por fragmento.

Los datos de objetos grandes (LOB) ntext, text e image existentes no se actualizan a fin de dejar espacio para la información de control de versiones de fila cuando una base de datos se actualiza a SQL Server desde una versión anterior de SQL Server. Sin embargo, la primera vez que se modifican los datos de LOB, se actualizan dinámicamente para habilitar el almacenamiento de información del control de versiones. Esto sucederá aunque no se generen versiones de filas. Una vez actualizados los datos de LOB, el número máximo de bytes almacenados por fragmento se reduce de 8.080 bytes a 8.040 bytes. El proceso de actualización es equivalente a eliminar el valor de LOB y volver a insertar el mismo valor. Los datos de LOB se actualizan aunque solo se haya modificado un solo byte. Esta operación se realiza una sola vez para cada columna ntext, text o image, pero, dependiendo del tamaño de los datos de LOB, puede que cada operación genere gran cantidad de asignaciones de página y actividad de E/S. Puede que también se genere gran cantidad de actividad de registro si la modificación se registra por completo. Las operaciones WRITETEXT y UPDATETEXT se registran mínimamente si el modelo de recuperación de la base de datos no se establece en FULL.

Los tipos de datos nvarchar(max), varchar(max) y varbinary(max) no están disponibles en versiones anteriores de SQL Server. Por lo tanto, no presentan problemas de actualización.

Debe asignarse suficiente espacio de disco para dar cabida a este requisito.

Supervisión de las versiones de fila y el almacén de versiones

Para los procesos de supervisión de versiones de fila, almacén de versiones y aislamiento de instantánea en cuanto al rendimiento y otros problemas, SQL Server proporciona herramientas en forma de Vistas de administración dinámica (DMV) y contadores de rendimiento del Monitor de sistema de Windows.

DMV

Las siguientes DMV proporcionan información sobre el estado actual del sistema de tempdb y el almacén de versiones, así como de las transacciones que utilizan las versiones de fila.

• sys.dm_db_file_space_usage. Devuelve información de uso del espacio para cada fila de la base de datos. Para obtener más información, consulte sys.dm_db_file_space_usage (Transact-SQL).

• sys.dm_db_session_space_usage. Devuelve la actividad de asignación y desasignación de páginas por sesión de la base de datos. Para obtener más información, consulte sys.dm_db_session_space_usage (Transact-SQL).

• sys.dm_db_task_space_usage. Devuelve la actividad de asignación y desasignación de páginas por tarea de la base de datos. Para obtener más información, consulte sys.dm_db_task_space_usage (Transact-SQL).

• sys.dm_tran_top_version_generators. Devuelve una tabla virtual para los objetos que producen la mayoría de las versiones del almacén de versiones. Agrupa las 256 longitudes de registro principales agregadas mediante su database_id y rowset_id. Use esta función para encontrar los principales consumidores del almacén de versiones. Para obtener más información, consulte sys.dm_tran_top_version_generators (Transact-SQL).

• sys.dm_tran_version_store. Devuelve una tabla virtual que muestra todos los registros de versión del almacén de versiones común. Para obtener más información, consulte sys.dm_tran_version_store (Transact-SQL).

• sys.dm_tran_version_store_space_usage. Devuelve una tabla virtual que muestra el espacio total en tempdb usado por los registros del almacén de versiones para cada base de datos. Para obtener más información, consulte sys.dm_tran_version_store_space_usage (Transact-SQL).

  Nota:

  Los objetos del sistema sys.dm_tran_top_version_generators y sys.dm_tran_version_store son funciones potencialmente muy costosas para ejecutarse, ya que ambas consultan todo el almacén de versiones, que podría ser muy grande. Mientras que sys.dm_tran_version_store_space_usage es eficaz y económica de ejecutar, ya que no navega por registros individuales del almacén de versiones y devuelve el espacio agregado del almacén de versiones que se ha utilizado en tempdb por base de datos.

• sys.dm_tran_active_snapshot_database_transactions. Devuelve una tabla virtual para todas las transacciones activas de todas las bases de datos en la instancia de SQL Server que utiliza versiones de fila. Las transacciones del sistema no aparecen en esta DMV. Para obtener más información, consulte sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

• sys.dm_tran_transactions_snapshot. Devuelve una tabla virtual que muestra las instantáneas tomadas por cada transacción. La instantánea contiene el número de secuencia de las transacciones activas que utilizan versiones de fila. Para obtener más información, consulte sys.dm_tran_transactions_snapshot (Transact-SQL).

• sys.dm_tran_current_transaction. Devuelve una sola fila que muestra información de estado relacionada con las versiones de fila de la transacción de la sesión actual. Para obtener más información, consulte sys.dm_tran_current_transaction (Transact-SQL).

• sys.dm_tran_current_snapshot. Devuelve una tabla virtual que muestra todas las transacciones activas en el momento en que se inicia la transacción actual de aislamiento de instantánea. Si la transacción actual está usando un aislamiento de instantáneas, esta función no devuelve filas. La DMV sys.dm_tran_current_snapshot es similar a sys.dm_tran_transactions_snapshot, excepto que devuelve solo las transacciones activas para la instantánea actual. Para obtener más información, consulte sys.dm_tran_current_snapshot (Transact-SQL).

Contadores de rendimiento

Los contadores de rendimiento de SQL Server proporcionan información sobre el rendimiento del sistema afectado por los procesos de SQL Server. Los siguientes contadores de rendimiento supervisan tempdb y el almacén de versiones, así como las transacciones que utilizan versiones de fila. Los contadores de rendimiento se encuentran en el objeto de rendimiento SQLServer:Transactions.

• Espacio disponible en tempdb (KB). Supervisa la cantidad, en kilobytes (KB), de espacio disponible en la base de datos tempdb. Debe haber suficiente espacio disponible en tempdb para controlar el almacén de versiones que admite aislamiento de instantánea.

  La fórmula siguiente ofrece una estimación aproximada del tamaño del almacén de versiones. En el caso de transacciones de larga duración, puede que sea conveniente supervisar la velocidad de generación y limpieza para estimar el tamaño máximo del almacén de versiones.

  [tamaño del almacén de versiones común] = 2 * [datos del almacén de versiones generados por minuto] * [mayor tiempo de ejecución (minutos) de la transacción]

  El mayor tiempo de ejecución de transacciones no debe incluir generaciones de índices en línea. Dado que estas operaciones pueden llevar mucho tiempo en tablas muy grandes, las generaciones de índices en línea utilizan un almacén de versiones independiente. El tamaño aproximado del almacén de versiones de generaciones de índices en línea equivale a la cantidad de datos modificados en la tabla, incluidos todos los índices, mientras la generación de índices en línea esté activa.

• Tamaño de almacén de versiones (KB). Supervisa el tamaño en KB de todos los almacenes de versiones. Esta información ayuda a determinar el espacio necesario para el almacén de versiones en la base de datos tempdb. La supervisión de este contador durante cierto tiempo proporciona una estimación útil del espacio adicional necesario para tempdb.

• Velocidad de generación de versión (KB/s). Supervisa la velocidad de generación de versión en KB por segundo en todos los almacenes de versiones.

• Velocidad de limpieza de versión (KB/s). Supervisa la velocidad de limpieza de versión en KB por segundo en todos los almacenes de versiones.

  Nota:

  La información procedente de Velocidad de generación de versión (KB/seg.) y Velocidad de limpieza de versión (KB/seg.) se puede utilizar para predecir los requisitos de espacio de tempdb.

• Recuento de unidad de almacén de versiones. Supervisa el recuento de unidades del almacén de versiones.

• Creación de unidad de almacén de versiones. Supervisa el número total de unidades del almacén de versiones creadas para almacenar versiones de filas desde que se inició la instancia.

• Truncamiento de unidad de almacén de versiones. Supervisa el número total de unidades del almacén de versiones truncadas desde que se inició la instancia. Una unidad del almacén de versiones se trunca cuando SQL Server determina que no se necesita ninguna de las filas de versiones almacenadas en la unidad del almacén de versiones para ejecutar transacciones activas.

• Frecuencia de conflictos de actualización. Supervisa la frecuencia de las transacciones de instantánea que tienen conflictos de actualización con respecto al número total de transacciones de instantánea de actualización.

• Tiempo mayor de ejecución de transacción. Supervisa el tiempo mayor de ejecución en segundos de cualquier transacción que utilice versiones de fila. Esto permite determinar si una transacción se ejecuta durante una cantidad de tiempo desproporcionada.

• Transacciones. Supervisa el número total de transacciones activas. Esto no incluye las transacciones del sistema.

• Transacciones de instantáneas. Supervisa el número total de transacciones de instantáneas activas.

• Transacciones de instantáneas de actualización. Supervisa el número total de transacciones de instantáneas activas que realizan operaciones de actualización.

• Transacciones de versión que no son instantáneas. Supervisa el número total de transacciones que no son instantáneas activas que generan registros de versión.

  Nota:

  La suma de Transacciones de instantáneas de actualización y Transacciones de versión que no son instantáneas representa el número total de transacciones que participan en la generación de versiones. La diferencia entre Transacciones de instantáneas y Transacciones de instantáneas de actualización notifica el número de transacciones de instantáneas de solo lectura.

Ejemplo de nivel de aislamiento basado en versiones de fila

En los siguientes ejemplos se muestran las diferencias de comportamiento entre transacciones de aislamiento de instantánea y transacciones de lectura confirmada que usan las versiones de fila.

A Trabajar con aislamiento de instantánea

En este ejemplo, una transacción que se ejecuta con aislamiento de instantánea lee los datos que a continuación modifica otra transacción. La transacción de instantáneas no bloquea la operación de actualización ejecutada por la otra transacción, sigue leyendo datos de la fila con control de versiones y omite la modificación de datos. No obstante, cuando la transacción de instantáneas intenta modificar los datos que ya han sido modificados por la otra transacción, genera un error y finaliza.

En la sesión 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;
    -- This SELECT statement will return
    -- 48 vacation hours for the employee.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

En la sesión 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;
    -- Subtract a vacation day from employee 4.
    -- Update is not blocked by session 1 since
    -- under snapshot isolation shared locks are
    -- not requested.
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

    -- Verify that the employee now has 40 vacation hours.
    SELECT VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

En la sesión 1:

    -- Reissue the SELECT statement - this shows
    -- the employee having 48 vacation hours. The
    -- snapshot transaction is still reading data from
    -- the versioned row.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

En la sesión 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

En la sesión 1:

    -- Reissue the SELECT statement - this still
    -- shows the employee having 48 vacation hours
    -- even after the other transaction has committed
    -- the data modification.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

    -- Because the data has been modified outside of the
    -- snapshot transaction, any further data changes to
    -- that data by the snapshot transaction will cause
    -- the snapshot transaction to fail. This statement
    -- will generate a 3960 error and the transaction will
    -- terminate.
    UPDATE HumanResources.Employee
        SET SickLeaveHours = SickLeaveHours - 8
        WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION
GO

B. Trabajar con transacciones de lectura confirmada utilizando las versiones de fila

En este ejemplo, una transacción de lectura confirmada que utiliza las versiones de fila se ejecuta simultáneamente con otra transacción. La transacción de lectura confirmada se comporta de diferente manera que una transacción de instantáneas. Al igual que una transacción de instantáneas, la transacción de lectura confirmada lee filas con versiones incluso después de que la otra transacción haya modificado los datos. Sin embargo, a diferencia de una transacción de instantáneas, la transacción de lectura confirmada:

• Leerá los datos modificados después de que la otra transacción confirme los cambios en los datos.
• Podrá actualizar los datos modificados por la otra transacción cuando la transacción de instantáneas no pueda.

En la sesión 1:

USE AdventureWorks2022;  -- Or any earlier version of the AdventureWorks database.
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;
    -- This SELECT statement will return
    -- 48 vacation hours for the employee.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

En la sesión 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;
    -- Subtract a vacation day from employee 4.
    -- Update is not blocked by session 1 since
    -- under read-committed using row versioning shared locks are
    -- not requested.
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

    -- Verify that the employee now has 40 vacation hours.
    SELECT VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

En la sesión 1:

    -- Reissue the SELECT statement - this still shows
    -- the employee having 48 vacation hours. The
    -- read-committed transaction is still reading data
    -- from the versioned row and the other transaction
    -- has not committed the data changes yet.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

En la sesión 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

En la sesión 1:

    -- Reissue the SELECT statement which now shows the
    -- employee having 40 vacation hours. Being
    -- read-committed, this transaction is reading the
    -- committed data. This is different from snapshot
    -- isolation which reads from the versioned row.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

    -- This statement, which caused the snapshot transaction
    -- to fail, will succeed with read-committed using row versioning.
    UPDATE HumanResources.Employee
        SET SickLeaveHours = SickLeaveHours - 8
        WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Habilitar los niveles de aislamiento basados en versiones de fila

Los administradores de bases de datos determinan la configuración de la base de datos para versiones de fila mediante las opciones de base de datos READ_COMMITTED_SNAPSHOT y ALLOW_SNAPSHOT_ISOLATION de la instrucción ALTER DATABASE.

Cuando se establece la opción de base de datos READ_COMMITTED_SNAPSHOT en ON, se activan inmediatamente los mecanismos utilizados para admitir la opción. Al establecer la opción READ_COMMITTED_SNAPSHOT, solo se permite en la base de datos la conexión que ejecuta el comando ALTER DATABASE. No debe haber ninguna otra conexión de base de datos abierta hasta que ALTER DATABASE haya finalizado. No es necesario que la base de datos esté en modo de usuario único.

La instrucción Transact-SQL siguiente permite READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

Si la opción de base de datos ALLOW_SNAPSHOT_ISOLATION se establece en ON, la instancia del motor de base de datos de SQL Server no genera versiones de filas para datos modificados hasta que finalicen todas las transacciones activas que han modificado los datos en la base de datos. Si hay transacciones de modificación activas, SQL Server establece el estado de la opción en PENDING_ON. Una vez finalizadas todas las transacciones de modificación, el estado de la opción cambia a ON. Los usuarios no pueden iniciar una transacción de instantáneas en la base de datos hasta que la opción esté completamente en ON. La base de datos pasa a un estado PENDING_OFF cuando el administrador de la base de datos establece la opción ALLOW_SNAPSHOT_ISOLATION en OFF.

La siguiente instrucción Transact-SQL habilita ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

En la tabla siguiente se enumeran y describen los estados de la opción ALLOW_SNAPSHOT_ISOLATION. El uso de ALTER DATABASE con la opción ALLOW_SNAPSHOT_ISOLATION no bloquea a los usuarios que actualmente acceden a los datos de la base de datos.

Estado del marco de aislamiento de instantánea para la base de datos actual	Descripción
Apagado	La compatibilidad con transacciones de aislamiento de instantánea no está activada. No se permiten transacciones de aislamiento de instantánea.
PENDING_ON	La compatibilidad de transacciones de aislamiento de instantánea se encuentra en estado de transición (de OFF a ON). Las operaciones abiertas deben finalizar. No se permiten transacciones de aislamiento de instantánea.
ACTIVAR	La compatibilidad de transacciones de aislamiento de instantánea está activada. Se permiten transacciones de instantáneas.
PENDING_OFF	La compatibilidad de transacciones de aislamiento de instantánea se encuentra en estado de transición (de ON a OFF). Las transacciones de instantáneas iniciadas con posterioridad no tienen acceso a esta base de datos. La actualización de transacciones sigue pagando el costo de crear versiones en esta base de datos. Las transacciones de instantáneas existentes siguen teniendo acceso a la base de datos sin problema. El estado PENDING_OFF no pasa a OFF hasta que finalicen las transacciones de instantáneas que estaban activas cuando el estado de aislamiento de instantánea de base de datos estaba en ON.

Use la vista de catálogo sys.databases para determinar el estado de ambas opciones de base de datos para las versiones de fila.

Todas las actualizaciones de tablas de usuario y algunas tablas de sistema almacenadas en la base de datos master y msdb generan versiones de fila.

La opción ALLOW_SNAPSHOT_ISOLATION se establece automáticamente en ON en las bases de datos master y msdb, y no puede deshabilitarse.

Los usuarios no pueden establecer en ON la opción READ_COMMITTED_SNAPSHOT en la bases de datos master, tempdb o msdb.

Uso de niveles de aislamiento basados en versiones de fila

El marco de versiones de fila está siempre habilitado en SQL Server y lo utilizan varias características. Además de proporcionar niveles de aislamiento basados en versiones de fila, se utiliza para admitir las modificaciones efectuadas en desencadenadores y sesiones de conjuntos de resultados activos múltiples (MARS), así como para admitir lecturas de datos en operaciones de índice ONLINE.

Los niveles de aislamiento basados en versiones de fila se habilitan en la base de datos. Cualquier aplicación que tenga acceso a objetos de bases de datos habilitadas puede ejecutar consultas con los siguientes niveles de aislamiento:

• Lectura de confirmadas, que utiliza versiones de fila al establecer la opción de base de datos READ_COMMITTED_SNAPSHOT en ON, como se muestra en el siguiente ejemplo de código:

  ALTER DATABASE AdventureWorks2022
      SET READ_COMMITTED_SNAPSHOT ON;
  

  Cuando la base de datos se habilita para READ_COMMITTED_SNAPSHOT, todas las consultas que se ejecutan en el nivel de aislamiento READ COMMITTED utilizan versiones de fila, lo que significa que las operaciones de lectura no bloquean las operaciones de actualización.

• Puede habilitar el aislamiento de instantáneas configurando la opción de base de datos ALLOW_SNAPSHOT_ISOLATION en ON, como se muestra en el ejemplo de código siguiente:

  ALTER DATABASE AdventureWorks2022
      SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  Una transacción que se ejecute en aislamiento de instantánea puede tener acceso a tablas de la base de datos que se hayan habilitado para instantáneas. Para obtener acceso a tablas que no se han habilitado para instantáneas, debe cambiarse el nivel de aislamiento. El siguiente ejemplo de código muestra una instrucción SELECT que une dos tablas mientras se ejecuta en una transacción de instantáneas. Una tabla pertenece a una base de datos en la que no se ha habilitado el aislamiento de instantánea. Cuando la instrucción SELECT se ejecuta en aislamiento de instantánea, no lo hace correctamente.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  BEGIN TRAN
      SELECT t1.col5, t2.col5
          FROM Table1 as t1
          INNER JOIN SecondDB.dbo.Table2 as t2
              ON t1.col1 = t2.col2;
  

  El siguiente ejemplo de código muestra la misma instrucción SELECT modificada para cambiar el nivel de aislamiento de transacción a lectura de confirmadas. Gracias a este cambio, la instrucción SELECT se ejecuta correctamente.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  BEGIN TRAN
      SELECT t1.col5, t2.col5
          FROM Table1 as t1
          WITH (READCOMMITTED)
          INNER JOIN SecondDB.dbo.Table2 as t2
              ON t1.col1 = t2.col2;
  

Limitaciones de transacciones con niveles de aislamiento basados en versiones de fila

Tenga en cuenta las siguientes limitaciones cuando trabaje con niveles de aislamiento basados en versiones de fila:

• READ_COMMITTED_SNAPSHOT no se puede habilitar en tempdb, msdb o master.

• Las tablas temporales globales se almacenan en tempdb. Cuando se obtiene acceso a tablas temporales globales en una transacción de instantáneas, debe realizarse alguna de las siguientes acciones:

  • Establezca la opción de base de datos ALLOW_SNAPSHOT_ISOLATION en ON en tempdb.
  • Usar una sugerencia de aislamiento para cambiar el nivel de aislamiento de la instrucción.

• Las transacciones de instantáneas provocan errores cuando:

  • Una base de datos pasa a ser de solo lectura una vez iniciada la transacción de instantáneas, pero antes de que ésta obtenga acceso a la base de datos.
  • Si al tener acceso a objetos de varias bases de datos, el estado de una base de datos se modificó de forma que la recuperación de la base de datos se produjo después del inicio de la transacción, pero antes del acceso de la transacción de instantáneas a la base de datos. Por ejemplo, la base de datos se estableció en OFFLINE y luego en ONLINE, cierre automático y apertura de base de datos, o adjuntar y separar una base de datos.

• Las transacciones distribuidas, incluidas las consultas de bases de datos con particiones distribuidas, no se admiten en aislamiento de instantánea.

• SQL Server no mantiene varias versiones de los metadatos del sistema. Las instrucciones del lenguaje de definición de datos (DDL) de tablas y otros objetos de base de datos (índices, vistas, tipos de datos, procedimientos almacenados y funciones de CLR (Common Language Runtime)) cambian los metadatos. Si una instrucción DDL modifica un objeto, cualquier referencia simultánea al objeto en aislamiento de instantánea provoca errores en la transacción de instantáneas. Las transacciones de lectura de confirmadas no tienen esta limitación cuando la opción de base de datos READ_COMMITTED_SNAPSHOT es ON.

  Por ejemplo, el administrador de la base de datos ejecuta la siguiente instrucción ALTER INDEX.

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID
      ON HumanResources.Employee REBUILD;
  GO
  

  Cualquier transacción de instantáneas que esté activa cuando se ejecuta la instrucción ALTER INDEX recibirá un error si intenta hacer referencia a la tabla HumanResources.Employee una vez ejecutada la instrucción ALTER INDEX. Las transacciones de lectura de confirmadas que utilicen versiones de fila no se verán afectadas.

  Nota:

  Las operaciones BULK INSERT pueden provocar cambios en los metadatos de la tabla de destino (por ejemplo, al deshabilitar las comprobaciones de restricciones). Cuando esto sucede, las transacciones simultáneas de aislamiento de instantánea que obtengan acceso a tablas de inserciones masivas generarán un error.

Personalizar las versiones de fila y bloqueo

Personalizar el tiempo de espera de bloqueo

Cuando una instancia del motor de base de datos de SQL Server no puede conceder un bloqueo a una transacción porque otra transacción ya posee un bloqueo en conflicto para el recurso, la primera transacción queda bloqueada a la espera de que se libere el bloqueo existente. De forma predeterminada no hay un tiempo de espera obligatorio, ni tampoco existe ningún modo de comprobar si un recurso está bloqueado antes de intentar bloquearlo, excepto intentar tener acceso a los datos (con el riesgo de quedar bloqueado indefinidamente).

Nota:

En SQL Server, use la vista de administración dinámica sys.dm_os_waiting_tasks para determinar si un proceso está bloqueado y quién lo bloquea. En versiones anteriores de SQL Server, use el procedimiento almacenado del sistema sp_who. Para obtener más información y ejemplos, consulte Descripción y resolución de problemas de bloqueo en SQL Server.

El parámetro LOCK_TIMEOUT permite a una aplicación establecer el tiempo máximo que una instrucción esperará en un recurso bloqueado. Cuando una instrucción ha esperado más tiempo del indicado en LOCK_TIMEOUT, la instrucción bloqueada se cancela automáticamente y se devuelve el mensaje de error 1222 (Lock request time-out period exceeded) a la aplicación. Pero SQL Server no cancela ni revierte ninguna transacción que contenga la instrucción. Por consiguiente, la aplicación debe tener un controlador de errores que pueda interceptar el mensaje de error 1222. Si una aplicación no intercepta el error, puede continuar sin ser consciente de que se ha cancelado una instrucción individual de la transacción y de que esto puede producir errores, ya que las instrucciones posteriores de la transacción podrían depender de la instrucción que nunca se ha ejecutado.

Al implementar un controlador de errores que intercepte el mensaje de error 1222 se permite a una aplicación controlar la situación de tiempo de espera y realizar una acción apropiada para solucionarla, como volver a enviar automáticamente la instrucción bloqueada o revertir la transacción completa.

Para determinar el valor LOCK_TIMEOUT actual, ejecute la función @@LOCK_TIMEOUT:

SELECT @@lock_timeout;
GO

Personalizar el nivel de aislamiento de transacción

READ COMMITTED es el nivel de aislamiento predeterminado del Motor de base de datos de Microsoft SQL Server. Cuando es necesario utilizar una aplicación en un nivel de aislamiento distinto, se pueden utilizar los métodos que se indican a continuación para configurar el nivel de aislamiento:

• Ejecute la instrucción SET TRANSACTION ISOLATION LEVEL.
• ADO.NET aplicaciones que usan el espacio de nombres administrado System.Data.SqlClient pueden especificar una opción IsolationLevel mediante el método SqlConnection.BeginTransaction.
• Las aplicaciones que usan ADO pueden establecer la propiedad Autocommit Isolation Levels.
• Al iniciar una transacción, las aplicaciones que utilicen OLE DB pueden llamar a ITransactionLocal::StartTransaction con la propiedad isoLevel establecida en el nivel de aislamiento de transacción deseado. Si se especifica el nivel de aislamiento en el modo de confirmación automática, para las aplicaciones que utilicen OLE DB se puede establecer la propiedad DBPROPSET_SESSION, DBPROP_SESS_AUTOCOMMITISOLEVELS en el nivel de aislamiento de transacción deseado.
• Las aplicaciones que usan ODBC pueden establecer el atributo SQL_COPT_SS_TXN_ISOLATION mediante SQLSetConnectAttr.

Si se especifica el nivel de aislamiento, el bloqueo de todas las consultas e instrucciones del lenguaje de manipulación de datos (DML) en la sesión de SQL Server se aplica en ese nivel de aislamiento. El nivel de aislamiento permanece vigente hasta que finaliza la sesión o hasta que se cambia la configuración del nivel de aislamiento.

En el siguiente ejemplo, se configura el nivel de aislamiento SERIALIZABLE:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID
    FROM HumanResources.Employee;
GO

El nivel de aislamiento se puede pasar por alto, si es necesario, para consultas o instrucciones DML individuales. Para ello debe especificarse una sugerencia de tabla. El hecho de especificar una sugerencia de tabla no afecta a las demás instrucciones de la sesión. Se recomienda que las sugerencias de tabla solo se utilicen para modificar el comportamiento predeterminado en los casos estrictamente necesarios.

Es posible que el motor de base de datos de SQL Server tenga que adquirir bloqueos al leer los metadatos incluso si el nivel de aislamiento se ha establecido en un nivel en el que no se soliciten bloqueos compartidos al leer los datos. Por ejemplo, una transacción que se ejecute en el nivel de aislamiento de lectura sin confirmar no adquiere bloqueos compartidos al leer datos, pero es probable que tenga que solicitar bloqueos en alguna ocasión al leer una vista de catálogo del sistema. Esto significa que es posible que una transacción con lectura sin confirmar provoque un bloqueo cuando ejecute consultas en una tabla mientras otra transacción simultánea modifica los metadatos de la tabla.

Para determinar el nivel de aislamiento de transacción que está establecido actualmente, utilice la instrucción DBCC USEROPTIONS como se indica en el siguiente ejemplo. El conjunto de resultados puede variar según el conjunto de resultados del sistema.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

El conjunto de resultados es el siguiente:

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Sugerencias de bloqueo

Se pueden especificar sugerencias de bloqueo para referencias de tablas individuales en las instrucciones SELECT, INSERT, UPDATE y DELETE. Las sugerencias especifican el tipo de bloqueo o las versiones de fila que utiliza la instancia del motor de base de datos de SQL Server para los datos de la tabla. Se pueden utilizar las sugerencias de bloqueo de tabla cuando se requiere un control más ajustado de los tipos de bloqueo adquiridos en un objeto. Estas sugerencias de bloqueo suplantan el nivel de aislamiento de la transacción actual durante la sesión.

Nota:

No se recomiendan sugerencias de bloqueo para su uso cuando está habilitado el bloqueo optimizado. Aunque se respetan las sugerencias de tabla y consulta, reducen la ventaja del bloqueo optimizado. Para obtener más información, consulte Evitar sugerencias de bloqueo con bloqueo optimizado.

Para obtener más información sobre las sugerencias de bloqueo específicas y sus comportamientos, vea Sugerencias (Transact-SQL); - tabla.

Nota:

El motor de base de datos de SQL Server suele elegir el nivel de bloqueo correcto casi siempre. Se recomienda utilizar las sugerencias de bloqueo de tabla para modificar el comportamiento de bloqueo predeterminado en los casos estrictamente necesarios. Impedir un nivel de bloqueo puede ir en detrimento de la simultaneidad.

El motor de base de datos de SQL Server podría verse forzado a adquirir bloqueos al leer los metadatos, incluso cuando procese una instrucción SELECT con una sugerencia de bloqueo que impida a las solicitudes compartir bloqueos al leer los datos. Por ejemplo, una instrucción SELECT que usa la sugerencia NOLOCK no adquiere bloqueos compartidos al leer los datos, pero podría solicitar bloqueos en alguna oportunidad al leer una vista de catálogo del sistema. Esto significa que es posible que una instrucción SELECT que utilice NOLOCK esté bloqueada.

Según se muestra en el siguiente ejemplo, si se establece el nivel de aislamiento de transacción en SERIALIZABLE y se utiliza la sugerencia de bloqueo de nivel de tabla NOLOCK con la instrucción SELECT, no se aplican los bloqueos de intervalos de claves usados normalmente para mantener las transacciones serializables.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
    FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT
        resource_type,
        resource_subtype,
        request_mode
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@spid;

-- End the transaction.
ROLLBACK;
GO

El único bloqueo utilizado que hace referencia a HumanResources.Employee es un bloqueo de estabilidad de esquema (Sch-S). En este caso, ya no se garantiza la serialidad.

En SQL Server, la opción LOCK_ESCALATION de ALTER TABLE puede penalizar los bloqueos de tabla y habilitar los bloqueos HoBT en tablas con particiones. Esta opción no es una sugerencia de bloqueo, pero se puede usar para reducir la escalación de los bloqueos. Para obtener más información, consulte ALTER TABLE (Transact-SQL)

Personalización del bloqueo de un índice

El motor de base de datos de SQL Server utiliza una estrategia de bloqueo dinámico que, en la mayoría de casos, elige automáticamente la mejor granularidad de bloqueo para las consultas. Se recomienda no invalidar los niveles de bloqueo predeterminados, que tienen el bloqueo de página y fila habilitados, a menos que se tenga un conocimiento claro de los patrones de acceso a tablas o índices y de que estos sean coherentes, y de que se surja un problema de contención de recursos que haya que resolver. Si se invalida un nivel de bloqueo, se puede obstaculizar considerablemente el acceso simultáneo a una tabla o índice. Por ejemplo, si se especifican bloqueos solamente para el nivel de tabla en una tabla de gran tamaño a la que los usuarios obtienen acceso frecuentemente, se pueden producir cuellos de botella, ya que los usuarios deben esperar a que se libere el bloqueo de la tabla para tener acceso a ella.

Hay algunos casos en lo que puede ser conveniente impedir los bloqueos de página o fila, siempre y cuando se comprendan perfectamente los patrones y estos sean coherentes. Por ejemplo, una aplicación de bases de datos utiliza una tabla de búsqueda que se actualiza semanalmente en un proceso por lotes. Los lectores simultáneos tienen acceso a la tabla con un bloqueo compartido (S) y las actualizaciones por lotes semanales obtienen acceso a la tabla con un bloqueo exclusivo (X). Al desactivar el bloqueo de página y fila en la tabla se reduce la sobrecarga de bloqueo durante la semana, ya que los lectores pueden tener acceso simultáneo a la tabla a través de bloqueos de tabla compartidos. Cuando se ejecuta el trabajo por lotes, este puede completar la actualización de forma eficaz porque obtiene un bloqueo de tabla exclusivo.

La desactivación del bloqueo de página y fila podría o no ser aceptable, ya que la actualización por lotes semanal impedirá que los lectores simultáneos tengan acceso a la tabla mientras se ejecuta la actualización. Si el trabajo por lotes solamente cambia unas cuantas filas o páginas, puede modificar el nivel de bloqueo para permitir el bloqueo de nivel de fila o página y, de esta forma, permitir que otras sesiones lean la tabla sin bloqueos. Si el trabajo por lotes tiene un gran número de actualizaciones, la obtención de un bloqueo exclusivo sobre la tabla puede ser la forma más eficaz de asegurarse de que el trabajo por lotes finaliza de modo eficaz.

En ocasiones se puede producir un interbloqueo si dos operaciones simultáneas adquieren bloqueos de fila en la misma tabla y se bloquean porque necesitan bloquear la página. Al impedir los bloqueos de fila se obliga a una de las operaciones a que espere y se evita el interbloqueo. Para obtener más información sobre los interbloqueos, consulte la guía de interbloqueos.

La granularidad del bloqueo utilizado en un índice se puede establecer mediante las instrucciones CREATE INDEX y ALTER INDEX. La configuración del bloqueo se aplica a las páginas de índice y a las páginas de tabla. Además, las instrucciones CREATE TABLE y ALTER TABLE se pueden usar para establecer la granularidad del bloqueo de en las restricciones PRIMARY KEY y UNIQUE. Para mantener la compatibilidad con versiones anteriores, el procedimiento almacenado del sistema sp_indexoption también puede establecer la granularidad. Para mostrar la opción de bloqueo actual de un determinado índice, utilice la función INDEXPROPERTY. Se pueden impedir los bloqueos de página, los de fila o una combinación de bloqueos de página y fila para un determinado índice.

Bloqueos no permitidos	Tienen acceso al índice
De página	Bloqueos de fila y tabla
De fila	Bloqueos de página y tabla
De página y fila	Bloqueos de tabla

Información avanzada sobre transacciones

Transacciones anidadas

Las transacciones explícitas se pueden anidar. El objetivo principal de esto es aceptar transacciones en procedimientos almacenados a los que se puede llamar desde un proceso que ya esté en una transacción o desde procesos que no tengan transacciones activas.

En el ejemplo siguiente se muestra el uso para el que están diseñadas las transacciones anidadas. El procedimiento TransProc exige su transacción, sin tener en cuenta el modo de transacción del proceso que lo ejecute. Si se llama a TransProc cuando una transacción está activa, la transacción anidada en TransProc se pasará por alto y se confirmarán o revertirán sus instrucciones INSERT basándose en la acción final adoptada para la transacción externa. Si un proceso que no tiene ninguna transacción pendiente ejecuta TransProc, la instrucción COMMIT TRANSACTION al final del procedimiento confirma de manera efectiva las instrucciones INSERT.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
               Colb CHAR(3) NOT NULL);
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are
   still in the table. This indicates that the commit
   of the inner transaction from the first EXECUTE statement of
   TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;
GO

El motor de base de datos de SQL Server omite la confirmación de las transacciones internas. La transacción se confirma o se revierte basándose en la acción realizada al final de la transacción más externa. Si se confirma la transacción externa, también se confirmarán las transacciones anidadas internas. Si se revierte la transacción externa, también se revertirán todas las transacciones internas, independientemente de si se confirmaron individualmente o no.

Cada llamada a COMMIT TRANSACTION o a COMMIT WORK se aplica a la última instrucción BEGIN TRANSACTION ejecutada. Si las instrucciones BEGIN TRANSACTION están anidadas, la instrucción COMMIT solo se aplica a la última transacción anidada, que es la más interna. Aunque una instrucción COMMIT TRANSACTION transaction_name de una transacción anidada haga referencia al nombre de la transacción externa, la confirmación solo se aplicará a la transacción más interna.

No es válido que el parámetro transaction_name de una instrucción ROLLBACK TRANSACTION haga referencia a las transacciones internas de un conjunto de transacciones anidadas con nombre. transaction_name solo puede hacer referencia al nombre de la transacción más externa. Si se ejecuta una instrucción ROLLBACK TRANSACTION transaction_name con el nombre de la transacción externa en cualquier nivel de un conjunto de transacciones anidadas, se revertirán todas las transacciones anidadas. Si se ejecuta una instrucción ROLLBACK WORK o ROLLBACK TRANSACTION sin el parámetro transaction_name en cualquier nivel de un conjunto de transacciones anidadas, se revertirán todas las transacciones anidadas, incluida la más externa.

La función @@TRANCOUNT registra el nivel de anidamiento de la transacción actual. Cada instrucción BEGIN TRANSACTION incrementa @@TRANCOUNT en uno. Cada instrucción COMMIT TRANSACTION o COMMIT WORK disminuye @@TRANCOUNT en uno. Una instrucción ROLLBACK WORK o ROLLBACK TRANSACTION que no tiene un nombre de la transacción revierte todas las transacciones anidadas y reduce @@TRANCOUNT a 0. Un ROLLBACK TRANSACTION que usa el nombre de transacción de la transacción más extrema en un conjunto de transacciones anidadas revierte todas las transacciones anidadas y reduce @@TRANCOUNT a 0. Si no está seguro de si se encuentra en una transacción, use SELECT @@TRANCOUNT para determinar si es 1 o más. Si @@TRANCOUNT es 0, no está en una transacción.

Uso de sesiones enlazadas

Las sesiones enlazadas facilitan la coordinación de las acciones entre varias sesiones iniciadas en un mismo servidor. Permiten que dos o más sesiones compartan la misma transacción y los mismos bloqueos, además de trabajar con los mismos datos sin que surjan conflictos de bloqueo. Se pueden crear sesiones enlazadas a partir de varias sesiones con la misma aplicación o desde varias aplicaciones con sesiones independientes.

Para participar en una sesión enlazada, una sesión llama a sp_getbindtoken o srv_getbindtoken (mediante Servicios abiertos de datos) para obtener un token de enlace. Un token de enlace es una cadena de caracteres que identifica de forma única cada transacción enlazada. El token de enlace se envía a las otras sesiones que se van a enlazar a la sesión actual. Las demás sesiones se enlazan con la transacción llamando a sp_bindsession con el token de enlace recibido de la primera sesión.

Nota:

Las sesiones deben tener una transacción de usuario activa para que sp_getbindtoken o srv_getbindtoken funcionen correctamente.

Los tokens de enlace deben transmitirse desde el código de la aplicación que establece la primera sesión al código de la aplicación que enlaza posteriormente sus sesiones a la primera sesión. No existe ninguna una instrucción Transact-SQL o función de API que pueda utilizar una aplicación para obtener el token de enlace de una transacción iniciada por otro proceso. A continuación se indican algunos métodos que se pueden utilizar para transmitir un token de enlace:

• Si se han iniciado todas las sesiones desde el mismo proceso de aplicación, se pueden guardar los tokens de enlace en la memoria global, o bien se pueden pasar como un parámetro a las funciones.

• Si se han establecido las sesiones desde procesos de aplicación independientes, los tokens de enlace se pueden transmitir mediante la comunicación entre procesos (IPC), como una llamada a un procedimiento remoto (RPC) o el intercambio dinámico de datos (DDE).

• Los tokens de enlace se pueden guardar en una tabla de una instancia del motor de base de datos de SQL Server que puedan leer los procesos que deseen enlazar a la primera sesión.

Solo puede haber una sesión activa a la vez en un conjunto de sesiones enlazadas. Si una sesión ejecuta una instrucción en la instancia o tiene resultados pendientes de la instancia, ninguna otra sesión enlazada podrá tener acceso a la instancia hasta que la sesión actual finalice el procesamiento o cancele la instrucción actual. Si la instancia está ocupada procesando una instrucción de otra de las sesiones enlazadas, se producirá un error que indica que el espacio de la transacción está en uso y que la sesión debería volver a intentarlo más tarde.

Cuando se enlazan sesiones, cada una de ellas mantiene su nivel de aislamiento. Si se usa SET TRANSACTION ISOLATION LEVEL para cambiar el valor del nivel de aislamiento de una sesión, no se verán afectados los valores de las sesiones enlazadas a ella.

Tipos de sesiones enlazadas

Los dos tipos de sesiones enlazadas son local y distribuido.

• Sesión enlazada local Permite que las sesiones enlazadas compartan el espacio de transacciones de una sola transacción en una única instancia del motor de base de datos de SQL Server.

• Sesión enlazada distribuida Permite que las sesiones enlazadas compartan la misma transacción entre dos o más instancias hasta que toda la transacción se haya confirmado o revertido mediante el Coordinador de transacciones distribuidas de Microsoft (MS DTC).

Las sesiones enlazadas distribuidas no se identifican mediante el token de enlace de una cadena de caracteres, sino mediante números de identificación de transacciones distribuidas. Si una sesión enlazada está implicada en una transacción local y ejecuta un RPC en un servidor remoto con SET REMOTE_PROC_TRANSACTIONS ON, MS DTC promueve automáticamente el nivel de la transacción enlazada local a transacción enlazada distribuida y se inicia una sesión de MS DTC.

Cuándo utilizar sesiones enlazadas

En versiones anteriores de SQL Server, las sesiones enlazadas se utilizaban básicamente para desarrollar procedimientos almacenados extendidos que tenían que ejecutar instrucciones Transact-SQL en nombre del proceso que los llamaba. Pasar el proceso que realiza la llamada en un token de enlace como un parámetro del procedimiento almacenado extendido permite al procedimiento combinar el espacio de transacciones del proceso que realiza la llamada y, por ello, integrar el procedimiento almacenado extendido con el proceso que realiza la llamada.

En el motor de base de datos de SQL Server, los procedimientos almacenados escritos mediante CLR son más seguros, escalables y estables que los procedimientos almacenados extendidos. Los procedimientos almacenados CLR utilizan el objeto SqlContext para combinar el contexto de la sesión de llamada en lugar de sp_bindsession.

Se pueden utilizar sesiones enlazadas para desarrollar aplicaciones de tres niveles en las que la lógica comercial se incorpora mediante programas independientes que funcionan en colaboración en una sola transacción comercial. Estos programas deben codificarse de forma que coordinen con precisión su acceso a la base de datos. Dado que las dos sesiones comparten los mismos bloqueos, ambos programas deben evitar intentar modificar los mismos datos a la vez. En cualquier momento solo puede haber una sesión que realice el trabajo como parte de la transacción. No se permiten ejecuciones en paralelo. La transacción solo se puede cambiar entre sesiones y en puntos de rendimiento bien definidos, como el momento en que han finalizado todas las instrucciones DML y se han recuperado todos los resultados.

Codificar transacciones eficaces

Es importante que las transacciones sean tan cortas como sea posible. Cuando se inicia una transacción, un sistema de administración de bases de datos (DBMS) debe contener muchos recursos hasta el final de la transacción para proteger las propiedades de atomicidad, coherencia, aislamiento y durabilidad (ACID) de la transacción. Si se modifican datos, se deben proteger las filas modificadas con bloqueos exclusivos que impidan que otra transacción lea las filas, y se deben mantener bloqueos exclusivos hasta que se confirme o se revierta la transacción. Dependiendo de la configuración del nivel de aislamiento de la transacción, las instrucciones SELECT pueden adquirir bloqueos que deben mantenerse hasta que la transacción se confirme o se revierta. Especialmente en sistemas con muchos usuarios, las transacciones deben ser tan cortas como sea posible para reducir el conflicto de bloqueos de recursos entre conexiones simultáneas. Es posible que las transacciones de larga duración y poco eficaces no constituyan un problema cuando hay un pequeño número de usuarios, pero son intolerables en sistemas con miles de usuarios. A partir de SQL Server 2014 (12.x), SQL Server admite transacciones duraderas retrasadas. Las transacciones durables diferidas no garantizan la durabilidad. Para saber más, vea Control de la durabilidad de las transacciones.

Instrucciones de código

A continuación se muestran las instrucciones para codificar transacciones eficaces:

• No pida entradas de los usuarios durante una transacción. Obtenga todas las entradas necesarias de los usuarios antes de iniciar la transacción. Si se necesita una entrada adicional del usuario durante una transacción, revierta la transacción actual y reinicie la transacción después de que el usuario proporcione la entrada. Aunque los usuarios respondan inmediatamente, los tiempos de reacción de las personas son mucho menores que la velocidad del equipo. Todos los recursos que mantiene la transacción se conservan durante un tiempo extremadamente largo que, en potencia, puede provocar problemas de bloqueos. Si los usuarios no responden, la transacción permanece activa y bloquea recursos críticos hasta que lo hagan, lo que puede tardar en suceder varios minutos o incluso horas.

• No abra una transacción mientras examina los datos si es posible. No puede iniciar las transacciones hasta que haya completado todos los análisis preliminares de datos.

• Haga la transacción lo más corta posible. Una vez que sepa las modificaciones que debe realizar, inicie una transacción, ejecute las instrucciones de modificación e, inmediatamente, confirme o revierta las operaciones. No abra la transacción antes de que sea necesario.

• Para reducir el bloqueo, considere la posibilidad de utilizar un nivel de aislamiento basado en versiones de fila para las consultas de solo lectura.

• Haga un uso inteligente de los niveles más bajos de aislamiento de las transacciones. Se pueden codificar rápidamente muchas aplicaciones para que utilicen un nivel de aislamiento de lectura confirmada de las transacciones. No todas las transacciones necesitan el nivel de aislamiento serializable de las transacciones.

• Haga un uso inteligente de las opciones de simultaneidad más bajas de los cursores, como las opciones de simultaneidad optimista. En un sistema que tenga pocas probabilidades de que se produzcan actualizaciones simultáneas, la sobrecarga que produce encontrar el error ocasional "alguien cambió los datos después de su lectura" puede ser mucho menor que la sobrecarga que produce bloquear siempre las filas a medida que se leen.

• Tenga acceso a la menor cantidad de datos posible en una transacción. Así reduce el número de filas bloqueadas y, por lo tanto, disminuye el conflicto entre transacciones.

• Evite las sugerencias de bloqueo pesimistas, como "holdlock", siempre que sea posible. Las sugerencias como HOLDLOCK o el nivel de aislamiento SERIALIZABLE pueden provocar que los procesos esperen incluso en bloqueos compartidos y que se reduzca la simultaneidad.

• Evite el uso de transacciones implícitas cuando tales transacciones implícitas puedan presentar un comportamiento imprevisible debido a su naturaleza. Consulte Transacciones implícitas y prevención de problemas de simultaneidad y de recursos.

• Diseñe índices con un factor de relleno reducido. El hecho de reducir el factor de relleno puede ayudarle a evitar o disminuir la fragmentación de las páginas de índice y, por tanto, reducir los tiempos de búsqueda de índices, especialmente cuando se recuperan del disco. Para ver información sobre la fragmentación de los datos e índices de una tabla o vista, puede usar sys.dm_db_index_physical_stats.

Transacciones implícitas y prevención de problemas de simultaneidad y de recursos

Para evitar los problemas de simultaneidad y de recursos, administre cuidadosamente las transacciones implícitas. Cuando utilice transacciones implícitas, la siguiente instrucción Transact-SQL después de COMMIT o ROLLBACK inicia automáticamente una nueva transacción. Esto puede hacer que se abra una nueva transacción mientras la aplicación examina los datos o, incluso, cuando pide una entrada del usuario. Tras concluir la última transacción necesaria para proteger las modificaciones de los datos, desactive las transacciones implícitas hasta que se necesite de nuevo una transacción para proteger las modificaciones de los datos. Este proceso permite que el motor de base de datos de SQL Server utilice el modo de confirmación automática mientras la aplicación examina los datos y obtiene la entrada del usuario.

Además, cuando el nivel de aislamiento de instantánea está habilitado, aunque una transacción nueva no mantenga bloqueos, una transacción de larga duración impedirá que las versiones anteriores se eliminen de tempdb.

Administrar las transacciones de ejecución prolongada

Una transacción de larga duración es una transacción activa que no se ha confirmado ni revertido puntualmente. Por ejemplo, si el usuario controla el inicio y la finalización de una transacción, una causa frecuente de las transacciones de ejecución prolongada es que un usuario inicie una transacción y se ausente mientras la transacción queda en espera de una respuesta suya.

Una transacción de larga duración puede provocar graves problemas para una base de datos de la siguiente manera:

• Si se cierra una instancia de servidor después de que una transacción activa haya realizado un gran número de modificaciones no confirmadas, la fase de recuperación del siguiente reinicio puede durar bastante más que el tiempo especificado en la opción de configuración del servidor recovery interval o la opción ALTER DATABASE ... SET TARGET_RECOVERY_TIME. Estas opciones controlan la frecuencia de puntos de comprobación activos e indirectos, respectivamente. Para más información sobre los tipos de puntos de comprobación, vea Puntos de comprobación de base de datos (SQL Server).

• Y, lo que es más importante, aunque la transacción en espera genere muy poco registro, la transacción retiene el truncamiento del registro de manera indefinida y provoca el excesivo crecimiento y llenado del mismo. Si el registro de transacciones se llena, la base de datos no puede realizar más actualizaciones. Para más información, vea Guía de arquitectura y administración de registros de transacciones de SQL Server, Solución de problemas de un registro de transacciones lleno (Error 9002 de SQL Server) y El registro de transacciones.

Importante

En base de datos de Azure SQL, las transacciones inactivas (transacciones que no se han escrito en el registro de transacciones durante seis horas) se finalizan automáticamente para liberar recursos.

Detección de transacciones de larga duración

Para buscar las transacciones de ejecución prolongada, use una de las opciones siguientes:

• sys.dm_tran_database_transactions

  Esta vista de administración dinámica devuelve información sobre las transacciones en la base de datos. En una transacción de ejecución prolongada, las columnas de especial interés incluyen la hora de la primera entrada del registro (database_transaction_begin_time), el estado actual de la transacción (database_transaction_state) y el número de flujo de registro (LSN) de la entrada inicial del registro de transacciones (database_transaction_begin_lsn).

  Para más información, consulte sys.dm_tran_database_transactions (Transact-SQL).

• DBCC OPENTRAN

  Esta instrucción permite identificar el Id. de usuario del propietario de la transacción, por lo que se puede realizar un seguimiento del origen de la misma para terminarla de forma más ordenada (confirmándola en lugar de revirtiéndola). Para obtener más información, vea DBCC OPENTRAN (Transact-SQL).

Detener una transacción

Puede que deba utilizar la instrucción KILL. Sin embargo, utilice esta instrucción con sumo cuidado, especialmente cuando se estén ejecutando procesos críticos. Para más información, consulte KILL (Transact-SQL).

Paradas

Los interbloqueos son un tema complejo relacionado con el bloqueo, pero diferente del bloqueo.

• Para más información sobre los interbloqueos, incluida la supervisión, el diagnóstico y ejemplos, vea la Guía de interbloqueos.
• Para más información sobre los interbloqueos específicos de Azure SQL Database, vea Análisis y prevención de interbloqueos en Azure SQL Database.

Contenido relacionado

• Sobrecarga del control de versiones de fila
• Información general sobre Eventos extendidos
• sys.dm_tran_locks (Transact-SQL)
• Vistas de administración dinámica del sistema
• Funciones y vistas de administración dinámica relacionadas con transacciones (Transact-SQL)
• Descripción y resolución de problemas de bloqueo en SQL Server
• Descripción y resolución de problemas de bloqueo en Azure SQL Database