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.
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.
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.