Controlar la durabilidad de las transaccionesControl Transaction Durability

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Las confirmaciones de transacciones deSQL ServerSQL Server pueden ser totalmente durables (el valor predeterminado de SQL ServerSQL Server ) o durables diferidas (conocidas también como confirmaciones diferidas).SQL ServerSQL Server transaction commits can be either fully durable, the SQL ServerSQL Server default, or delayed durable (also known as lazy commit).

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.Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk. 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.Delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are written to disk. Para que una transacción sea durable, es necesario escribir las entradas del registro de transacciones en el disco.Writing the transaction log entries to disk is required for a transaction to be durable. Las transacciones durables diferidas pasan a ser durables cuando las entradas del registro de transacciones se vacían en el disco.Delayed durable transactions become durable when the transaction log entries are flushed to disk.

Este tema contiene información detallada sobre las transacciones durables diferidas.This topic details delayed durable transactions.

Durabilidad total frente a durabilidad diferida de transaccionesFull vs. Delayed Transaction Durability

Tanto la durabilidad total como la durabilidad diferida de transacciones tienen sus ventajas y desventajas.Both full and delayed transaction durability have their advantages and disadvantages. Una aplicación puede tener una combinación de transacciones totalmente durables y durables diferidas.An application can have a mix of fully and delayed durable transactions. Debe considerar detenidamente las necesidades de la empresa y cómo encaja cada una de ellas en esas necesidades.You should carefully consider your business needs and how each fits into those needs.

Durabilidad total de transaccionesFull transaction durability

Las transacciones totalmente durables escriben el registro de transacciones en el disco antes de devolver el control al cliente.Fully durable transactions write the transaction log to disk before returning control to the client. Debe utilizar transacciones totalmente durables cuando:You should use fully durable transactions whenever:

  • El sistema no puede tolerar la pérdida de datos.Your system cannot tolerate any data loss.
    Vea la sección ¿Cuándo puedo perder datos? para obtener información sobre cuándo puede perder algunos de los datos.See the section When can I lose data? for information on when you can lose some of your data.

  • El cuello de botella no se debe a la latencia de escritura en el registro de transacciones.The bottleneck is not due to transaction log write latency.

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.Delayed transaction durability reduces the latency due to log I/O by keeping the transaction log records in memory and writing to the transaction log in batches, thus requiring fewer I/O operations. 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.Delayed transaction durability potentially reduces log I/O contention, thus reducing waits in the system.

Garantías de la durabilidad total de transaccionesFull Transaction Durability Guarantees

Durabilidad diferida de transaccionesDelayed transaction durability

La durabilidad diferida de las transacciones se realiza mediante escrituras asincrónicas de registro en el disco.Delayed transaction durability is accomplished using asynchronous log writes to disk. 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.Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. La durabilidad diferida de las transacciones reduce tanto la latencia como la contención en el sistema porque:Delayed transaction durability reduces both latency and contention within the system because:

  • El procesamiento de confirmación de transacciones no espera a que finalicen las E/S del registro y a devolver el control al cliente.The transaction commit processing does not wait for log IO to finish and return control to the client.

  • 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.Concurrent transactions are less likely to contend for log IO; instead, the log buffer can be flushed to disk in larger chunks, reducing contention, and increasing throughput.

    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.You may still have log I/O contention if there is a high degree of concurrency, particularly if you fill up the log buffer faster than you flush it.

Cuándo utilizar durabilidad diferida de transaccionesWhen to use delayed transaction durability

He aquí algunos casos en los que puede ser beneficioso usar la durabilidad diferida de transacciones:Some of the cases in which you could benefit from using delayed transaction durability are:

Puede tolerar alguna pérdida de datos. You can tolerate some data loss.
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.If you can tolerate some data loss, for example, where individual records are not critical as long as you have most of the data, then delayed durability may be worth considering. Si no puede tolerar la pérdida de datos, no utilice la durabilidad diferida de transacciones.If you cannot tolerate any data loss, do not use delayed transaction durability.

Experimenta cuellos de botella en la escritura de registros de transacciones. You are experiencing a bottleneck on transaction log writes.
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.If your performance issues are due to latency in transaction log writes, your application will likely benefit from using delayed transaction durability.

Las cargas de trabajo conllevan un alto índice de contención. Your workloads have a high contention rate.
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.If your system has workloads with a high contention level much time is lost waiting for locks to be released. La durabilidad diferida de transacciones reduce el tiempo de confirmación y, por tanto, libera los bloqueos con mayor rapidez, lo que redunda en un mayor rendimiento.Delayed transaction durability reduces commit time and thus releases locks faster which results in higher throughput.

