transaction delay when replica unavailable - what effect?

  • snomadj

    SSCarpal Tunnel

    Points: 4259

    I'm using AlwaysOn synchronous replica.  User issues an update on primary via SSMS.  In order for the transaction to be committed on primary, the tran needs to get to the secondary, be hardened to the secondary log & an ack sent & received back.  Let's say this takes 50 ms.  Does this mean the user sits and waits for 50 ms for a response back to his SSMS window?

    What happens when the secondary is unavailable for an hour?  Does he now sit and wait for the hour?

    I'm clearly not getting what effect the actual tran delay is having to the user.  I'd be super grateful for clarification.

    Thanks.

  • MVDBA

    SSC-Insane

    Points: 20802

    back in the day of mirroring, if a transaction couldn't be committed to the standby then it would lock the primary (unless you had enterprise edition and hi performance mode)

    this is a complete guess (and i'd love someone to tell me too) ssms will most likely lock up until you kill the spid

    you could easily replicate the issue by putting an open transaction on the secondary, see if ssms still works against that table. then roll back the transaction - I usually do

    begin tran

    update table set email=email

    waitfor delay '00:01:00'

    rollback tran

     

    MVDBA

  • Michael L John

    One Orange Chip

    Points: 25761

    snomadj wrote:

    I'm using AlwaysOn synchronous replica.  User issues an update on primary via SSMS.  In order for the transaction to be committed on primary, the tran needs to get to the secondary, be hardened to the secondary log & an ack sent & received back.  Let's say this takes 50 ms.  Does this mean the user sits and waits for 50 ms for a response back to his SSMS window?

    What happens when the secondary is unavailable for an hour?  Does he now sit and wait for the hour?

    I'm clearly not getting what effect the actual tran delay is having to the user.  I'd be super grateful for clarification.

    Thanks.

    Unless there was some sort of maintenance occurring, why would the secondary be offline for an hour?  Or was this just a scenario you were using as an example?

    The answer is no, the user will not be sitting there waiting for an hour.  If the secondary is unavailable, such as it being shut down, the transactions that need to be committed on the secondary will simply wait until it is available.  Your transaction logs will probably start growing.

    If in fact this is simply a matter of latency between the primary and secondary, you will definitely wait until the transaction is committed on the secondary.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Sue_H

    SSC Guru

    Points: 90260

    If the secondary is unavailable for an hour, the primary would switch to asynchronous commit. There is a session timeout period that defaults to 10 seconds. If that is exceeded, that's when the primary quietly switches to asynchronous. Those seem a bit buried in the documentation. The switch to asynchronous - see the first Note section in this documentation:

    Availability Modes (Always On Availability Groups)

    Session Timeout period is discussed in this documentation:

    Overview of AlwaysOn Availability Groups (SQL Server)

    Sue

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply