Is it possible to emulate a distributed transaction with a database other than ACID?

Question

We have a system that uses SQL Server 2008 in tandem with MongoDB, using the latter for many special reporting functions. They don’t intersect a lot, but there are one or two places where they really need to work together.

I was always a little worried about the transactional consequences, but decided that this is not a very big problem if the application first performed Mongo work, and technically it is not so much for the problem if the transaction fails in the middle once at a time. But a new error occurred, due to which they constantly failed, and although I corrected the error that caused it, it made me realize how much this is due to the fact that I can’t just throw out a distributed transaction over a whole unit of work.

Given one database that supports distributed transactions (SQL Server 2008) and another that does not actually support any ACID semantics (MongoDB), you can somehow structure the application code so that the unit of work ("transaction")) is either successful, or rolled back in both databases?

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

Answer 1

What are your consistency requirements? Is it normal if the Mongo special reporting is not always fully updated?

If not, then I think it’s not easy for you.

If so, then I think I’ll come up for transactional MSMQ with SQL Server, and then I’ll configure one (or several) services to update Mongo by processing messages from the queue.

Answer 2

, , ID . mongo, - .

..

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
  }
}

mongodb sql.

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();                 
      }

    }

: Aaronaught.

, SQL , (.. ), , SQL Server.

, , mongo 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
      }

  }

, sql, mongo.

Answer 3

Ramesh Vel , , .

ACID - , . , catch, , try, .

, . , , , :

• . , , , , . , ThreadAbortException, . .
• . , . safe state ? / . , . ,

, ACID ity , ACID, , , SQL code commit, ( ).

. , , (.. / ), ACIDity , , +1

Answer 4

, u specail IEnlistmentNotification ISinglePhaseNotification.