Delayed Transaction Durability in SQL Server 2014 (CTP2)

Introduction

As we know, Durability (ACID; I hope you remember) guarantees that when a transaction is for examle committed then changes made by the transaction are permanently stored on disk. SQL Server provides the durability by logging the transaction to the transaction log on the disk before the transaction is considered to be committed. In the other words, we can say that if the transaction log entry fails then the entire transaction is roll backed.

SQL Server 2014 (CTP2) introduced Delayed Durability. It helps reduce the IO contention for writing to the transaction log. Transaction commits are asynchronous. In this case, transactions are logged to the transaction log buffer and then control is returned to the application. The log buffers are written to disk later. This feature is also known as Lazy Commit.

Before this version of SQL Server supports Fully Durable Transaction, this return commit as successful and returns control to the client (or application) after the log records are written to the disk. Whereas Delayed Durabilitytransaction commits are asynchronous and report the commit as successful before the write log records on the disk.

Control Transaction Durability

We can set this option both by the GUI as well as by direct script.

We have the following three options to set the value of Delayed Durability:

  • Disabled: The Delayed Durability feature cannot be used by the current database. This is the default setting. This is the same as a fully durable transaction.
  • Allowed: With this option, each transaction's durability is determined by the transaction level (DELAYED_DURABILITY = {OFF | ON}).
  • Forced: With this option, every transaction must follow Delayed Durability. This is very useful when transaction durability is more important for the database.

Syntax

ALTER DATABASE [DatabaseName] SET DELAYED_DURABILITY = {DISABLED | ALLOWED | FORCED}


Set Delayed Durability with COMMIT

SQL Server also allows us to use various durability levels at the transaction level. The COMMIT syntax is extended to support force delayed transaction durability. This COMMIT option is ignored when DELAYED_DURABILITY is DISABLED or FORCED at the database level.

Syntax

COMMIT TRAN [ transaction name | @transaction name variable ] [ WITH ( DELAYED_DURABILITY = { OFF | ON })]


Example

BEGIN TRAN

INSERT INTO table1 (myDate) SELECT GETDATE()

COMMIT WITH (DELAYED_DURABILITY = ON)


Delayed Durability with Procedure

SQL Server also allows us atomic blocks in natively compiled procedure level control. At the time of creation of the Stored Procedure, set DELAYED_DURABILITY to ON or OFF.

Example

CREATE PROCEDURE TESTPROC

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS BEGIN ATOMIC WITH 

(

    DELAYED_DURABILITY = ON,

    TRANSACTION ISOLATION LEVEL = SNAPSHOT

)

DECLARE @i INT = 0

WHILE(@i<5)

BEGIN

PRINT @i

SET @i+=1

END

END


Forced a transaction log flush

SQL Server has the system Stored Procedure "sp_flush_log" that forces a flush of the log records of all preceding committed (in memory).

SQL Server feature and Delayed Transaction Durability

 

SQL Server Feature

Remarks

CDC and CT

All transactions with Change Tracking are fully durable.

Cross Database or distributed Transaction

Fully durable in all cases.

Failover clustering

It allows delayed transaction durability but some data might be lost due to delay.

Replication

Delayed durable transaction does not guarantee for the replication. Transactions are only replicated
after they have been made durable.

Log shipping

The log shipping only includes those transactions that have been made durable.

Log Backup

Same log shipping.

Crash recovery

Some changes due to delayed durable transactions may be lost.

Delayed Transaction Durability in other Database

Database Name

Feature Name

Remarks

Oracle

COMMIT WRITE BATCH WAIT|NOWAIT

 

MySQL

GROUP_COMMIT

Available with and after version 4.x

PostgreSQL

GROUP_COMMIT

Available with and after version 9.2

Conclusion

Delayed Transaction Durability is a new feature introduced in SQL Server 2014 (CTP2). Some of the cases in which we can take advantage of Delayed Transaction Durability aew when some data losses are tolerable, if our system has heavy workloads and writing to the transaction log ewquires more time, Delayed Transaction Durability helps us to reduce the commit time. Delayed Transaction Durability guarantees that changes done by one transaction are visible to another transaction once the transaction is committed successfully.

The main advantages of Delayed Transaction Durability are:

  • Reduced commit time
  • Release of the transaction lock faster (less blocking and higher quantity)



Similar Articles