[jira] [Issue Comment Deleted] (OFBIZ-7346) connection pooling not working

["Jacques Le Roux (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OFBIZ-7346?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jacques Le Roux updated OFBIZ-7346:
-----------------------------------
    Comment: was deleted

(was: Thanks Gareth,

I can confirm I see this behaviour (connection pooling not working, ie connections not being reused and dissapearing) while using the 
bq. Select * from pg_stat_activity  where usename='ofbiz'
request in Postgres.

With the patch applied I was able to generate 10 used pooled connections using OWAS Zap running against ecommerce and catalog, and simultaneously JMeter on catalog. After shutting them, the connections stay visible and IDLE, so ready to be reused. So the patch clearly fixes this issue which is at least reproductible in Postgres (version 9.1 with last driver loaded with Ant+Ivy) and easily visible in pdAdmin III (I use 1.18.1)

So far I see no reasons to not commit, but as Gareth is cautious I'll be so and check that no issues could creep in.

Ah, I also noticed that contrary what I saw before applying the patch, when I run the
bq. Select * from pg_stat_activity  where usename='ofbiz'
query, before running any request against OFBiz, I see 2 connections ready to use. Before applying the patch (and after OFBIZ-7344 beind fixed) nothing appears and this does not respect the *pool-minsize="2"* parameter of the *<datasource name="localpostgres"...* in entityengine.xml. So this patch also fixes this "minor" issue.

BTW I checked this issue did not exist in R13.07
)

> 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: Minor
>         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)