[dbcp] Unnecessary ROLLBACK's with BasicDataSource

Bernd Eckenfels

2018-08-21 16:39:22 UTC

Hello,

Can you proof that this puts additional pressure on the database? It does make some uneccessary round-trips but the database will just ignore the rollbacks when there are no open changes/transactions IMHO. Did you see somewhere on the database otherwise?

Using rolllback unconditionally to reset the state of transactions is easier and more reliable than actually trying to keep track. If it does indeed affect the database it would be a bit more work to come up with another solution.
Gruss
Bernd

Gruss
Bernd
--
http://bernd.eckenfels.net

________________________________
Von: Andrey Shcheglov <***@jetbrains.com>
Gesendet: Dienstag, August 21, 2018 5:01 PM
An: ***@commons.apache.org
Betreff: [dbcp] Unnecessary ROLLBACK's with BasicDataSource

Hello,

If BasicDataSource (version 1.4, as I'm limited to Java 1.6) is
configured with defaultAutoCommit=false, then two extra ROLLBACK's are
executed, one when a database connection is taken from the pool, and
another one when returning a connection to the pool.

T4CConnection(PhysicalConnection).rollback() line: 1950
PoolableConnection(DelegatingConnection).rollback() line: 368
PoolableConnectionFactory.passivateObject(Object) line: 685
BasicDataSource.validateConnectionFactory(PoolableConnectionFactory)
line: 1559
BasicDataSource.createPoolableConnectionFactory(ConnectionFactory,
KeyedObjectPoolFactory, AbandonedConfig) line: 1545
BasicDataSource.createDataSource() line: 1388
BasicDataSource.getConnection() line: 1044

and

T4CConnection(PhysicalConnection).rollback() line: 1950
PoolableConnection(DelegatingConnection).rollback() line: 368
PoolableConnectionFactory.passivateObject(Object) line: 685
GenericObjectPool.addObjectToPool(Object, boolean) line: 1379
GenericObjectPool.returnObject(Object) line: 1342
PoolableConnection.close() line: 90
PoolingDataSource$PoolGuardConnectionWrapper.close() line: 191

Thus, each successful database transaction is a sequence of:

1. ROLLBACK (connection taken from the pool)
2. do something with the database connection (application code)
3. COMMIT (application code)
4. ROLLBACK (connection closed)

(instead of a single COMMIT), while each failed one is a sequence of

1. ROLLBACK (connection taken from the pool)
2. do something with the database connection (application code)
3. ROLLBACK (application code)
4. ROLLBACK (connection closed)

(instead of a single ROLLBACK).

This behaviour applies an additional pressure to the redo and undo
subsystems (particularly, in /Oracle/ case, but the problem holds for
any database accessed via /commons-dbcp/, e. g.: I observed the same
issue with /MySQL/).

*Question 1*: how can I get rid of these extra ROLLBACK's while still
having my data source configured with defaultAutoCommit=false (i. e.
without the need to manually call setAutoCommit(false) for each
connection taken from the data source)?

*Question 2* (I'm aware I may be barking up the wrong tree, but still):
alternatively, how can I mitigate the issue from /Oracle/ side (i. e.
without changing my code or replacing libraries)?

Regards,
Andrey.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@commons.apache.org
For additional commands, e-mail: user-***@commons.apache.org