none
What happens to open transactions when failling over (Always on) RRS feed

  • Question

  • In the case of a manual failover, what happens to open transactions.  Are they killed (rolled back), completed, a little bit of both?

    Does the status of the query has any impact (are running queries handled differently then waiting ones, does the type of wait have an impact...)

    I am using synchronous commit mode and all I seem to find is reference to the potential or absense of loss of data.

    Thursday, September 3, 2015 1:27 PM

Answers

  • Because you are in synchronous commit mode, transactions are considered committed only when they are persisted on the LDF files on both replicas. So, when an AG fails over to a secondary replica, an open transaction will be rolled back because it has not been committed on the original primary replica. Now, even if the transaction committed on the original primary replica but not persisted on the LDF file on the secondary replica, it is still not considered a fully committed transaction from the point of view of the AG.

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Thursday, September 3, 2015 4:31 PM
    Moderator
  • point: 3---your secondary becomes primary and primary becomes secondary.

    as part of this data sync will be performed. so your uncommitted transaction will get committed.

    Until this sync is not completed user have to wait. Once the sync is completed user will get acknowledge about the transaction is committed. 

    Monday, September 7, 2015 5:16 AM

All replies

  • Because you are in synchronous commit mode, transactions are considered committed only when they are persisted on the LDF files on both replicas. So, when an AG fails over to a secondary replica, an open transaction will be rolled back because it has not been committed on the original primary replica. Now, even if the transaction committed on the original primary replica but not persisted on the LDF file on the secondary replica, it is still not considered a fully committed transaction from the point of view of the AG.

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Thursday, September 3, 2015 4:31 PM
    Moderator
  • You are using Synchronous commit mode. That means your secondary replica commits first and then commits data in Primary. 

    Assume failover happen in the mean time. your secondary becomes primary and primary becomes secondary. The moment it become primary it will wait until data is synchronised.

    Please read more on Availability groups  

    Friday, September 4, 2015 7:31 AM
  • Thanks a bunch.

    Your help is priceless.  So extracting from the documentation (I added numbers)"

    "

    • 1 - On receiving a transaction from a client, the primary replica writes the log for the transaction to the transaction log and concurrently sends the log record to the secondary replicas.
    • 2 - Once a log record is written to the transaction log of the primary database, the transaction can be undone only if there is a failover at this point to a secondary that did not receive the log. The primary replica waits for confirmation from the synchronous-commit secondary replica.
    • 3- The secondary replica hardens the log and returns an acknowledgement to the primary replica.
    • 4 -On receiving the confirmation from the secondary replica, the primary replica finishes the commit processing and sends a confirmation message to the client.

    "

    So a failover occurring before the secondary hardened the log would be rolled back.  What happens to a transaction hardened on the secondary but not yet on the primary.  What happens if the failover happens between point 3 and point 4?  Will it be retransmitted after the failover?  At that point, It is hardened on the secondary...


    • Edited by Antoine F Sunday, September 6, 2015 2:37 AM
    Sunday, September 6, 2015 2:34 AM
  • point: 3---your secondary becomes primary and primary becomes secondary.

    as part of this data sync will be performed. so your uncommitted transaction will get committed.

    Until this sync is not completed user have to wait. Once the sync is completed user will get acknowledge about the transaction is committed. 

    Monday, September 7, 2015 5:16 AM