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

[mssql-support]

First published on MSDN on Apr 01, 2011

The behavior of a synchronous commit in AlwaysON is to commit the transaction on the local replica as well as the secondary replica before the transaction is considered complete.    If you  have used database mirroring you may be familiar with the term ‘High Availability’ to describe a similar behavior.

A common misperception is that the commit request originates on the primary, SQL Server commits to stable media on primary and then sends a message to the secondary to commit the transaction wait for the response.   However, in order to scale AlwaysON does NOT send a specific message to the secondary(s) for each commit request but instead uses multiple messages and active workers to optimize the performance.  This blog will walk you through how a synchronous commit, showing you how the process works in AlwaysON.

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.

At this point the primary and secondary are talking to each other, log blocks are being shipped to the secondary and hardened by the receive worker and the log blocks are being redone by the redo worker.    As I mentioned before AlwaysON is using a series of messages and workers to accomplish activity in parallel.

Now let us look at what happens when you issue a COMMIT TRAN.

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.

We can see that AlwaysOn uses a series of messages and workers is used to optimize the log block handling and provide high availability.  By avoiding the transmission of a specific commit message to the secondary for every commit that occurs on the primary the system is able to avoid a flood of the communications between the primary and secondary(s) that would not scale well.

Bob Dorr - Principal SQL Server Escalation Engineer