CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

  • Comments 4

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.

image

image

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

Leave a Comment
  • Please add 5 and 8 and type the answer here:
  • Post
  • It seems to me the text in the second table has been truncated on the right border. I see this in IE9 and Chrome.

  • The table in the content section is too wide. You can correct that with chrome inspector and read the text.

  • Probably worth mentioning why this allows the primary and secondary to actually not be in sync because of the latency in the redo applying the changes; or, does the change just hardened to the secondary log file get copied into the buffer pool as a dirty page and therefore queryable?

  • @Tony - That's correct.  Synchronous means the transaction is hardened on both the primary and secondary prior to the client receiving commit acknowledgement.  

    However, if you use the secondary for reads, be careful not to query the secondary within the same connection expecting the transaction to show up each and every time.  You will eventually have timing such that the query results do not contain the committed change because the redo log has not completed yet.

Page 1 of 1 (4 items)