Delayed Transaction Durability in MS SQL 2014(CTP2)
Introduction
When we first learn the Database we all know about the ACID property of database. We are not going to review it again. Just going to the D means the Durability.
Durability means that when a transaction is committed then changes made by the transaction are permanently stored on disk.
Before MS SQL Server 2014 the TRANSACTION of the SQL Server is Fully Durable. That means TRANSACTION commits are synchronous and report a COMMIT as successful and return control to the client only after the log records for the transaction are written to disk.
If the log records are written to disk successfully then the COMMIT process is successful and the control returns to client. In the other words, we can say that if the transaction log entry fails then the entire transaction is ROLLBACK.
MS 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.
Both Full and Delayed Transaction durability have their own advantage and disadvantage.
When to Use Full Transaction Durability
· We must use it when our system not tolerates any data loss.
· The bottleneck is not due to transaction log write latency.
When to Use Delayed Transaction Durability
We can tolerate some data loss
Where the individual records are not critical as long as we have most of the data.
We are experiencing a bottleneck on transaction log writes
If our performance issues are due to latency in transaction log writes, our application will likely benefit from using delayed transaction durability.
We workloads have a high contention rate
If our system has workloads with a high contention level much time is lost waiting for locks to be released. Delayed transaction durability reduces commit time and thus releases locks faster which results in higher throughput.
Option of the Transaction Durability
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.
Syantax
ALTER DATABASE [DatabaseName]
SET DELAYED_DURABILITY = {DISABLED | ALLOWED | FORCED}
Please note that MS SQL Server 2014 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.
COMMIT TRAN
[ transaction name | @transaction name variable ]
[ WITH ( DELAYED_DURABILITY = { OFF | ON })]
Example
BEGIN TRAN
INSERT INTO tbl_JODT
(JOINDT)
SELECT GETDATE()
COMMIT WITH (DELAYED_DURABILITY = ON)
With Procedure Example
CREATE PROCEDURE TESTPROC
WITH NATIVE_COMPILATION, SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH
(
DELAYED_DURABILITY = ON,
TRANSACTION ISOLATION LEVEL = SNAPSHOT
)
<.... Body ...>
END
Forced a transaction log flush
MS SQL Server 2014 has the system Stored Procedure named sp_flush_log that forces a flush of the log records of all preceding committed (in memory).
MS SQL Server 2014 has the system Stored Procedure named sp_flush_log that forces a flush of the log records of all preceding committed (in memory).
References
