Database Reference
In-Depth Information
Understanding the full transaction
durability
Fully durable transactions mean that SQL Server will wait for a commit to be
reported and the transaction to be written to the transaction log before returning
control to the client. While it is waiting for the commit to be reported, SQL Server
will block other transactions and sessions from accessing the resources it is updating.
Fully durable transactions will harden or write the transaction to the transaction log
before returning control to the client. This is the default behavior in SQL Server. You
should use this setting if you cannot tolerate data loss or poor performance in your
system, which is not caused by write latency on the transaction log.
A fully durable transaction guarantees that once a commit succeeds, data changes
made by the transaction are visible to other transactions in the system and durability
is guaranteed on commit. The transaction is written to the log before control is
returned to the client, which means that in the event of crash recovery running
against the database, the transaction will not be lost.
Understanding the delayed transaction
durability
Delayed transaction durability can reduce the waiting time caused by I/O
throughput on the database transaction log. With Delayed Durability the transaction
log records are stored in memory and written to the disk in batches, which in turn
means shorter I/O wait times. The process is sometimes referred to as a lazy commit.
Delayed transaction durability uses a process of asynchronous log writes to disk. The
transaction log records are stored in memory and are written to the log (hardened)
when the buffer is full or a
buffer flushing
event happens. This process can reduce
latency or wait time in the system along with reducing contention and the possibility
of blocking in the system.
With Delayed Durability, the commit process does not wait for the transaction to
be written to the transaction log (or hardened) before returning control to the client.
Also, concurrent transactions are less likely to compete for log I/O. Instead, the
transaction is stored in memory and written to the disk in larger chunks, which
can result in reduced contention and increased system throughput.
Search WWH ::
Custom Search