[dbcp] Unnecessary ROLLBACK's with BasicDataSource

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[dbcp] Unnecessary ROLLBACK's with BasicDataSource

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

The corresponding stack traces are (/Oracle Thin/'s T4CConnection example):

> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [dbcp] Unnecessary ROLLBACK's with BasicDataSource

Bernd Eckenfels
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 <[hidden email]>
Gesendet: Dienstag, August 21, 2018 5:01 PM
An: [hidden email]
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.

The corresponding stack traces are (/Oracle Thin/'s T4CConnection example):

> 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: [hidden email]
For additional commands, e-mail: [hidden email]