Transaction manager for service method that carries out DB operations on tables from multiple schemas
hibernate, java, spring, transactions
Solution
If you need to coordinate multiple transactional resources, then I think your only option is using distributed transaction management with JTA. This article explains how you can configure Spring to use a JTA transaction manager.
Edit:
After doing some research, it seems that you have a much better option if your three datasources really just point to different schemas (which are the same thing as databases in mysql) hosted by the same server. If that is the case, and you have a single user which is used for all your three connections, then you can basically merge them into one single connection, and use `@Table` annotations on your entities to specify the schema they belong to, like: `@Table(schema = "schemaX")`.
This can be done because the database name given in the connection URL only specifies the default database (see doc), but you can still execute SQL statements that qualify table names with other database names, which is exactly what the `@Table` annotation causes to Hibernate.
Having now one single connection resolves your original problem of doing atomic updates in tables from different schemas.
Problem
- The application is in Spring + hibernate. - It has a process under which mysql tables from 3 schemas get updated. - I have different dataSources, sessionFactories and transactionManagers for each schema. - I have 3 DAO classes carrying out DB operations on their respective schema tables. - Since all the DB operations are supposed to be under one single transaction, I am calling those dao methods from one service method. But the problem is that I don't know how to annotate this service method `@Transactional`. Transactional annotation accepts only one transaction manager, but I am having 3. Is my approach wrong? How can I solve this?