Garantías de la durabilidad diferida de transaccionesDelayed Transaction Durability Guarantees

  • 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.Once transaction commit succeeds, the changes made by the transaction are visible to the other transactions in the system.

  • La durabilidad de las transacciones solo se garantiza después de vaciar en el disco el registro de transacciones en memoria.Transaction durability is guaranteed only following a flush of the in-memory transaction log to disk. El registro de transacciones en memoria se vacía en el disco cuando:The in-memory transaction log is flushed to disk when:

    • Una transacción totalmente durable de la misma base de datos efectúa un cambio en la base de datos y se confirma correctamente.A fully durable transaction in the same database makes a change in the database and successfully commits.

    • El usuario ejecuta el procedimiento almacenado del sistema sp_flush_log correctamente.The user executes the system stored procedure sp_flush_log successfully.

      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.If a fully durable transaction or sp_flush_log successfully commits, all previously committed delayed durability transactions are guaranteed to have been made durable.

    • SQL ServerSQL 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.does attempt to flush the log to disk both based on log generation and on timing, even if all the transactions are delayed durable. Esto suele realizarse correctamente si el dispositivo de E/S está al día.This usually succeeds if the IO device is keeping up. Sin embargo, SQL ServerSQL Server no proporciona garantías de durabilidad sólidas que no sean las transacciones durables y sp_flush_log.However, SQL ServerSQL Server does not provide any hard durability guarantees other than durable transactions and sp_flush_log.

Cómo controlar la durabilidad de las transaccionesHow to control transaction durability

Control de nivel de base de datosDatabase level control

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.You, the DBA, can control whether users can use delayed transaction durability on a database with the following statement. Debe establecer la configuración de durabilidad diferida con ALTER DATABASE.You must set the delayed durability setting with ALTER DATABASE.

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

DISABLED 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]).[default] With this setting, all transactions that commit on the database are fully durable, regardless of the commit level setting (DELAYED_DURABILITY=[ON | OFF]). No hay necesidad de modificar y recompilar el procedimiento almacenado.There is no need for stored procedure change and recompilation. De esta forma, puede asegurarse de que la durabilidad diferida nunca ponga datos en peligro.This allows you to ensure that no data is ever put at risk by delayed durability.

ALLOWED ALLOWED
Con esta configuración, la durabilidad de cada transacción se determina en el nivel de transacción: DELAYED_DURABILITY = { OFF | ON }.With this setting, each transaction's durability is determined at the transaction level - DELAYED_DURABILITY = { OFF | ON }. Vea 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.See Atomic block level control - Natively Compiled Stored Procedures and COMMIT level control -Transact-SQL for more information.

FORCED FORCED
Con esta configuración, cada transacción que se confirma en la base de datos es durable diferida.With this setting, every transaction that commits on the database is delayed durable. 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.Whether the transaction specifies fully durable (DELAYED_DURABILITY = OFF) or makes no specification, the transaction is delayed durable. 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.This setting is useful when delayed transaction durability is useful for a database and you do not want to change any application code.

Control de nivel de bloque ATOMIC: procedimientos almacenados compilados de forma nativaAtomic block level control - Natively Compiled Stored Procedures

El código siguiente va en el interior del bloque ATOMIC.The following code goes inside the atomic block.

DELAYED_DURABILITY = { OFF | ON }    

OFF 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.[default] The transaction is fully durable, unless the database option DELAYED_DURABLITY = FORCED is in effect, in which case the commit is asynchronous and thus delayed durable. Consulte Database level control para obtener más información.See Database level control for more information.

ON 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.The transaction is delayed durable, unless the database option DELAYED_DURABLITY = DISABLED is in effect, in which case the commit is synchronous and thus fully durable. Consulte Database level control para obtener más información.See Database level control for more information.

Código de ejemploExample Code:

CREATE PROCEDURE <procedureName> ...    
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER    
AS BEGIN ATOMIC WITH     
(    
    DELAYED_DURABILITY = ON,    
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,    
    LANGUAGE = N'English'    
    ...    
)    
END    

Tabla 1: durabilidad de bloques ATOMICTable 1: Durability in Atomic Blocks

