Controlar la durabilidad de las transacciones

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Las confirmaciones de transacciones de SQL Server pueden ser totalmente durables (el valor predeterminado de SQL Server) o durables diferidas (conocidas también como confirmaciones diferidas).

Las confirmaciones de transacciones totalmente durables son sincrónicas y notifican una instrucción COMMIT como correcta para devolver el control al cliente únicamente tras escribir en disco los registros de la transacción. Las confirmaciones de transacciones durables diferidas son asincrónicas y notifican una instrucción COMMIT como correcta antes de escribir en disco los registros de la transacción. Para que una transacción sea durable, es necesario escribir las entradas del registro de transacciones en el disco. Las transacciones durables diferidas pasan a ser durables cuando las entradas del registro de transacciones se vacían en el disco.

Este artículo ofrece información detallada sobre las transacciones durables diferidas.

Durabilidad total frente a durabilidad diferida de transacciones

Tanto la durabilidad total como la durabilidad diferida de las transacciones tienen ventajas e inconvenientes. Una aplicación puede tener una combinación de transacciones totalmente durables y durables diferidas. Debe considerar detenidamente las necesidades de la empresa y cómo encaja cada una de ellas en esas necesidades.

Durabilidad total de transacciones

Las transacciones totalmente durables escriben el registro de transacciones en el disco antes de devolver el control al cliente. Debe utilizar transacciones totalmente durables cuando:

  • El sistema no puede tolerar la pérdida de datos. Vea la sección ¿Cuándo puedo perder datos? para obtener información sobre cuándo puede perder algunos de los datos.

  • El cuello de botella no se debe a la latencia de escritura en el registro de transacciones.

La durabilidad diferida de transacciones reduce la latencia debida a operaciones de E/S de registro al conservar los registros de transacciones en memoria y escribir por lotes en el registro de transacciones, lo que requiere menos operaciones de E/S. Las transacciones de perdurabilidad diferida reduce potencialmente la contención de las E/S del registro, de forma que se reducen las esperas en el sistema.

Garantías de la durabilidad total de transacciones

Durabilidad diferida de transacciones

La durabilidad diferida de las transacciones se realiza mediante escrituras asincrónicas de registro en el disco. Las entradas del registro de transacciones se conservan en un búfer y se escriben en el disco cuando el búfer se llena o cuando se produce un evento de vaciado del búfer. La durabilidad diferida de las transacciones reduce tanto la latencia como la contención en el sistema porque:

  • El procesamiento de confirmación de transacciones no espera a que finalicen las E/S del registro y a devolver el control al cliente.

  • Resulta menos probable que las transacciones simultáneas compitan por E/S del registro; en su lugar, el búfer de registro puede vaciarse en el disco en fragmentos mayores, lo cual reduce la contención y aumenta el rendimiento.

    Nota

    Es posible que siga habiendo contención de E/S si hay un alto grado de simultaneidad, especialmente si el búfer de registro se llena más rápidamente que se vacía.

Cuándo utilizar durabilidad diferida de transacciones

He aquí algunos casos en los que puede ser beneficioso usar la durabilidad diferida de transacciones:

Puede tolerar alguna pérdida de datos.
Si puede tolerar cierta pérdida de datos, por ejemplo cuando los registros individuales no son críticos siempre y cuando tenga la mayoría de los datos, puede resultar útil usar la durabilidad diferida. Si no puede tolerar la pérdida de datos, no utilice la durabilidad diferida de transacciones.

Experimenta cuellos de botella en la escritura de registros de transacciones.
Si los problemas de rendimiento se deben a la latencia en la escritura de registros de transacciones, seguramente la aplicación se beneficiará de utilizar la durabilidad diferida de transacciones.

Las cargas de trabajo conllevan un alto índice de contención.
Si el sistema tiene cargas de trabajo con un alto índice de contención, se perderá mucho tiempo esperando a que se liberen los bloqueos. La durabilidad diferida de las transacciones reduce el tiempo de confirmación y, por tanto, libera los bloqueos con mayor rapidez, lo que redunda en un mayor rendimiento.

Garantías de la durabilidad diferida de transacciones

  • Una vez que la transacción se confirma correctamente, los cambios que realiza la transacción son visibles para las demás transacciones del sistema.

  • La durabilidad de las transacciones solo se garantiza después de vaciar en el disco el registro de transacciones en memoria. El registro de transacciones en memoria se vacía en el disco cuando:

    • Una transacción totalmente durable de la misma base de datos efectúa un cambio en la base de datos y se confirma correctamente.

    • El usuario ejecuta el procedimiento almacenado del sistema sp_flush_log correctamente.

      Si una transacción totalmente durable o sp_flush_log se confirma correctamente, se garantiza que todas las transacciones de durabilidad diferida que se hubieran confirmado anteriormente sean durables.

    • SQL Server intenta vaciar el registro en el disco en función de la generación de registros y del tiempo, incluso si la durabilidad de todas las transacciones es diferida. Esto suele realizarse correctamente si el dispositivo de E/S está al día. Sin embargo, SQL Server no proporciona garantías de durabilidad sólidas que no sean las transacciones durables y sp_flush_log.

Cómo controlar la durabilidad de las transacciones

Control de nivel de base de datos

Como administrador de la base de datos (DBA), con la instrucción siguiente puede controlar si los usuarios pueden usar transacciones de durabilidad diferida en una base de datos. Debe establecer la configuración de durabilidad diferida con ALTER DATABASE.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

DISABLED
[valor predeterminado] Con esta configuración, todas las transacciones que se confirman en la base de datos son totalmente durables, independientemente del nivel de confirmación (DELAYED_DURABILITY= [ON | OFF]). No hay necesidad de modificar y recompilar el procedimiento almacenado. De esta forma, puede asegurarse de que la durabilidad diferida nunca ponga datos en peligro.

ALLOWED
Con esta configuración, la durabilidad de cada transacción se determina en el nivel de transacción: DELAYED_DURABILITY = { OFF | ON }. Consulte Control de nivel de bloque ATOMIC: procedimientos almacenados compilados de forma nativa y Control de nivel COMMIT: Transact-SQL para obtener más información.

FORCED
Con esta configuración, cada transacción que se confirma en la base de datos es durable diferida. Independientemente de que la transacción especifique que es totalmente durable (DELAYED_DURABILITY = OFF) o no haga especificación alguna, la transacción es durable diferida. Esta configuración resulta útil cuando la durabilidad diferida es adecuada para una base de datos y no desea cambiar ningún código de aplicación.

Control de nivel de bloque ATOMIC: procedimientos almacenados compilados de forma nativa

El código siguiente va en el interior del bloque ATOMIC.

DELAYED_DURABILITY = { OFF | ON }

OFF
[valor predeterminado] La transacción es totalmente durable, salvo que la opción de base de datos DELAYED_DURABLITY = FORCED esté activa, en cuyo caso la instrucción COMMIT será asíncrona y por tanto durable diferida. Para obtener más información, consulte Control de nivel de base de datos.

ON
La transacción es durable diferida, salvo que la opción de base de datos DELAYED_DURABLITY = DISABLED esté activa, en cuyo caso la instrucción COMMIT será síncrona y por tanto totalmente durable. Para obtener más información, consulte Control de nivel de base de datos.

Código de ejemplo

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
/* procedure body goes here */
END

Tabla 1: Durabilidad en bloques ATOMIC

Opción de durabilidad de bloque ATOMIC Ninguna transacción existente Transacción en proceso (totalmente durable o durable diferida)
DELAYED_DURABILITY = OFF El bloque ATOMIC inicia una nueva transacción totalmente durable. El bloque ATOMIC crea un punto de retorno en la transacción existente y después inicia la nueva transacción.
DELAYED_DURABILITY = ON El bloque ATOMIC inicia una nueva transacción durable diferida. El bloque ATOMIC crea un punto de retorno en la transacción existente y después inicia la nueva transacción.

Control de nivel COMMIT: Transact-SQL

La sintaxis de COMMIT se ha ampliado para que pueda forzar la durabilidad diferida de transacciones. Si DELAYED_DURABILITY es DISABLED o FORCED en el nivel de base de datos (vea más arriba), esta opción de COMMIT se omite.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

OFF
[valor predeterminado] La instrucción COMMIT de la transacción es totalmente durable, salvo que la opción de base de datos DELAYED_DURABLITY = FORCED esté activa, en cuyo caso la instrucción COMMIT será asincrónica y por tanto durable diferida. Para obtener más información, consulte Control de nivel de base de datos.

ON
La instrucción COMMIT de la transacción es durable diferida, salvo que la opción de base de datos DELAYED_DURABLITY = DISABLED esté activa, en cuyo caso la instrucción COMMIT será sincrónica y por tanto totalmente durable. Para obtener más información, consulte Control de nivel de base de datos.

Resumen de opciones y sus interacciones

En esta tabla se resumen las interacciones entre las configuraciones de durabilidad diferida de nivel de base de datos y las configuraciones de nivel de confirmación. Las configuraciones de nivel de base de datos siempre tienen prioridad sobre las configuraciones de nivel de confirmación.

