two diffrent database in unit of work

Question

I will explain my problem in the form of an example. Suppose we want to use both databases in a transaction. Data is edited in database 1 (for example, Postgres) and then added to database 2. Finally, we want to commit the changes (uow.commit()). It is possible that the first database is committed and a problem occurs when the data is committed in the second database.

I used unit of work and outbox pattern but this problem was not solved.

My unit of work has two different databases, neo4j, postgres (I don't know if this is correct or not, or how many databases can one unit of work have?) Both databases are partners in the logic part of the program and none of them are read-only.

I have no insistence to use unit of work or outbox, etc. and I want my transactions to be completed successfully in all my databases and if one of them faces a problem, none of them will be done.
I prefer not to use complicated methods like 2 phase commit.

Is there a pattern to solve this problem or do you have a suggestion or a solution? thanks

this is my unit of work.

class UnitOfWork(AbstractUnitOfWork):

    def __init__(self, postgres_session_factory=DEFAULT_SESSION_FACTORY, neo4j_driver=NEO4J_DRIVER):
        self.session_factory = postgres_session_factory
        self.neo4j_driver = neo4j_driver

    def __enter__(self):
        self.session = self.session_factory()  # type: Session
        
        self.neo4j_session = self.neo4j_driver.session()
        self.organization = repository.OrganizationRepository(self.session)
        self.organization_chart = repositories.OrganizationChartRepository(self.session)

        self.organization_chart_node = repositories.OrganizationChartNodeRepository(self.neo4j_session)
        return super().__enter__()

    def __exit__(self, *args):
        super().__exit__(*args)
        self.session.close()

    def _commit(self):
        self.session.commit()
        self.neo4j_session.commit()

    def rollback(self):
        self.session.expunge_all()
        self.session.rollback()

Does this answer your question? Using multiple databases in a bounded context — Doc Brown
– Doc Brown, Commented Jun 6, 2023 at 11:53
"Both databases are partners in the logic part of the program and none of them are read-only." - this is often a design flaw when transactional consistency is required. — Steve
– Steve, Commented Jun 6, 2023 at 14:52

Doc Brown · Accepted Answer · 2023-06-06 20:09:36Z

Across two or more different database systems, you cannot expect a transaction to work out-of-the-box in a cross-database manner.

Hence, if you want two databases to be involved in one transaction, you need to rollback the first commit manually in case the commit for the second one fails. This means, if you added some records, you have to delete them. If you deleted some records, you have to remember the original ones in-memory or in some log and re-insert them. If you changed some records, you have to undo theses changes. Something along the lines of this (pseudo)code:

def _commit(self):
    try:
         self.neo4j_session.commit()
    except SomeException:
         self.rollback()
         return
    try:
        self.session.commit()
    except SomeException:
         self.rollback()
         // ...
         // here you have to add some code to rollback
         // the last operations on neo4j before the 
         // latest self.neo4j_session.commit() manually
         // ..
         self.neo4j_session.commit()

Of course, when the manual rollback fails at the end, this might leave the two databases in a some unsynchronized state. This is not really avoidable, hence you will have to choose carefully the order in which you apply those commits. I would also recommend to design the system in a way that it can tolerate certain inconsistencies for a while, or think of some clean-up process which resolves such inconsistencies regularly and asynchronously at a later point in time.

For more complex approaches which work for distributed systems, inform yourself about approaches for distributed transactions like two-phase commit or the Saga pattern.

@JimmyJames: don't know, I am not an expert on this. But I found this interesting link, which compares several distributed transaction approaches for micro services, and XA is mentioned as one potential 2PC implementation. My answer goes clearly under "Modular Monolith", which is I guess what the OP is after. — Doc Brown
– Doc Brown, Commented Jun 6, 2023 at 21:17

Stack Exchange Network

two diffrent database in unit of work

1 Answer 1

Your Answer

Linked

Hot Network Questions

two diffrent database in unit of work

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions