When is a Transaction not a Transaction

Question: When is a transaction not a transaction?

Answer: When is not durable

Databases transactions are designed to ensure that selects, inserts, updates and deletes are processed reliably in the context of a single operation. To be more exact transactions implement the so called ACID properties which are:

·         Atomicity: The changes to the database all happen or none of them do

·         Consistency: The changes ensure that all validation rules apply and the database is left in a consistent state

·         Isolation: Changes made by one user are isolated from others until the transaction is complete

·         Durability: Once a transaction is complete it will remain so, regardless of power loss or other failures

A SQL Server transaction starts with BEGIN TRAN and ends with COMMIT or ROLLBACK and looks something like this:

BEGIN TRAN
                UPDATE …
                INSERT …
                DELETE …
COMMIT TRAN

Transactions in SQL Server are managed through the use of locks which protect one transaction from others and the transaction log which is separate storage that records all the changes to the database in addition to them being recorded in the database itself. In particular for a commit the data is guaranteed to be written to the transaction log:

The above model has served us well since SQL Server was invented (i.e. version 1.0). But SQL Server 2014 is adding a feature called DELAYED_DURABILITY. This is used as follows:

1.       ALTER DATABASE … SET DELAYED_DURABILITY = ALLOWED

·         Other options are DISABLED and FORCED

·         The database has to allow delayed durability before it can be used in a transaction

2.       BEGIN TRAN …  COMMIT TRAN WITH DELAYED_DURABILITY = ON (or OFF)

·         Makes a particular transaction non-durable 

3.       CREATE PROC … AS BEGIN ATOMIC WITH (DELAYED_DURABILITY = ON …

·         Makes a the transaction associated with the procedure non-durable

·         Only applies to native in memory procedures (another new feature in SQL Server 2014)  

In short the DELAYED_DURABILITY clause in the above allows the transaction to be non-durable breaking the ACID properties. To be exact it allows a transaction COMMIT to complete before all the data is written to the transaction log. 

So why does SQL Server 2014 need a special command that breaks durability? One answer is speed!

The fact is that on very high performance transactional systems the writes to the transaction log are the biggest I/O bottleneck simply because all data has to be written there before the COMMIT can complete.  One technique I have often used is to place the transaction log on a dedicated high speed disk drive to minimise the overhead of this. However in some systems the transaction log is still the limiting factor of overall performance.

I was monitoring a database recently that did high volume inserts of large records and I could see that the Wait State was often WRITE_LOG for the INSERT statements, thereby indicating that the transaction log was the limiting factor for performance.

Allowing a transaction to have DELAYED_DURABILITY means that SQL Server does not have to write the log immediately on COMMIT. Instead it can wait, holding data in cache, and then combine it with data from other transactions before writing it. This means fewer larger writes to the log increasing overall speed. Note the data is always written, just later than usual.

The danger of DELAYED_DURABILITY is that should the system fail then it could happen that a particular committed transaction may not be in the log and so on restart that transaction will be lost. This will only occur for the very latest transactions and will be similar to the way the updates for a transaction that has not yet been committed are lost on system failure.

It is a business decision as to whether this additional small loss of data is acceptable and I expect in many cases it will be given the low probability it will happen and the extra system performance it will provide.

In summary with SQL Server 2014 we will be able to choose to have DELAYED_DURABILITY for some or all of our transactions allowing us to squeeze even more performance out of the high volume transactional systems that require it.