Valor COMMIT/Valor de base de datos DELAYED_DURABILITY = DISABLED DELAYED_DURABILITY = ALLOWED DELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = OFF Transacciones de nivel de base de datos. La transacción es totalmente durable. La transacción es totalmente durable. La transacción es de durabilidad diferida.
DELAYED_DURABILITY = ON Transacciones de nivel de base de datos. La transacción es totalmente durable. La transacción es de durabilidad diferida. La transacción es de durabilidad diferida.
DELAYED_DURABILITY = OFF Transacciones entre bases de datos o distribuidas. La transacción es totalmente durable. La transacción es totalmente durable. La transacción es totalmente durable.
DELAYED_DURABILITY = ON Transacciones entre bases de datos o distribuidas. La transacción es totalmente durable. La transacción es totalmente durable. La transacción es totalmente durable.

Cómo forzar un vaciado del registro de transacciones

Existen dos formas de forzar el vaciado en el disco del registro de transacciones.

  • Ejecutar todas las transacciones totalmente durables que modifican la misma base de datos. De esta forma se fuerza un vaciado en disco de las entradas de registro de todas las transacciones de durabilidad diferida confirmadas previamente.

  • Ejecutar el procedimiento almacenado del sistema sp_flush_log. Este procedimiento fuerza un vaciado en disco de las entradas de registro de todas las transacciones de durabilidad diferida confirmadas previamente. Para obtener más información, consulte sys.sp_flush_log (Transact-SQL).

Durabilidad diferida y otras características de SQL Server

Replicación transaccional, Change Tracking y captura de datos modificados

  • En el caso de las bases de datos habilitadas para la replicación transaccional o la captura de datos modificados (CDC), no se admite el uso de durabilidad diferida.

  • Change Tracking con durabilidad diferida se admite. Todas las transacciones con Change Tracking son totalmente duraderas. Una transacción tiene la propiedad change tracking si realiza operaciones de escritura en tablas que han habilitado el seguimiento de cambios.

A partir de SQL Server 2022 CU 2 y SQL Server 2019 CU 20, es posible que vea:

  • Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set si intenta habilitar la replicación transaccional o la captura de datos modificados en una base de datos que ha habilitado la durabilidad diferida.

  • Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled si intenta habilitar la durabilidad diferida en una base de datos configurada con replicación transaccional o captura de datos modificados.

Recuperación tras bloqueo
Se garantiza la coherencia, pero es posible que se pierdan algunas modificaciones de las transacciones de durabilidad diferida que se habían confirmado.

Transacciones entre bases de datos y DTC
Si una transacción es entre bases de datos o distribuida, es totalmente durable, independientemente de cualquier configuración de confirmación de base de datos o de transacción.

Grupos de disponibilidad AlwaysOn y creación de reflejo
Las transacciones de durabilidad diferida no garantizan la durabilidad del primario ni de los secundarios. Tampoco garantizan ningún conocimiento sobre la transacción en el secundario. Después de confirmar los cambios, se devuelve el control al cliente antes de que se reciba alguna confirmación de un secundario sincrónico. La replicación de réplicas secundarias continuará produciéndose mientras tenga lugar el vacío en el disco del primario.

Agrupación en clústeres de conmutación por error
Es posible que se pierdan algunas escrituras de transacciones durables diferidas.

Azure Synapse Link para SQL
Las transacciones duraderas diferidas no se admiten con Azure Synapse Link para SQL.

Trasvase de registros
Solo las transacciones que se han convertido en durables se incluyen en el registro que se trasvasa.

Copia de seguridad de registros de transacciones
Solo las transacciones que se han convertido en durables se incluyen en la copia de seguridad.

¿Cuándo puedo perder datos?

Si implementa la durabilidad diferida en alguna de las tablas, verá que ciertas condiciones pueden provocar la pérdida de datos. Si no puede tolerar una pérdida de datos, no debería usar la durabilidad diferida en las tablas.

Catástrofes

En caso de catástrofe, como un bloqueo del servidor, perderá los datos de todas las transacciones confirmadas que no se hayan guardado en el disco. Las transacciones de durabilidad diferida se guardan en el disco siempre que se ejecute una transacción totalmente durable respecto a una tabla (optimizada para memoria durable o basada en disco) en la base de datos o cuando se llama a sp_flush_log . Si está usando transacciones de durabilidad diferida, conviene crear una tabla pequeña en la base de datos que podrá actualizar regularmente o llamar de forma periódica a sp_flush_log para guardar todas las transacciones confirmadas pendientes. El registro de transacciones también se vacía cada vez que se llena, pero es difícil de predecir e imposible de controlar.

SQL Server apagado y reinicio

En lo que respecta a la durabilidad diferida, no hay ninguna diferencia entre el cierre inesperado y el cierre/reinicio planeado de SQL Server. Al igual que en las catástrofes, debe prever una pérdida de datos. En un apagado o reinicio planeado, algunas transacciones que no se han escrito en el disco se pueden guardar en el disco antes del apagado, pero no debería contar con ello. Tenga previsto sin embargo que en un cierre/reinicio, bien se haya planeado o no, se pierden datos al igual que en las catástrofes.

Pasos siguientes