0

The official documentation says that synchronous-commit with automatic failover will not lose data. But I'm still confused about how this is guaranteed. Actually I'm confused how the replication, commit, ack of secondary and reply to client located in the workflow.

Is it possible that some synchronous-commit ack but the master crashes before it receives all acks? In this case, some of the secondaries are in the lead of the transaction log and one of them become next master which with the uncommitted transaction committed.

Or maybe the always-on do 2PC, the master send commit signal to all secondaries and secondaries commit after receive the signal? Then will there be a condition that that master crashes after sending the commit signal but before it replies to the client, then we still have an uncommitted transaction committed.

I've setup the availability group myself, but I'm not sure how to confirm my suspicions because SQL Server is not open sourced.

Improve this question
3
  • 2
    I have to say that simultaneously holding the "I don't trust the vendor" and "but I'll trust what a bunch of random internet strangers tell me" would create cognitive dissonance for me.
    – Ben Thul
    Commented Apr 1 at 14:31
  • @BenThul I'm sorry you are so irritated. The official document doesn't say anything about the mechanism, that's why I ask this question
    – Godvania
    Commented Apr 7 at 3:49
  • I'm not irritated. But you're looking for evidence from people with the same access to resources that you have. Which is to say that you're unlikely to get an authoritative answer here.
    – Ben Thul
    Commented Apr 8 at 0:11

1 Answer 1

Reset to default
1

Actually I'm confused how the replication, commit, ack of secondary and reply to client located in the workflow.

A high level overview is available, it has XEs that you can use to understand the process more in depth.

Is is possible that some synchronous-commit ack but the master crashes before it receives all acks?

Yes, it's possible. Since the primary replica would have crashed before acknowledging from the secondary replicas, the client would have never received an acknowledgement, thus to the client (and SQL) it would have never been marked as committed successfully. Regardless of the primary coming back up or a synchronous commit secondary coming online, the items would be rolled back.

Or maybe the always-on do 2PC, the master send commit signal to all secondaries and secondaries commit after receive the signal?

It's a 2-phase approach as I somewhat explained above in the example. Until the client receives the acknowledgement (although some race conditions could cause this to not occur but the transaction did commit), the transaction is considered uncommitted. If something would happen while the client does not have an acknowledgement from the server, it will be rolled back and the client have to submit the item again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.