local or xa datasource

[ashvish]

Hi,

I have an enterprise application that is configured to use just one local-tx datasource (ds1). The transaction isolation level is set to transaction serializable. As part of reducing database contention, I was thinking of configuring another local tx datasource (ds2) which would be used only for reading committed data from the database for example use for reports. But while experimenting with the same I find that while ds1 is in the process of doing read and save operations to the db, a read operation using ds2 waits till ds1's transaction is over. Is it possible to overcome this by using xa-datasource? If so is jTDS driver the best available option to use? I have searched a lot about this, but haven't found any concrete solution yet. Any suggestions would be highly appreciated. JBoss version being used is 4.

Thanks

Ashvish

[wdfink]

you use the same DB for both DS right?

In that case I suppose that the DB is aware that both Tx are the same.

An XA-datasource will not help, it is just a protocol to enlist more than one resource in one transaction.

You might try a no-tx datasource, see config datasources.

Also you might rethink the design maybe you can use another isolation level, SERIALIZABLE will be very restrictive and might slow down your application.

[ashvish]

Yes I am using the same DB for the same DS. I wonder if the issue has got something to do with the read call coming from a connection in a different pool? The same read call from the first connection pool doesn't face any problems, I wonder why?

[wdfink]

I think it will be an issue of Tx demarcation. Depend on what connection, isolation level and DB (driver) you use the two connections are in the same or different Tx.

I'm not sure whether a no-tx-datasource will non-block read from a DB where a Tx is running with 'isolation serializable'.

So if you try to achieve a higher performance you should think about 'optimistic locking' that will be a common approach for high parallel access.

[ashvish]

The interesting thing is that I did some load test using the 3 isolation levels serializable, repeatable read and read committed but couldn't notice any significant improvement in performance.

[wdfink]

Such test are difficult, if you have no conflicts (e.g. change/read records in parallel) you will not see any difference.
unfortunately this can change if you go life and increase load or change the client behaviour.