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:

```
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[dt_gap height=”10″ /]

```
BEGIN TRAN ...COMMIT TRAN WITH DELAYED_DURABILITY = ON (or OFF)
```
Makes a particular transaction non-durable[dt_gap height=”10″ /]

```
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)[dt_gap height=”10″ /]

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.

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to [email protected]

About the Author

Geoff Ballard

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).