A very high level description of our test set up is:
- Java application is running on Jboss (WildFly)
- Using Oracle as database server
- XA transaction between IBM MQ and Oracle data sources
- More than 100 concurrent transactions doing MQ GET/PUT and SQL inserts/read/update/delete
- More than 1000 transactions per seconds to be processed
- Each transaction is doing about 100 SQL inserts + some reads (some of the transactions are also doing a few delete and updates)
- 2 Jboss nodes with 32 CPU's and 32 CPU oracle database server
Each transaction will connect to about 3 data sources. However, all data sources are on the same data base instance. I have been wondering if there is an overhead in having multiple data sources against same data base instance when having an XA transaction. One of the reason i ask, is that we are now struggling with the "enq: DX - contention" waits in Oracle.
I have been trying to google without finding a clear answer. it seems like Oracle have some kind of optimizations for this scenario https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/distributed-transactions.html#GUID-2C258328-7DFD-42ED-AA03-01959FFE924A (32.3.4 Oracle XA Optimizations) However, as mentioned above, we are struggling with "enq: DX - contention" waits
Any insight will be helpful
I see serveral problems in your solution if you are using multiple datasources to the same database.
If you have 3 Datasources you have 3 seperate connections to the database. So you also have 3 parallel database transactions.
First of all you have overhead in your application because you have to synchronise the transactions on the application level. On top this 3 transaction are then syncronized via XA. This might cause a huge performance problem.
If you have 3 transactions you need 3 commits which might be slower than one commit.
The 3 DB transactions might run into deadlocks because all 3 are trying to modify the same or related data.