Let's assume that two SQL Server instances (S1 and S2) are in synchronous mirroring or in a synchronous Availability Group. Now the following happens:
- A write happens on S1 and transaction commit is initiated by the client
- The write is hardened on S2 (but not yet hardened on S1)
- S1 loses power and the commit log record is never written
- S2 loses power (the transaction is hardened already)
- S1 starts up and undoes the transaction
- S2 starts up and keeps the transaction
Now S2 is further along in time! It has a write that S1 does not have. The two replica have diverged.
What happens in such a situation? What happens when more writes arrive at S1 and take S1 into an incompatible history regarding S2? The write histories would look like a fork.
Best Answer
Firstly I should say great question usr. Sean, Robert and Max have both correctly stated that due to the re-negotiation between the Primary and Secondary, that the correct LSN is always resolved so that there is not possibility of any discrepancy in that scenario.
In response to the discussion with Shanky, in Synchronous mode, the Commit first has to occur on the Mirror (as you have correctly assumed usr) and this does indeed mean that the log has to be written to (those respectively log buffers flushed to disk on commit). Remember that this only means the log file has the transaction hardened to it, but at this stage, the transaction would still be "in the redo queue". This allows for a very fast commit to the Mirror, meaning that the commit on the Primary can also occur very quickly afterwards - this thereby reduces the chances further that there would be a discrepancy in the first place.
Other thing to consider is that if you are using delayed durability, this alters the mechanics further, and could actually result in lost transactions to your Mirrored database as a whole (due to the change in behaviour of the log buffer flushes - which no longer flush on commit), but resolution would between the two would still be able to occur to find the correct lsn point.