Connection pool issues

[daiagou]

Moqui has two ways to configure Connection pool:
1.With "xa-properties":

<inline-jdbc pool-maxsize="140"><xa-properties serverName="${entity_ds_host}" port="${entity_ds_port?:'3306'}"
                    pinGlobalTxToPhysicalConnection="true" autoReconnectForPools="true" useUnicode="true" encoding="UTF-8" useCursorFetch="true"
                    databaseName="${entity_ds_database}" user="${entity_ds_user}" password="${entity_ds_password}"/></inline-jdbc>

2.Not with "xa-properties":

<inline-jdbc jdbc-uri="jdbc:mysql://${entity_ds_host}:${entity_ds_port?:'3306'}/${entity_ds_database}?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"
                         jdbc-username="${entity_ds_user}" jdbc-password="${entity_ds_password}"/>

I tested the two Connection pool and found some problems:
#1. In pressure testing situations (such as 300 thread pressure testing for 5 minutes), it is easy to encounter connection acquisition errors:

error enlisting a ConnectionJavaProxy of a JdbcPooledConnection from datasource transactional_DS in state ACCESSIBLE with usage count 1 wrapping com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@11fe8373 on com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@15eceed7

#2.The situation is quite complex. Please be patient and listen to my explanation.
The scenario is that a service executes first, and the service opens the transaction, so the autoCommit property of the connection used in the service is false. When the service finishes executing, the connection will be returned to the Connection pool. If the service executes normally, the autoCommit attribute of the connection returned to the Connection pool is true after the transaction is committed. If the service executes abnormally, the autoCommit property of the connection returned to the Connection pool is false!

Although this usually does not cause problems. But there is also a hidden danger. For example, in the following situations:

ec.getTransaction().commit();
EntityValue tccVoucherBatch = ec.getEntity().makeValue("").create();

The above code first commits a transaction. Normally, the insert operation below will be automatically submitted, but in concurrent cases, there is a probability of obtaining a connection with autoCommit false, which may result in the insert not being entered into the database.

[jonesde]

If I'm reading this correctly then my answer is: don't do that. If you do things manually with transactions then you have to do it correctly or it will not work. Creating/inserting a record without a transaction in place, as your code shows, is an example of that. Use transactions correctly, or it won't work. For the most part it is best to let the Service Facade manage transaction for you, and never commit a transaction you didn't begin. See the JavaDoc comments in the TransactionFacade.java interface for some examples.

[daiagou]

I agree with your statement. We should try not to handle the affairs ourselves, but rather hand them over to the service.

Perhaps I didn't express myself clearly, I'm very sorry. Let me add something. My question is: No matter how I handle transactions, the state of each link I obtain from the connection pool should be consistent. The AutoCommit attribute should be uniformly false, and should not be true or false occasionally. What do you think?

[jonesde]

Because of the nature of databases, JDBC, JTA, and implementations of JTA like Bitronix (and others like it), there is no guarantee of a valid state of connections from a connection pool, or the transactional status. Bitronix does support connection test statements, if configured in the Moqui Conf XML file, and that helps with things like connections in a bad state from network errors, database errors, transaction conflicts that haven't timed out properly, etc.

You threw out a bunch of stuff, and it generally isn't productive to talk about a bunch of what ifs... I can't really help with that, research or reproduce an issue, comment on what might be going on, help you fix a bug in your code or in Moqui or a dependency, etc.

For general advice: don't make assumptions or paint yourself into a logical corner. 90% of the time when I'm called in to help people solve a problem they are having trouble with the solution comes as a result of re-examining the issue and throwing out assumptions, designing to tests to answer unknowns, etc.