AlwaysON - HADRON Learning Series: - How does AlwaysON Process a Synchronous Commit Request

Step

Action

Connect

The secondary establishes a valid connection to the primary using the configured mirroring endpoints.

Request Data

The secondary initiates a request to the primary, asking for the log blocks to be shipped.   The secondary and primary will negotiate the proper LSN staring point and other information necessary.

Start Log Scanner

A log scanner worker is started on the primary.  The log scanner ships log blocks to the secondary.  The information can be retrieved from log cache, as blocks are flushed on the primary, or the log file, for LSNs required by the secondary, which are no longer resident in log cache.

Redo

The secondary starts a redo thread to processes log blocks (runs redo) that have been received from the log scanner on the primary and hardened on the secondary.

Progress Response(s)

The secondary sends progress message to the primary on regular intervals—approximately every 3 messages from the primary or every one second occur, whichever occurs first.
The response contains information about the secondary progress including the LSN level that has been hardened as well as redone.

NOTE: This is a significant part of understanding the commit behavior.   There is a distinct difference between hardening the log block and redoing the log block.   This will become more evident as I talk about how we wait for the commit response from the secondary.

Step

Action

Commit Tran

The T-SQL COMMIT TRAN triggers the Flush To LSN behavior.   SQL Server requires that the log records be flushed (hardened) to stable media to consider the transaction committed.

Local Flush To LSN

On the primary a request is made to Flush to LSN level of the commit record.   This tells the log writer thread on the Primary database replica to bundle up all log records up to the commit and flush them to stable media.

Here is where the messaging of AlwaysON shines.   When a log block is flushed it will signal other readers of the log cache (in our case the log scanner) that a log block is ready and can be sent to the secondary.

The log scanner picks up the log block(s) and sends them to the AlwaysON log block cracker.   The cracker logic looks for operations that need special handling, such as file stream actives, file growth, etc…   The cracking logic can send messages to the secondary and once the log block has been cracked the log block is sent to the secondary.

Log Block Message

The log block message is processed on the secondary.

NOTE: This is where folks get a bit confused but hopefully I can explain.   The processing of the log block message means the log block is saved to stable media (written to the log file).  Redo is a separate worker!  The log block can then be stored in the secondary log cache and redo will process the log block as it handles redo.

Progress Response

The progress response contains information such as LSN harden level and the redo LSN level.   These can be different as redo is still in progress.   These messages are returned approx. every 3 messages or 1 second (think of the database mirroring ping / is alive concept.)

Commit Complete

KEY CONCEPT

To complete the commit, the log block must be harden on the primary and the secondary.   It does not need to be redone on the secondary just hardened to meet HA capabilities.   The redo lag time only impacts the time to return to operations and not the commit capabilities.