1 of 1 people found this helpful
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.
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?
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.
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.
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.