1

Sometimes we have to use multiple databases in one project with the intentions of:

  1. Denormalizing data or read models

  2. Using the advantages of that database (technology)

There has been a lot of talk about the 1st case, but I haven't found anything about the 2nd case

Example 1: To solve the complexities of access in an organization based on the organizational chart, we used graph databases such as neo4j.

Example 2: We want to make some system forms (entities) dynamic and allow users to add new fields to them and store these added values in nosql.

Considering that most use cases are better to be done transactionally, we use unit of work But this pattern is not compatible with several databases

The primary solution that comes to my mind is to use the SQL database as the main database, and other databases store the data as a shadow (or a projection of the data) and always sync with the main SQL database (sync with outbox pattern and events).

I emphasize again that this shadow data is not intended to read the model and has an effect on write side of program (business logic).

Thank

Update: Complete example

Suppose we have a system that uses the outbox pattern to ensure that events are sent, and at the end of the unit of work, they are committed.

Org chart data is supposed to be inside neo4j

That is, AddChartUseCase was executed, it must save the data in neo4j and the event must be saved in the outbox (ChartCreatedEvent).

How to have a unit of work that manages a transaction between two databases (I think the answer is: impossible)

7
  • @DocBrown i added it , thnks
    – PersianMan
    May 30, 2023 at 12:01
  • 1
    Can't UoW manage multiple data sources / transactions at the time?
    – Laiv
    May 30, 2023 at 12:29
  • @Laiv I think it can't
    – PersianMan
    May 30, 2023 at 12:39
  • 2
    Well, I have checked Fowler's book and it doesn't (explicitly) state that a UoW can not span multiple databases, but let's assume UoW is bound to a single data source. If that's true, then the "how" doesn't matter because it's beyond the scope of the pattern. Right?
    – Laiv
    May 30, 2023 at 13:34
  • 1
    Like @Laiv explained in his answer, Distributed Transactions are cumbersome to implement. If both databases are "owned" by the same service and, depending on your consistency requirements, you might just want to use a more traditional approach using a data synchronization tool that can perform partial and full syncs and can be triggered by an event (domain event or time triggered event) of just manually.
    – Sil
    May 31, 2023 at 11:21

2 Answers 2

2

"Unit of Work" is not necessarily incompatible with multiple databases. It is incompatible with different services / processes, where each service has its own storage system.

Inside one service, an UoW could theoretically hold two or more different database contexts, execute a combined transaction on both and manage a manual rollback on one of the databases when a commit operation on the other database fails. That, however, means you would have one service using two databases simultanously and synchrounously, which can easily introduce scaling issues.

Since you mentioned the Transactional Outbox pattern, I think the architecture you have in mind looks different: each database is under control of a different (micro-?) service, which means you need a distributed transaction. "Outbox", which is a common way to implement this, splits a business transaction into two or more technical transactions, at least one for each service.

That way, you can use an UoW to

  • collect the CRUD operations inside one service for it's database, as well as the commands necessary to update dependend services

  • when it comes to COMMIT, apply the CRUD operations as well as the INSERTs into the outbox table with the forementioned commands as the payload.

A dependend service will then get an event, generated by some message relay and message broker from the Outbox, and will have to interpret the payload accordingly. Maybe for interpreting the event they will have to create an UoW on their own, this will depend on the overall complexity. In the worst case, a failing transaction here needs to generate an event to rollback the first transaction again (but this is case dependend, and when your different databases are interwoven to this degree, then you may reconsider your choice to split the data into different DBMS).

TLDR; UoW is an implementation detail of one service, and when your databases are under control of different services, and your business transactions span multiple service, one UoW alone will not be sufficient to implement a business transaction.

1

In addition to @DocBrown's answer.

Allow me to be a bit more "expeditious".

How to have a unit of work that manages a transaction between two databases (I think the answer is: impossible)

If you ask how to link both data sources' transactions into a single ACID-wise one, the answer is distributed transactions. The bad news is, they aren't easy to implement. At least not simple enough to do it from a UoW.

Distributed transactions are also hard to implement because not all data sources support these transactions. When they don't, it's required a custom implementation. Custom implementations come in two flavours: middleware (e.g. application servers) or custom Transaction Managers (e.g. libraries). In both cases, the UoW ask for new connections to this new "layer" and operates it as if it was a regular DB transaction. Under the hood, the transaction manager/middleware does all the hard job.

If you are to implement a custom transaction manager, given the short-living nature of your transactions, the Two-phase-commit algorithm looks like a good starting point.

Now bear in mind the following; you can't assume that all data sources implement ACID transactions. Hopefully, your databases will, so you can open connections, create transactions, save (commit) data or revert changes (rollback) symmetrically, but if one of the databases doesn't support rollbacks or ACID transactions, then you have to provide the transaction manager with custom failover strategies. For example, when rollbacks aren't supported, the transaction manager has to execute compensative transactions. These transactions need transaction management too... As you may guess, not a trivial thing to code.

2
  • How do they implement in such cases? An implementation of uow with two databases with minimal complexity
    – PersianMan
    May 31, 2023 at 11:43
  • Transaction management is a whole domain per se. It's quite complex, to be honest. I doubt anyone has solved this at such a high level. Now make yourself the following questions: why do you need it? What functional or non-functional requirement I am solving? Is it really a need or just a wish of mine? I don't know all the context but unless both transactions are critical for the business, i don't think you need DT
    – Laiv
    May 31, 2023 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.