What is the 'best' way to do distributed transactions across multiple databases using Spring and Hibernate

up vote9down votefavorite 3

I have an application - more like a utility - that sits in a corner and updates two different databases periodically. It is a little standalone app that has been built with a Spring Application Context. The context has two Hibernate Session Factories configured in it, in turn using Commons DBCP data sources configured in Spring. Currently there is no transaction management, but I would like to add some. The update to one database depends on a successful update to the other. The app does not sit in a Java EE container - it is bootstrapped by a static launcher class called from a shell script. The launcher class instantiates the Application Context and then invokes a method on one of its beans. What is the 'best' way to put transactionality around the database updates? I will leave the definition of 'best' to you, but I think it should be some function of 'easy to set up', 'easy to configure', 'inexpensive', and 'easy to package and redistribute'. Naturally FOSS would be good. java hibernate spring transactions xa share|improve this question edited Jul 23 '13 at 21:11 Arjan Tijms 19.3k53472 asked Sep 24 '08 at 17:01 Vihung 2,77542143

add comment

4 Answers

activeoldestvotes

up vote4down voteaccepted

The best way to distribute transactions over more than one database is: Don't. Some people will point you to XA but XA (or Two Phase Commit) is a lie (or marketese). Imagine: After the first phase have told the XA manager that it can send the final commit, the network connection to one of the databases fails. Now what? Timeout? That would leave the other database corrupt. Rollback? Two problems: You can't roll back a commit and how do you know what happened to the second database? Maybe the network connection failed after it successfully committed the data and only the "success" message was lost? The best way is to copy the data in a single place. Use a scheme which allows you to abort the copy and continue it at any time (for example, ignore data which you already have or order the select by ID and request only records > MAX(ID) of your copy). Protect this with a transaction. This is not a problem since you're only reading data from the source, so when the transaction fails for any reason, you can ignore the source database. Therefore, this is a plain old single source transaction. After you have copied the data, process it locally. share|improve this answer answered May 20 '09 at 11:38 Aaron Digulla 127k22177334

1   Distributed Transactions must statisfy all 4 ACID (Atomicity, Consistency, Isolation, Durability) properties. What's your problem? The scenario you described cannot happen, as the managers are communicating with each other and do only commit when all participating nodes have exchanged a "GO". –  Falcon Jan 5 '11 at 11:24  2   @Falcon: So what happens if the network fails between PREPARE and COMMIT? Or one of the server dies? "cannot happen" can't happen in reality. –  Aaron Digulla Jan 7 '11 at 17:05 add comment

Did you find this question interesting? Try our newsletter

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

 

up vote5down vote

Setup a transaction manager in your context. Spring docs have examples, and it is very simple. Then when you want to execute a transaction: try { TransactionTemplate tt = new TransactionTemplate(txManager); tt.execute(new TransactionCallbackWithoutResult(){ protected void doInTransactionWithoutResult( TransactionStatus status) { updateDb1(); updateDb2(); } } catch (TransactionException ex) { // handle } For more examples, and information perhaps look at this: XA transactions using Spring share|improve this answer edited Sep 24 '08 at 17:29 answered Sep 24 '08 at 17:20 Curious Developer

add comment

up vote2down vote	In this case you would need a Transaction Monitor (server supporting XA protocol) and make sure your databases supports XA also. Most (all?) J2EE servers comes with Transaction Monitor built in. If your code is running not in J2EE server then there are bunch of standalone alternatives - Atomicos, Bitronix, etc. share|improve this answer answered Dec 28 '08 at 17:15 maximdim 4,4591022
	add comment

up vote0down vote

When you say "two different databases", do you mean different database servers, or two different schemas within the same DB server? If the former, then if you want full transactionality, then you need the XA transaction API, which provides full two-phase commit. But more importantly, you also need a transaction coordinator/monitor which manages transaction propagation between the different database systems. This is part of JavaEE spec, and a pretty rarefied part of it at that. The TX coordinator itself is a complex piece of software. Your application software (via Spring, if you so wish) talks to the coordinator. If, however, you just mean two databases within the same DB server, then vanilla JDBC transactions should work just fine, just perform your operations against both databases within a single transaction. share|improve this answer answered Sep 28 '08 at 12:11 skaffman 177k29363452

add comment