commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Wes Clark" <wcl...@guidewire.com>
Subject RE: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL
Date Fri, 19 Sep 2008 21:00:08 GMT
You might check the state of autocommit on the connections you are
getting from the pool.  The Java standard is that autocommit should be
on, but maybe PostgreSQL behaves differently.  I'm not familiar with it.
If autocommit is off, the "select 1" will implicitly start a
transaction.  Changing the isolation level also requires a commit if not
in autocommit mode. 

-----Original Message-----
From: ben short [mailto:jamin.short@gmail.com] 
Sent: Friday, September 19, 2008 1:36 PM
To: Jakarta Commons Users List
Subject: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL

Hi,

I'm trying to use the BasicDataSource to pool PostgreSQL connections.
I'm also using Spring and Hibernate.

My BasicDataSource configuration is as follows....

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="org.postgresql.Driver"/>
        <property name="url"
value="jdbc:postgresql://127.0.0.1/tyrellcheck?user=sysadmin&amp;passwor
d=password"/>
        <property name="validationQuery" value="SELECT 1"/>
        <property name="initialSize" value="5"/>
        <property name="maxActive" value="25"/>
        <property name="minIdle" value="5"/>
        <property name="maxIdle" value="5"/>
        <property name="defaultAutoCommit" value="false"/>
        <property name="testOnBorrow" value="true"/>
    </bean>

The problem I have is that springs transaction manager is trying to set
the transaction isolation based on transactional annotations in my code.
But I get the following exception thrown.

org.postgresql.util.PSQLException: Cannot change transaction isolation
level in the middle of a transaction

I have debugged the code and see that when the validateConnection method
gets called on the PoolableConnectionFactory it executes the validation
query which starts a transaction, so later on when the springs
transaction manager tried to set the transaction isolation the exception
is thrown.

While debugging I called commit on the connection after the validation
query had been executed and all worked OK.

I believe this to be a bug, should I log it?

Regards

Ben Short
blog.benshort.co.uk

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


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


Mime
View raw message