Multiple Database - Sanity Check

[bungrudi]

Hello,

We are planning to offer our client SaaS model, therefore have a requirement to use multiple databases (as many as 50) in one seam application. Those databases will have exactly the same structure. Here's what we are doing:

- Like the classic model that have been asked here several times, the database connection will be chosen based on the logged in user. For this we are planning to use the solution in this thread http://seamframework.org/Community/MultipleDatabases .

- There are transactions that span multiple databases, e.g. inter bank transfer, where we should do debit operation in one database and credit operation in another. For a particular transactions, there will be at most 3 different dataSource involved. For this, we’ll setup JTA/XA transaction manager and list all those 50 dataSource to participate in it.

Are these sound sane? How would other implement these?
On point 2, will there be a performance degradation related to the number of resource participating in a transaction? (I’m sure there will, but has anyone sanely enlist 50 resource in one transaction when only 3 resource needed). Any other idea on this one?

Regards,

Rudi A.

[bungrudi]

After watching this presentation http://www.infoq.com/presentations/native-transactions-java-spring , i'll forget about XA and go with manual compensation instead.

[asookazian]

This is an interesting use case.  Typically, you must inject an EntityManager interface for each db that is involved in the distributed tx.  In your case, the complication is that for a particular user you don't know until runtime which db's will be required to be injected for that specific Seam component. 

The Spring video is very interesting and informative.  The Spring @Transactional is much more robust than the Seam equivalent.  For example, the readOnly attribute set to true (which was available since Spring 2.0), forces Hibernate into MANUAL flush mode, whereby the flush() never occurs and thus the performance hit for comparing object graphs for all cached version and potentially modified version of entity is eliminated.  Flushes are essentially suppressed using this attribute.

You can't do this with the Seam @Transactional.

What is manual compensation? 

[bungrudi]

Manual compensation means manual fix to failed data posting, which if it were in a transaction this will never happen. In the video, he addressed it in around 40th minutes IIRC.