Transaction: spring1.2 + hibernate + tomcat + multiple database

[entaroadun7]

Hi,
my application uses multiple databases (all with the same structure and one with another). I do some operation on this one original db and one db from this that have the same structure. I need transaction during this operation i.e. i save something to the first db and than to the another. Is it possible to do it on tomcat or do i need application server? Do I have to use JtaTransactionManager or can i use i.e. HibernateTransactionManager. Can somebody provide me with some configuration (including transaction) when there are 2 different db (on the same server) and their uses diffrent DAO's and the same transaction manager ? (situation like this)
DB1
DAO1
tx1

DB2
DAO2
tx1

ps. i'm using spring 1.2

[Chris Davies]

Hi,

It sounds like you're looking for a distributed transaction manager capable of two-phase commits. I've never played with any of these before (prior to Googling a second ago, I thought you had to pay big bucks [Oracle] to get this sort of thing), but you might want to check this out: http://jotm.objectweb.org/

Apologies for a shallow reply - I'm sure someone with industry experience in this area will be able to serve you better. =) Might give you something to read in the meantime though!

EDIT: I think you'll find this article very interesting: http://www.javaworld.com/javaworld/jw-04-2007/jw-04-xa.html

[Jean]

I'm designing an architecture with the same requirements almost.

I've been researching on the Distributed Transaction Manager previously. There's three I've identified that can be used independently with Spring:
- jotm (http://jotm.objectweb.org/)
- Jboss one (http://labs.jboss.com/jbosstm/)
- Atomikos (http://www.atomikos.com/products.html#ate)

I'd tend to use the JBoss one since the two others haven't been updated in the past years. It's not necessarily a good reason though.

I would updated to latest Spring asap though if I were you. It's really a no brainer.

As for configuring Spring and Hibernate to use different datasources, I'm curious on how you would do that.
The way I see it, there should be one SessionFactory per datasource. Then DAO per SessionFactory. Service would then combine datasource usage by getting the required DAO.
Is it the way you built your layers?

Would Spring know how to automatically open XA transaction just by marking Service layer method as transactional?

[Marten Deinum]

Would Spring know how to automatically open XA transaction just by marking Service layer method as transactional?

Spring delegates that to a PlatformTransactionManager so if you registered a JtaTransactionManager spring will start a XA transaction (of course only if your datasources support XA as well!).



As for configuring Spring and Hibernate to use different datasources, I'm curious on how you would do that.
The way I see it, there should be one SessionFactory per datasource. Then DAO per SessionFactory. Service would then combine datasource usage by getting the required DAO.
Is it the way you built your layers?

Well you can switch the datasource quite easily however this only works for databases with the same structure. However as soon as you switched your datasource/connection the first one isn't going to commit anymore. (I used switching datasources in a situation where we had 1 application with multiple client which had there own databse).

In this case it thus doesn't apply.

[GuyPardon]

I've been researching on the Distributed Transaction Manager previously. There's three I've identified that can be used independently with Spring:
- jotm (http://jotm.objectweb.org/)
- Jboss one (http://labs.jboss.com/jbosstm/)
- Atomikos (http://www.atomikos.com/products.html#ate)

I'd tend to use the JBoss one since the two others haven't been updated in the past years. It's not necessarily a good reason though.


I can assure you that Atomikos has had continuous and constant updates - every since the inception 7 years ago!

If you are using JBoss, I recommend that you try Atomikos and enjoy the simpler configuration benefits.

Best
Guy
DISCLAIMER: I work for Atomikos - but still;-)

[Jean]

Guy, thanks for the update.
I'm glad to hear that.
I probably had the impression from the web site look & feel and from some date I saw on the download page (and that was from 2005 if I remember correctly).
I'm not using any yet, just doing pre research.

I'd like to ask you a question I've never been able to answer despite all my readings. Hopefully that'll be something clear for you since it's transaction related ;)

Since any atomical operation on a database occur in a transaction anyway, does it make sense to demarcate a transaction (in some Java code for instance, using Spring) for a set of read-only database operations, isolation level being repeatable_read??

To be more concrete, is it worth to demarcate a transaction with Spring at the service level when you know all the DAO operation included will be read only?

What does it bring? Is is an additional constraint on the DBMS?

I can't see any advantage here, except that it looks proper from the Java code. Since it's repeatable_read isolated, it doesn't block any reading, so it shouldn't incur any potential problem with the DB.

thanks
jean

[GuyPardon]

Hi,

XA makes sense (and also JTA at the service level) even for readonly: the XA protocol also makes sure that the DB locks are kept until the end of the transaction (needed to ensure correctness of the 'repeatable read' at the global level).

With distributed transactions, not just the individual transactions are important but also the way they are interleaved in each database. Without XA (and its locks) your readonly transactions could be interleaved with concurrent updates in such a way that you see inconsistent global data. In other words, global constraints cannot be guaranteed without XA.

The second part of XA is that it ensures all-or-nothing properties of global transactions, but that is less relevant for readonly.

Best
Guy

[Jean]

My question was in fact targeted for a single data source. But answer applies to both. thanks :)

So if I understand well, the locks in a repeatable_read isolation level are put for each READ operation, ensuring that a second read of the same data would be consistent.
So if I know that data will be read only once, why do I need the lock for?

If during the span of the transaction some data are updated by a second transaction, repeatable_read isolation ensure I won't be able to read them before the commit of the second transaction. But I will still able to read some data that have been updated during the transaction span.
Please correct me if I'm wrong. To me, that render the usage of read-only transaction (for simple read) not really useful. Unless you use the Serializable isolation.
In this case, when you open a transaction, what are the resources locked? Can only one transaction at a time be executed on the whole DBMS?

Sorry for messing around, but that pretty much represent my mind's confusion about transaction.
If you can help clear it up a little bit, I'd be very helpful!

cheer
-jean

[GuyPardon]

So if I understand well, the locks in a repeatable_read isolation level are put for each READ operation, ensuring that a second read of the same data would be consistent.
So if I know that data will be read only once, why do I need the lock for?


The locks are relevant for multiple DBs mainly. Suppose two transactions are there, T1 and T2. T1 updates at DB-1 and DB-2 whereas T2 reads at DB-1 and DB-2.

Without JTA/XA and the locks you could have:

T1 writes at DB-1 when T2 reads at DB-2
T2 reads at DB-1 when T1 reads at DB-2

Resulting in the interleaved execution of T1 and T2. This can be incorrect even if each read in itself is repeatable: you will see partial results of T1.



Unless you use the Serializable isolation.
In this case, when you open a transaction, what are the resources locked? Can only one transaction at a time be executed on the whole DBMS?


No, but the locks are more restrictive so that the result is _equivalent_ to one transaction at a time.

Guy

[Jean]

Interesting Marten, but I'm not quite sure I understand what you mean by:


Well you can switch the datasource quite easily however this only works for databases with the same structure. However as soon as you switched your datasource/connection the first one isn't going to commit anymore. (I used switching datasources in a situation where we had 1 application with multiple client which had there own databse).

Do you mean by 'switching datasource', injecting several SessionFactory in a DAO, so that you could use one single DAO on several datasource (provided their structure allows it)?

In my view, when concerned datasources have different structure, since you inject a datasource in a SessionFactory, then you would have different DAOs per SessionFactory.
And any transaction spanning several datasource would commit any change made through any of the DAOs. Am I right here?

--jean

[Marten Deinum]

Read my fried, read...



however this only works for databases with the same structure....




Do you mean by 'switching datasource', injecting several SessionFactory in a DAO

No I was talking about switching DataSource not SessionFactory.

Imagine 40 exact the same databases, 1 database for each client, you really don't want to instantiate 40 sessionfactories (if you want to run out of resources you want to do that). So you switch the datasource out depending on the request that comes in (the request identifies the client and with that you can switch to the correct database).

So in 1 transaction you cannot switch the datasource (hibernate still operates on the datasource/connection). As soon as you switch it and the session commits it will commit only the current datasource/connection not the one that got switched out.

For the full story read this blog post (http://mdeinum.wordpress.com/2007/01/05/one-application-per-client-database/) of mine.



any transaction spanning several datasource would commit any change made through any of the DAOs

That depends on your configuration and transaction setup.

[Jean]

OK, I do understand your case now.
SessionFactory and datasource/connection where closely associated in my mind. I guess your setup is an exception.

In most cases I suppose you manage your multiple datasources at the DAO level, each DAO getting its own SessionFactory injected.

I'm actually researching for a setup with two DBMS: one in ReadOnly mode and the other in read/write.
The RO one is a database used by another application, and the read process shall not disrupt or block any standard process occuring on it.

That's why I was wondering if RO XA transaction were needed in a read only mode. I'm still wondering how to justify its usage however.

With this configuration, my best bet would be to use a read_commited level for RO transaction on the readOnly DBMS, so that operations on this one doesn't get disturbed.
With repeatable_read, read data would be blocked for update operation during the span of the transaction.

On this other, since there can't be 2nd level caching on this RO DBMS - it's getting accessed by another application, modification wouldn't be noticed by the cache - repeated read might occur much more frequently.

In any case, I'll have difficulty convincing the customer that XA transactions are needed, cause in his view, we can just read the data as they are...

thanks for your tips!