Opción de durabilidad de bloque ATOMICAtomic block durability option Ninguna transacción existenteNo existing transaction Transacción en proceso (totalmente durable o durable diferida)Transaction in process (fully or delayed durable)
DELAYED_DURABILITY = OFFDELAYED_DURABILITY = OFF El bloque ATOMIC inicia una nueva transacción totalmente durable.Atomic block starts a new fully durable transaction. El bloque ATOMIC crea un punto de retorno en la transacción existente y después inicia la nueva transacción.Atomic block creates a save point in the existing transaction, then begins the new transaction.
DELAYED_DURABILITY = ONDELAYED_DURABILITY = ON El bloque ATOMIC inicia una nueva transacción durable diferida.Atomic block starts a new delayed durable transaction. El bloque ATOMIC crea un punto de retorno en la transacción existente y después inicia la nueva transacción.Atomic block creates a save point in the existing transaction, then begins the new transaction.

Control de nivel COMMIT -Transact-SQLTransact-SQLCOMMIT level control -Transact-SQLTransact-SQL

La sintaxis de COMMIT se ha ampliado para que pueda forzar la durabilidad diferida de transacciones.The COMMIT syntax is extended so you can force delayed transaction durability. 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.If DELAYED_DURABILITY is DISABLED or FORCED at the database level (see above) this COMMIT option is ignored.

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

OFF 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.[default] The transaction COMMIT is fully durable, unless the database option DELAYED_DURABLITY = FORCED is in effect, in which case the COMMIT is asynchronous and thus delayed durable. Consulte Database level control para obtener más información.See Database level control for more information.

ON 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.The transaction COMMIT is delayed durable, unless the database option DELAYED_DURABLITY = DISABLED is in effect, in which case the COMMIT is synchronous and thus fully durable. Consulte Database level control para obtener más información.See Database level control for more information.

Resumen de opciones y sus interaccionesSummary of options and their interactions

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.This table summarizes the interactions between database level delayed durability settings and commit level settings. Las configuraciones de nivel de base de datos siempre tienen prioridad sobre las configuraciones de nivel de confirmación.Database level settings always take precedence over commit level settings.

Valor COMMIT/Valor de base de datosCOMMIT setting/Database setting DELAYED_DURABILITY = DISABLEDDELAYED_DURABILITY = DISABLED DELAYED_DURABILITY = ALLOWEDDELAYED_DURABILITY = ALLOWED DELAYED_DURABILITY = FORCEDDELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = OFF Transacciones de nivel de base de datos.DELAYED_DURABILITY = OFF Database level transactions. La transacción es totalmente durable.Transaction is fully durable. La transacción es totalmente durable.Transaction is fully durable. La transacción es de durabilidad diferida.Transaction is delayed durable.
DELAYED_DURABILITY = ON Transacciones de nivel de base de datos.DELAYED_DURABILITY = ON Database level transactions. La transacción es totalmente durable.Transaction is fully durable. La transacción es de durabilidad diferida.Transaction is delayed durable. La transacción es de durabilidad diferida.Transaction is delayed durable.
DELAYED_DURABILITY = OFF Transacciones entre bases de datos o distribuidas.DELAYED_DURABILITY = OFF Cross database or distributed transaction. La transacción es totalmente durable.Transaction is fully durable. La transacción es totalmente durable.Transaction is fully durable. La transacción es totalmente durable.Transaction is fully durable.
DELAYED_DURABILITY = ON Transacciones entre bases de datos o distribuidas.DELAYED_DURABILITY = ON Cross database or distributed transaction. La transacción es totalmente durable.Transaction is fully durable. La transacción es totalmente durable.Transaction is fully durable. La transacción es totalmente durable.Transaction is fully durable.

Cómo forzar un vaciado del registro de transaccionesHow to force a transaction log flush

Existen dos formas de forzar el vaciado en el disco del registro de transacciones.There are two means to force flush the transaction log to disk.

  • Ejecutar todas las transacciones totalmente durables que modifican la misma base de datos.Execute any fully durable transaction that alters the same database. De esta forma se fuerza un vaciado en disco de las entradas de registro de todas las transacciones de durabilidad diferida confirmadas previamente.This forces a flush of the log records of all preceding committed delayed durability transactions to disk.

  • Ejecutar el procedimiento almacenado del sistema sp_flush_log.Execute the system stored procedure sp_flush_log. Este procedimiento fuerza un vaciado en disco de las entradas de registro de todas las transacciones de durabilidad diferida confirmadas previamente.This procedure forces a flush of the log records of all preceding committed delayed durable transactions to disk. Para obtener más información, vea sys.sp_flush_log (Transact-SQL).For more information see sys.sp_flush_log (Transact-SQL).

Durabilidad diferida y otras características de SQL ServerSQL ServerDelayed durability and other SQL ServerSQL Server features

