Pop quiz: What does ACID stand for?
That should be a pretty easy question for database professionals:
It’s that last property, durability, that I want to talk about today. Durability guarantees that, when a transaction is committed, it stays committed. The changes made by that transaction are permanent, even in the event of a power failure. Now, for years, relational databases have stuck like glue to the ACID rules. SQL Server achieved durability by logging any transaction to the transaction log on disk before it was considered committed. That is, until now.
SQL Server 2014 CTP2 introduces a concept called delayed durability. With delayed durability, transactions are logged to the transaction log buffers in memory and control is returned to the application. The log buffers are hardened to disk later. How much later? According to BOL, the buffer will be flushed to disk when any of the following events take place:
- sp_flush_log is executed
- The in-memory transaction log buffer fills up.
- A fully durable transaction in the same database makes a change in the database and commits.
And even though BOL doesn’t specifically mention it, the buffer will also be flushed in the event of an orderly shutdown. So, with delayed durability, ACID really becomes ACI…D.
Delayed Durability in action
Using delayed durability starts at the database level. In SQL 2014 CTP2, we have a new database-level property, DELAYED_DURABILITY. This property can be set to one of three values:
- DISABLED – This is the default setting. All transactions are fully durable on commit
- ALLOWED – Durability is set at the transaction level, but by default transactions are fully durable unless specified otherwise
- FORCED – Delayed durability is applied to all transactions, even if the transaction specifies otherwise
Let’s start by creating a traditional, fully durable database in SQL 2014. We’ll then create a table and insert a record. We’ll also start up Perfmon and monitor Transactions/sec and Log Bytes Flushed/sec.
CREATE DATABASE [Durability] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Durability', FILENAME = N'C:\SQL2014\Data\Durability.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Durability_log', FILENAME = N'C:\SQL2014\Logs\Durability_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [Durability] SET DELAYED_DURABILITY = DISABLED GO USE [Durability] GO CREATE TABLE t1 ( id INT IDENTITY (1,1), currtime DATETIME) GO BEGIN TRAN INSERT INTO t1 (currtime) SELECT getdate() COMMIT
Looking at Perfmon above, we see that the transaction and the log flush to disk happened simultaneously. But what happens if we use delayed durability? We’ll start by allowing it at the database level.
USE [master] GO ALTER DATABASE [Durability] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT GO
At this point we can use delayed durability, but only if we specifically tell SQL Server to do so. We do that with a COMMIT option.
BEGIN TRAN INSERT INTO t1 (currtime) SELECT getdate() COMMIT WITH (DELAYED_DURABILITY=ON)
Now when we look at Perfmon, we see a blip for the transaction, but no corresponding log flush.
To harden that log buffer to disk, we can either run another transaction without delayed durability, or simply use the sp_flush_log stored procedure.
Now we finally see the log bytes being flushed to disk.
When to use delayed durability
First of all, don’t use this if you can’t tolerate the possibility of any data loss. If your database crashes, any transactions that haven’t been hardened to disk will be lost. That said, if you’re seeing a log of transaction log write waits, delayed durability can help alleviate that bottleneck by batching up writes. Also, because delayed durability reduces commit time, locks are released faster, meaning less blocking and higher throughput.
For more information on delayed durability in SQL Server 2014 CTP2, check out Books Online.
Thank you for the article – by and large, you made it very clear that delayed durability means you’re risking data loss, though I would be interested to see just how bad it could be and how CHECKDB might end up interpreting things after a sudden crash, as well as some metrics on actual performance differences.
However, while you correctly stated that “Durability guarantees that, when a transaction is committed, it stays committed” I must take serious issue with the statement “So, with delayed durability, ACID really becomes ACI…D” – it does not. Delayed durability breaks ACID compliance completely, because the guarantee is no longer there – all the NoSQL products that eventually write to disk after reporting a successful transaction have the same situation, and they’re not ACID compliant either.
With delayed durability allowed, the database now merely holds the traits of atomicity, consistency, and isolation – just ACI, nothing else, since they must be guaranteed in order to count.
I’ve been doing a series on Delayed Durability using CTP 2 including some crude performance testing. Any feedback welcome!
“…the buffer will also be flushed in the event of an orderly shutdown…”
Is this, in fact, true?
“For delayed durability, there is no difference between an unexpected shutdown and an expected shutdown/restart of SQL Server. ”
Hi Dave – That’s interesting, I’m not sure that section was there when I initially posted this. However, per BOL, “A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.” (http://msdn.microsoft.com/en-us/library/ms189573.aspx) And “Unless the WITHNOWAIT option is used, SHUTDOWN shuts down SQL Server by…Inserting a checkpoint in every database.” (http://msdn.microsoft.com/en-us/library/ms188767.aspx). That would seem to indicate that the log buffers would be flushed to disk in the event of an orderly shutdown. So which is it? Interesting…