Infrastructure at your Service

Stéphane Haby

Delayed Transaction Durability: A new feature in SQL Server 2014 CTP2

Delayed Transaction Durability is a new SQL Server 2014 feature that was not included in SQl Server 2014 CTP1 but discreetly published in CTP2. It is a very interesting functionality that helps reducing the IO contention for transaction log writes.

In other databases engine, you will find equivalents such as:

  • Oracle: ‘COMMIT WRITE BATCH WAIT|NOWAIT’ (link here)
  • MySQL: ‘group_commit’ since version 4.x (link here)
  • PostgreSQL: ‘group_commit’ introduce in version 9.2 (link here)

What is it for?

It is very simple: ‘Delayed Transaction Durability’ helps reducing the IO contention for transaction log writes. Transaction commits are asynchronous. The most important advantage is to release faster locks, but you have to consider that data can be lost (;-))…

Full or Delayed Transaction Durability

What kind of Transaction Durability should you choose – full or delayed? This is not an easy topic:

Transaction durability Explanation Advantage Disadvantage
Full transactions are written in the transaction log to disk before returning control to the client No data loss IO contention during log writes
Delayed transactions are written in the transaction log to disk after returning control to the client reduces IO contention Data loss possible

The best way to apply Transaction Durability maybe consists in mixing both types…
This feature is not available for In-Memory OLTP transaction but only for regular transaction.

How to use it?

This option is available in 3 control levels:

  • Database
  • Atomic block in Natively Compiled Procedure
  • COMMIT command option in T-SQL

Database level control

You can set this option with SSMS GUI or directly by script.
GUI: Right-click on database > properties > options -:




To insure backward compatibility, this option is set on DISABLED by default.

Three setting options are available:

  • DISABLED: feature cannot be used
  • ALLOWED: each transaction controls its durability (default is OFF for Atomic block in Natively Compiled Procedure and COMMIT command option in T-SQL)
  • FORCED: every transaction is under delayed durability (might be dangerous)

Atomic block in Natively Compiled Procedure level control

In the creation of the stored procedure, it is just the option DELAYED_DURABILITY with ON or OFF like this sample:


COMMIT command option in T-SQL level control

In the same way as for the Atomic Block in Natively Compiled Procedure, the COMMIT command has the option to set DELAYED_DURABILITY ON or OFF.


Delayed Transaction Durability is a new feature in SQL Server 2014, but given the feedback from my colleagues concerning other database systems (thanks a lot!), I would advise to use it with caution.
Data loss is a risk for many applications and the data consistency is not granted, but in most case, you should have no problem.
A good example is a (very, very good) application inserting a lot of data and for each INSERT line a COMMIT is done…
Don’t smile, it’s a common use case!

It is very practical in this case to have this option available in order to insure a fast workload.
I hope this will help you find the right answers and encourage you evaluating SQL Server 2014 CPT2 in order to resolve IO contention problems. SQL Server 2014 CTP2 is downloadable here.
For more information on Delayed Transaction Durability, look up the msdn website here.

One Comment

  • James Day says:

    Delayed Transaction Durability is not what concurrent commit in MySQL delivers. MySQL concurrent commit is durable at the time the system returns a result to the clients, subject to the value used for the innodb_flush_log_at_trx_commit setting.

    The equivalent of Delayed Transaction Durability is innodb_flush_log_at_trx_commit, which has three options:

    0: buffered within MySQL, but it’ll try to flush about once a second.
    1: an fsync with each commit (or batch of commits when concurrent commits are happening). Safe against power loss and lesser failures. ACID. Default.
    2: flush to OS with each commit, so crash-safe for MySQL but not power loss or OS crash.

    1 is what’s needed for ACID, 2 is a good compromise where power outages are believed to be unlikely. 1 with battery-backed write caching controller is the preferred way to get both durability and speed.

    James Day, MySQL Senior Principal Support Engineer, Oracle

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant