4

Morning all,

I am working on a project where I am saving data to Mongo DB and also to Sql Server (Entity Framework code first). There are a number of scenarios where I carry out a write to each database in a single block of code. I'm wondering, can any of you suggest a way to handle this in something similar to a transaction ? such that if any part of the code block fails, the whole thing should fail and rollback ?

Kieran
  • 186
  • 2
  • 13

2 Answers2

4

I don't think there's any bullet-proof way of doing this since you not only have two separate connections but also two different architectures.

Assuming your SQL Server has the more complicated data model (the one that's more likely to fail for some reason) I came up with an approach in the past that worked for me:

  1. Execute the operations sequentially not both at the same time
  2. Execute the SQL satement first, if it fails don't execute the MongoDB statement and you'll be consistent
  3. Should it succeed, execute the MongoDB statement next
  4. If the MongoDB statement fails write an error log. Make sure the log is not on a remote machine so that the possibility that the logging could fail is as small as possible.

You can later use the error log to either manually or automatically salvage your data. In any case you should implement a retry policy for all statements, since the most likely reason for a failed operation is (given your code is correct) a timing issue and retrying solves this in general. If you're doing it right there will be maybe like one exotic error a month.

Of course in step 4 you could try to revert the SQL operation, instead of (or in addition to) writing a log entry. But this is mostly cumbersome and leaves you with the question what to do should the revert fail.

Maybe there still is some magic middleware to integrate MongoDB statements into an SQL transaction but for the time being I would just acknowledge that data consistency and using different databases are opposing goals. Try to monitor errors closely and reduce the error potential.

Peter
  • 1,361
  • 15
  • 19
1

Two Phase Commit will suit your scenario. In a single transaction we can hit any number(normally we use two) of DB's and maintain our Data synchronized across the DB's.

More info on Two Phase Commit

https://lostechies.com/jimmybogard/2013/05/09/ditching-two-phased-commits/

https://www.coursera.org/learn/data-manipulation/lecture/mXS0H/two-phase-commit-and-consensus-protocols

https://sankarsan.wordpress.com/tag/two-phase-commit/

Read this post

How to force only one transaction within multiple DbContext classes?

Community
  • 1
  • 1
Clement Amarnath
  • 5,301
  • 1
  • 21
  • 34