Seguimiento de cambios y captura de datos modificados Change tracking and change data capture
Todas las transacciones con seguimiento de cambios son totalmente durables.All transactions with change tracking are fully durable. Las transacciones tienen la propiedad de seguimiento de cambios si realizan operaciones de escritura en tablas que se han habilitado para seguimiento de cambios.A transaction has the change tracking property if it does any write operations to tables that are enabled for change tracking. No se admite el uso de durabilidad diferida para bases de datos que usan captura de datos modificados (CDC).The use of delayed durability is not supported for databases which use change data capture (CDC).

Recuperación tras bloqueo Crash recovery
Se garantiza la coherencia, pero es posible que se pierdan algunas modificaciones de las transacciones de durabilidad diferida que se habían confirmado.Consistency is guaranteed, but some changes from delayed durable transactions that have committed may be lost.

Transacciones entre bases de datos y DTC Cross-database and 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.If a transaction is cross-database or distributed, it is fully durable, regardless of any database or transaction commit setting.

Grupos de disponibilidad AlwaysOn y creación de reflejo Always On Availability Groups and Mirroring
Las transacciones de durabilidad diferida no garantizan la durabilidad del primario ni de los secundarios.Delayed durable transactions do not guarantee any durability on either the primary or any of the secondaries. Tampoco garantizan ningún conocimiento sobre la transacción en el secundario.In addition, they do not guarantee any knowledge about the transaction at the secondary. Después de COMMIT, se devuelve el control al cliente antes de que se reciba algún reconocimiento desde un elemento secundario sincrónico.After commit, control is returned to the client before any acknowledgement is received from any synchronous secondary. La replicación de réplicas secundarias continuará produciéndose mientras tenga lugar el vacío en el disco del primario.Replication to secondary replicas does continue to happen as flush to disk on the primary happens.

Agrupación en clústeres de conmutación por error Failover clustering
Es posible que se pierdan algunas escrituras de transacciones durables diferidas.Some delayed durable transaction writes might be lost.

Replicación de transacciones Transaction Replication
Las transacciones durables diferidas no son compatibles con la replicación transaccional.Delayed durable transactions is not supported with Transactional Replication.

Trasvase de registros Log shipping
Solo las transacciones que se han convertido en durables se incluyen en el registro que se trasvasa.Only transactions that have been made durable are included in the log that is shipped.

Copia de seguridad de registros Log Backup
Solo las transacciones que se han convertido en durables se incluyen en la copia de seguridad.Only transactions that have been made durable are included in the backup.

¿Cuándo puedo perder datos?When can I lose data?

Si implementa la durabilidad diferida en alguna de las tablas, verá que ciertas condiciones pueden provocar la pérdida de datos.If you implement delayed durability on any of your tables, you should understand that certain circumstances can lead to data loss. Si no puede tolerar una pérdida de datos, no debería usar la durabilidad diferida en las tablas.If you cannot tolerate any data loss, you should not use delayed durability on your tables.

CatástrofesCatastrophic events

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.In the case of a catastrophic event, like a server crash, you will lose the data for all committed transactions that have not been saved to disk. 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 .Delayed durable transactions are saved to disk whenever a fully durable transaction is executed against any table (durable memory-optimized or disk-based) in the database, or sp_flush_log is called. 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.If you are using delayed durable transactions, you may want to create a small table in the database that you can periodically update or periodically call sp_flush_log to save all outstanding committed transactions. El registro de transacciones también se vacía cada vez que se llena, pero es difícil de predecir e imposible de controlar.The transaction log also flushes whenever it becomes full, but that is hard to predict and impossible to control.

Cierre y reinicio deSQL ServerSQL Server SQL ServerSQL Server shutdown and restart

En lo que respecta a la durabilidad diferida, no hay ninguna diferencia entre el cierre inesperado y el cierre/reinicio planeado de SQL ServerSQL Server.For delayed durability, there is no difference between an unexpected shutdown and an expected shutdown/restart of SQL ServerSQL Server. Al igual que en las catástrofes, debe prever una pérdida de datos.Like catastrophic events, you should plan for data loss. En un cierre/reinicio planeado, algunas transacciones que no se han escrito en el disco podrían, en primer lugar, guardarse en el disco, pero no debería contar con ello.In a planned shutdown/restart some transactions that have not been written to disk may first be saved to disk, but you should not plan on it. 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.Plan as though a shutdown/restart, whether planned or unplanned, loses the data the same as a catastrophic event.

Ver tambiénSee Also

Transacciones con tablas con optimización para memoriaTransactions with Memory-Optimized Tables