6

Recently we are working on a POC to get Always on work and happened to see this article in BOL

http://technet.microsoft.com/en-us/library/ms366279.aspx

This article suggests that there would be logical inconsistency when we are dealing with Synchronous mode too, but will this actually be the case?

Consider for example databases A and B on which the transaction is running and A is in High-safety mode and B is not mirrored. The log of A has to go to Mirrored database then the Primary database commits eventually two phase commit(transaction on B) succeeds but article suggests that log will not be transferred in the first place and results in commit on B which is contradictory. Please help me in understanding Whether the statement suggested in above article is true. If yes how can it be :).

PS :Please let me know if I need to provide more information around this.

Improve this question

1 Answer 1

Reset to default
2

The article isn't well worded but here's what I think it's saying

Beginning scenario - DatabaseA is in an AG and is currently primary on Server1 and can run on Server2. DatabaseB can only run on Server1

T1 - Transaction begins T2 - Row inserted into DatabaseA (log not yet sent to Server2 though) T3 - Row inserted into DatabaseB T4 - Transaction commit called T5 - DatabaseB commits transaction (since the two DBs commit individually) T6 - DatabaseA fails over before log is sent

Since the log was never sent DatabaseA wouldn't have the row but DatabaseB would. If synchronous mode is being used that can't happen because the commit can't be called until the log from the row is sent. However, it may be possible for the commit on DatabaseB to happen and DatabaseA to fail over before the commit happens there. This may lead to a rollback in DatabaseA instead of a commit. I can't say with certainly that you can get into the same situation with synchronous mode but if you can that's how it would likely happen.

Improve this answer
2
  • Thanks for the reply :). but in synchronous mode...it will happen this way T1-tran begins T2-row inserted into db A...it hardens tlog-send the log to A1 T3-db b commits tran but waits for a to give ack back(as crsss db tran splits a single tran to two child tran). If at this situation A fails then the total tran should rollback and should not result in any logical inconsistency. Please let me know if I am getting wrong here
    – user11143
    Dec 6, 2012 at 4:44
  • Right. I don't know for sure if this can happen but if the child transaction on DatabaseB commits and the transaction fails to commit on DatabaseA due to the failover what will happen. Hopefully it'll get rolled back. However, I do see room for Bad Mojo happening. Someone with a lot more knowledge about the internals than me is going to have to provide the final answer.
    – cfradenburg
    Dec 7, 2012 at 14:47

Your Answer

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

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