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 COMMITSQL 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)