6

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:

  1. A write happens on S1 and transaction commit is initiated by the client
  2. The write is hardened on S2 (but not yet hardened on S1)
  3. S1 loses power and the commit log record is never written
  4. S2 loses power (the transaction is hardened already)
  5. S1 starts up and undoes the transaction
  6. 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.

Improve this question
3
  • 1
    Mirroring would look at the LSNs and know they were out of sync.
    – Hannah Vernon ♦
    Oct 20 '14 at 19:17
  • 1
    And then? There is no way to consolidate the replicas. They have conflicting writes. Also, the network could be down so that the replicas cannot talk to each other.
    – usr
    Oct 20 '14 at 19:21
  • 1
    Then you have a situation where, at worst, the mirror wont sync with the master, and you need to recreate the mirror.
    – Hannah Vernon ♦
    Oct 21 '14 at 0:07
6

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.

Improve this answer
4
  • Apologies for my bad grammar - I should have proof read my response better!
    – Mark Broadbent
    Nov 21 '14 at 11:33
  • I guess the core of the question now is whether the principal or the mirror harden their logs first. If the mirror hardens first (and then power loss to both nodes) the mirror can be further ahead in time. Negotiating the correct LSN is not a problem that I see (just use the max). But diverged writes are a problem and it is not apparent to me how exactly diverged writes are prevented in all cases. Maybe the answer is: The two replica can indeed diverge in rare cases and require manual reinitialization!
    – usr
    Nov 21 '14 at 12:04
  • Remember that from application perspective (assuming we are not talking about delayed durability which is different), under synchr the app in question will not receive a commit signal (i.e. commit statement wont return) until the transaction has successfully committed period. Therefore the question of where the first commit actually happens is arguably a moot point because until the commit returns at the application, the transaction is not (and cannot be) considered committed. All official documentation I've read states that in Synchr, first commit is on Mirror. Async occurs on primary first
    – Mark Broadbent
    Nov 21 '14 at 14:59
  • 1
    I would also add, that it is a shame that MS don't publish more publicly available material on the exact low level mechanics of many of their technologies. Doing so would give more understanding and confidence. That said, it would be possible to get a good idea of what is going on from cracking open Windbg and also viewing transaction log records using many of the techniques Paul Randal provides in his excellent blog.
    – Mark Broadbent
    Nov 21 '14 at 15:03
2

I am talking about database mirroring as I am not sure whether this discussion holds same for AG which I assume it will be. I am talking about Synchronous mirroring and as per my knowledge actually following happens

Following happens when suppose DML is started on Database principal

1.The transaction Log record from DML transaction would be inserted into transaction Log buffer.

2.The transaction log buffer would then be written to disk that is hardened and at same time the Log buffer would be sent to Mirror server and principal will wait for confirmation from mirror server.

please note the commit has still not been given for transaction in Log buffer

3.The mirror will receive log records in its transaction log buffer and it will write to disk and the notify principal that it has hardened the piece of log record

4.Principal would receive acknowledgement and then COMMIT for transaction would be entered in log record buffer

As you can see now commit of transaction is entered into log buffer and hardened but SQL Server still does not confirms this as committed transaction

5.Now same process for commit would be followed as above it would be hardened to disk and log record containing commit would be sent to Mirror and then it would harden it send acknowledge and replay transaction logs.

Now transaction is actually committed

I dont agree below

The write is hardened on S2 (but not yet hardened on S1)

You must say commit instead of hardened.Like I said above hardening of Log records containing commit is done on principal first and at same time it is sent to mirror but transaction actually is considered committed only after principal receives information from mirror that Log records containing commit which it sent to mirror has been hardened. It also records the mirroring failover LSN and then after this it sends acknowledgement to principal.

Principal waits for the completion of its own I/O and the I/O of the mirror before considering the transaction complete. When the principal receives its response from the mirror, the principal can then proceed to the next hardening.

S1 loses power and the commit log record is never written

No this IMO is not correct inference like I said above before sending log records containing commit principal first hardens it on disk. SQL Server would not rollback the transaction if it does not receives confirmation from mirror transaction would still be open

A very important point failures to commit on the mirror will not cause a transaction rollback on the principal.

I would like you to read Table 9 Section in This Link

Improve this answer
11
  • When I say "harden" I mean "hardening of the commit record". This is the only record that counts.; There is no difference between hardening and considering the transaction committed. If immediately after hardening the power fails the transaction will be considered committed after startup. The in-memory state is lost. SQL Server has forgotten that maybe it need acknowledgement from the mirror.
    – usr
    Oct 20 '14 at 19:30
  • 2
    The problem, @usr, is you're assuming that no negotiation happens between the principal and the mirror when the databases start back up. The negotiation will happen and the databases will "agree" on the LSN. In your situation no success is sent to the client, the databases would negotiate a starting point, and it would keep going on. The client thinks the connection was cut and the transaction lost, so everything is at the same place.
    – Sean Gallardy - Mostly Retired
    Oct 21 '14 at 12:47
  • 2
    @usr - There is a way to rollback, the log is there. I don't know the exact specifics, but the negotiation does happen. In most cases, S2 wouldn't commit before S1 due to the extra wire time but in the rare case that it could there are built in mechanisms to deal with it. Now your question CHANGES dramatically if you say the write occurs on S2 and immediately before hardening on S1 there is a manual failover that allows data loss. In THAT case the values would exist on S@ and when S1 came back up, the LSN would be negotiated and mirroring would catch up, if possible.
    – Sean Gallardy - Mostly Retired
    Oct 22 '14 at 3:53
  • 4
    Sean is correct here. Any time a partner is offline, when it comes back online, the partners compare their log files and sync them. The commit will always be rolled back or committed on both partners before mirroring resumes.
    – Robert L Davis
    Nov 21 '14 at 4:06
  • 1
    @RobertLDavis so you are saying the mirror can also send log records to the primary in case it happens to be further along in time? (I have shown in detail how that can happen.) That would pretty much answer the question.
    – usr
    Nov 21 '14 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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