[jira] [Commented] (OFBIZ-7346) connection pooling not working

[Nicolas Malin (Jira)]

    [ https://issues.apache.org/jira/browse/OFBIZ-7346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15335851#comment-15335851 ]

Jacques Le Roux commented on OFBIZ-7346:
----------------------------------------

Thanks for this complement Gareth,

* setRollbackOnReturn, so this explains why this issue doest not exist with Derby, it's a matter of driver (DBMS actually) see Derby vs Postgres
** https://db.apache.org/derby/docs/10.9/devguide/cdevconcepts29416.html
** https://www.postgresql.org/docs/9.1/static/ecpg-sql-set-autocommit.html
Another solution would be to force Postgres to autocommit but I'd not do that, better not change this DBMS dependent behaviour.
* setEnableAutoCommitOnReturn you said
bq. I observed autoCommit was being set to true at transaction commit (can't remember source file/line)
I see connection.setAutoCommit(true) only in 1 place: DatabaseUtil.getConnection(). But it' used quite a lot, so yes better be safe than sorry, since setEnableAutoCommitOnReturn does not impair anything. BTW (minor) I found connection.setAutoCommit(false) in 2 places
** SQLProcessor (we can forget that, specific to manual interactions with the DB)
** CursorStatement.newCursorStatement() (through private constructor), eventually called by DBCPConnectionFactory.getConnection() when checking cache

> connection pooling not working
> ------------------------------
>
>                 Key: OFBIZ-7346
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-7346
>             Project: OFBiz
>          Issue Type: Bug
>          Components: ALL COMPONENTS
>    Affects Versions: Trunk
>            Reporter: Gareth Carter
>            Assignee: Jacques Le Roux
>            Priority: Critical
>         Attachments: DBCPConnectionFactory.patch
>
>
> Connection pooling does not seem to work. Connections are created fine but as the close() method is called (outside of transaction) or the transaction is committed the connections are closed to the db server and not returned to the pool.
> I believe the issue is with org.apache.commons.dbcp2.PoolableConnectionFactory passivateObject method. This will call rollback() when rollbackOnReturn is set to true even if the transaction is committed. This is because any connection wrappers extending org.apache.commons.dbcp2.DelegatingConnection cache autoCommit status. At some point, this cached autoCommit is different from the underlying connection autoCommit. The rollback() method will throw an exception and the connection is destroyed rather than put back to the pool
> Environment this has been tested on:
> ofbiz: rev 1725574 and latest trunk (as of 2016-06-14)
> db: postgresql 9.1
> jdbc driver: postgresql-9.3-1101.jdbc4
> os: linux and windows
> I have asked dev ml for others to check this with other dbs. Jacques has test with postgres but could not see this behaviour

--
This message was sent by Atlassian JIRA
(v6.3.4#6332)