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

by

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.

More Related Contents:

  1. Why does Hibernate disable INSERT batching when using an IDENTITY identifier generator
  2. Transaction marked as rollback only: How do I find the cause
  3. Do you need a database transaction for reading data?
  4. javax.transaction.Transactional vs org.springframework.transaction.annotation.Transactional
  5. How to rollback a database transaction when testing services with Spring in JUnit?
  6. HibernateException: Couldn’t obtain transaction-synchronized Session for current thread
  7. Spring, JPA, and Hibernate – how to increment a counter without concurrency issues
  8. Does Spring @Transactional attribute work on a private method?
  9. How do I call the default deserializer from a custom deserializer in Jackson
  10. Mapping ManyToMany with composite Primary key and Annotation:
  11. Spring Boot – Handle to Hibernate SessionFactory
  12. How to fetch FetchType.LAZY associations with JPA and Hibernate in a Spring Controller
  13. Unable to get spring boot to automatically create database schema
  14. No serializer found for class org.hibernate.proxy.pojo.bytebuddy.ByteBuddyInterceptor
  15. Exception NoClassDefFoundError for CacheProvider
  16. Can I combine a @Query definition with a Specification in a Spring Data JPA repository method?
  17. java.lang.NoSuchMethodError: org.hibernate.SessionFactory.openSession()Lorg/hibernate/classic/Session
  18. Hibernate-sequence doesn’t exist
  19. Could not commit JPA transaction: Transaction marked as rollbackOnly
  20. Spring JpaRepository – Detach and Attach entity
  21. Rollback transaction after @Test
  22. How do I properly do a background thread when using Spring Data and Hibernate?
  23. Getting a ‘No thread-bound request found’ error from spring in my web app
  24. How to map a JSON column with H2, JPA, and Hibernate
  25. How to configure transaction management for working with 2 different db in Spring?
  26. Hibernate: CRUD Generic DAO
  27. Mapping JPA or Hibernate projection query to DTO (Data Transfer Object)
  28. org.dbunit.dataset.NoSuchTableException: Did not find table ‘xxx’ in schema ‘null’
  29. Registering a SQL function with JPA and Hibernate
  30. TransactionRequiredException Executing an update/delete query

Leave a Comment