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

We have a system that uses SQL Server 2008 in tandem with MongoDB, using the latter for a lot of ad-hoc reporting features. They don't intersect much, but there are one or two places where they do actually need to work together.

I was always slightly concerned about the transactional implications but figured it was not really a big problem if the application did the Mongo work first, and it technically isn't that big of a problem if a transaction fails in the middle once in a while. But a recent bug came up that was causing them to consistently fail, and although I've fixed the bug that caused it, it made me realize just how much of a nuisance it is that I can't just throw a distributed transaction over the whole unit of work.

Given one database that supports distributed transactions (SQL Server 2008) and another that doesn't really support any ACID semantics (MongoDB), is there some way I can structure the application code 开发者_JS百科so that a unit of work ("transaction") either succeeds or gets rolled back in both databases?

Obviously I would have to use some extra columns/keys to track the status of the transaction - but what, and in what context?

What are your consistency requirements? Is it ok if ad-hoc reporting on the Mongo is not always entirely up-to-date?

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.

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<MongoDocs>();
 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<MongoDocs>();
 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.

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:

1. 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
2. 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 think that u can create specail resource managers for that cases via IEnlistmentNotification or ISinglePhaseNotification.