How to work with database B within the scope of Spring-managed transaction on database A, all without XA?

Question

To begin with, this question is not about applying an external XA transaction manager, as this would definitely solve the problem. The question is how go around without it assuming the following:

• Transactions in both databases are executed within a single Java thread
• Data is read and written from/to both databases, as well as other non-transactional sources and destinations
• Uttermost data consistency between two databases is NOT required
• Optimistic 1PC commit is acceptable (failed 2nd commit should not trigger rollback for 1st database)
• Programmatic or declarative transaction scope control are both ok

Now let's suppose the following scenario:

1. Begin transaction X for db A
2. Read and write some data in db A
3. Based on that data, sometimes I need to use db B
   • (keep working in the same Java thread)
   • Begin transaction Y for db B
   • Read and write some data in db B
   • If something fails here, rollback BOTH transactions
   • Commit transaction Y
4. Effectively resume transaction X
5. Read and write even more data in db A
6. If something fails here, rollback transaction X only
7. Commit transaction X

The way I feel it, everything calls for a "nested transaction" solution, which is fortunately supported by Spring DataSourceTransactionManager. The issue is, Propagation.NESTED assumes that both transaction X and Y are executed in the same database (DataSource), and possibly over the same underlying JDBC Connection. But this is clearly not the case I have, as databases have individual connections and are able to support independent transactions.

Another possible solution I tried is to create two DataSourceTransactionManager instances, one for each database. From the first glance, it looks a cleaner solution - but then I realized that standard Spring classes rely heavily on static, thread-local fields, thus guaranteeing stomping on each other when trying to use two managers concurrently by the same thread (see assumption above). No go.

Now I am thinking about subclassing all relevant Spring transaction management classes to "separate" those shared static fields between packages. It feels like inventing a bicycle, though, so I would prefer not to do it.

As external XA transaction manager is seen as an overkill (due to very loose consistency requirements, see above), is the only solution to go down on JDBC level and programmatically manage transaction Y (begin, read, write data, commit)? Or am I missing some advanced concept in spring-tx?

Answer 1

I'm not a Spring expert (thus I can't say anything to the idea of subclassing) but what I know the Spring transactions uses abilities of JTA.

As you said the DataSourceTransactionManager works just per resource and NESTED functionality is possible because of the JDBC api and its functionality to work with safepoints (https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#setSavepoint--). This ability is limited to one Connection.

I think you can go with manual JDBC management as you suggested. Or I would still consider the transaction manager. The transaction manager not only manage XA transactions but it provides the implementation of the JTA api for you can use the declarative or programmatic approach. The most overhead for the transaction management is this XA handling - the data needs to be saved to the drive during prepare at the application and at the database side. If you use only the transaction management capability with non-XA resource then transaction manager gives you the JTA api to run transaction, does not provide the consistency (it's not what you need) and not using the XA overhead.

If you use transaction manager and two non-XA resources (DriverManagerDataSource) then you can drive transactions like - begin - update data - suspend #1 - begin - update data - commit #2 - resume #1 - commit.

Unfortunately, your particular case fits the best the nested transaction model which is not supported in JTA. But even with the NESTED Spring scope this case is precisely what you need. The nested works in way that if the nested transaction is rolled-back then the outer transaction is not rolled-back automatically. In other words rollback of the nested transaction (transaction Y) does not mean the outer transaction is rolled-back as well (transaction X).