Is it possible to emulate a distributed transaction with a non-ACID database?

If no, then I think you're in for a hard time. If yes, then I think I'd go for some transactional MSMQ along with the SQL Server, and then set up one (or more) services to update the Mongo by processing messages from the queue.

Transactional message queuing has been the best solution so far to this type of problem. It's not perfect, but in my case, since the Mongo database is just backing a web service app, the same app can force processing of any queued items before allowing anything to be pulled from Mongo. So even though it's impossible to guarantee consistency on the inside, at least those inconsistencies can be made invisible to the outside world.

– Aaronaught Jun 22 at 21:05.

May be you should introduce a new field in each document like Transaction ID. So this can be used to rollback your newly added documents from mongo if something goes wrong. Pseudo code.. Using (var tx=new transaction....){ try { var txID= random id; //your sql data insertion //Mongo db document insertion with tx id if (some problem) { rollbackSQL(); // and delete all the documents with the current tx id } } catch() { rollbackSQL(); // and delete all the documents with the current tx id } } Or you can do all your mongodb insertion on successful sql commit.

Var docList = new List(); Using (var tx=new transaction....){ try { //your sql data insertion docList. Add(mongoDoc); if (success){ sqlcommit(); foreach(var doc in docList ) { mongodb. Insert(doc); } } } catch() { rollbackSQL(); } } Update: To Aaronaught's comment.

The second code snippet here doesn't work at all, because the SQL transaction has already been committed before the Mongo insert is attempted, and if that insert fails (i.e. Broken connection), it's too late to roll it back in SQL Server. Its true, this can be solved by adding documents into mongo before sql commit var docList = new List(); Using (var tx=new transaction....) { try { //your sql data insertion docList.

Add(mongoDoc); if (success) { foreach(var doc in docList ) { mongodb. Insert(doc); } sqlcommit(); } } else { rollbackSQL(); } catch() { rollbackSQL(); // And delete all newly added mongo documents by looping docList } } Now you can make sure that commit only happens after completing both sql & mongo insertion. Cheers.

The second code snippet here doesn't work at all, because the SQL transaction has already been committed before the Mongo insert is attempted, and if that insert fails (i.e. Broken connection), it's too late to roll it back in SQL Server. The first snippet almost works, but the big problem is that finally block isn't truly guaranteed to execute and certainly isn't guaranteed to succeed if it does execute; a true distributed transaction remains in a consistent state even one of the nodes crashes temporarily, or the code servicing it fails completely (i.e.

Process termination). – Aaronaught Dec 6 '10 at 14:08 @Aaronaught, yeah you are right, my 2nd option doesn't work for your requirements. I have updated my answer, check out.. – RameshVel Dec 7 '10 at 5:08 The updated answer still has fundamentally the same problem, in that it's relying on an exception handler to delete the Mongo document.

Catch and finally blocks are not guaranteed to execute and may not succeed if they do execute. If the process somehow manages to get hosed before the SQL commit then I'm stuck with an orphaned document in Mongo. Again, what I need is something that emulates a two-phase commit and a write-ahead log; mookid is probably the closest in suggesting MSMQ, but unfortunately it's the wrong order of operations for this particular application.

– Aaronaught Dec 7 '10 at 15:13.

Answer by Ramesh Vel is very good, but there is another point you forgot. ACID is a paradigm that is fault tolerant. You assume that your transaction will either reach the catch block, then being rolled back, or the end of the try, then committed.In general, it's not true.

There are cases in which the DB must be kept consistent and none of the above conditions are met, like: Infinite loops. Supposing there is a bug in your code or in a method invoked by your code, the transaction might never end. Fix with a watchdog timer that throws ThreadAbortException is a viable way.

Deadlocks are treated the same way Hardware failures. Suppose the power goes down during the execution. What's the safe state of the DB?

You must roll back the whole pending transactions, and/or re-do them again. This is something you can't directly fix within your code in the example above. Commercial DBMSs have plenty of transaction logs that help restore the correct DB state In other words, it's not possible to obtain full ACIDity, in terms of full fault tolerance, when using a DBMS that is not designed for ACID operations, without actually implementing a software layer that stands between your code and the DBMS which performs SQL operations only after code commit, keeping a stable log of completed transactions (pending to commit after a restore).

This is a very complex matter. If you accept there are chances of your DB not to be consistent (ie. You're not a bank and/or you have manual consistency checkers to use after restore) you can emulate ACIDity with the snippet shown in the other answer, to which goes my +1.

I pointed most of this out in my comment to his answer. I've already thought of all of this, that is why I asked the question. I really wish you hadn't upvoted that answer because it's wrong, it doesn't actually address the original issue which is consistency across databases in the event of a failure.

Yes, I know that it involves writing a software "layer" between the code and databases; yes, I know it's complicated. Mentioning this doesn't really help me either. I wasn't asking someone to write the library for me, just a general algorithm or approach.

– Aaronaught Dec 6 '10 at 18:07 Seems it's all my fault in reading your question too fast without proper attention. About the approach, I gave you one: pending transactions. When you commit a transaction, you first write it down to file (should you replicate it?) and then perform every single operation in the transaction deleting then one by one from the file after they have been performed correctly.

You have to assume that once you can do commit via code, no consistency failure will occur. But **ACID**ity means independency and locks, and this get things more and more complicated (continued) – djechelon Dec 6 '10 at 19:05 So, again, it's not a viable solution to try to make ACID a DBMS that is not, while you can use commercial ACID-enabled products. Why?

Because all the possible issues that come from the ACID acronym will force you to actually write down a new DBMS ex novo over the existing DBMS. An effor that is not worth. If you are performing system integration over legacy systems, this may get more complicated than thought, because I may conclude that the requirements collide and integration is impossible.

This may be a starting point for seriously thinking about the matter. – djechelon Dec 6 '10 at 19:08.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.