Unit of Work / Repository pattern updating multiple sources at the same time

Question

I have implemented unit of work / repository pattern for SQL Server and MySql. They work great when I have to update either database server.

A new requirement has come in - for one object type, I need to update both SQL Server and MySql at the same time. Ideally, this would be done in a transaction.

Can someone please suggest a way to implement this?

Answer 1

If you are using .NET you can take a look at TransactionScope class. It allows you to create distributed transaction over two separate db connections as shown here. You will need to check what additional configuration is required for MySql to participate in this transaction though.