You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by ben short <ja...@gmail.com> on 2008/09/19 22:36:26 UTC
[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&password=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
Re: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL
Posted by James Carman <ja...@carmanconsulting.com>.
Spring will take care of setting the autocommit mode to off, I
believe. So, I'd take that setting out and see what happens.
On Fri, Sep 19, 2008 at 5:08 PM, ben short <ja...@gmail.com> wrote:
> My configuration turns auto commit off which I need as I perform more
> than one operation in my transactions.
>
> On Fri, Sep 19, 2008 at 10:00 PM, Wes Clark <wc...@guidewire.com> wrote:
>> 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&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
>>
>>
>
> ---------------------------------------------------------------------
> 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
Re: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL
Posted by ben short <ja...@gmail.com>.
Ah but I'm using springs transaction manager that manages getting,
commiting etc of transactions.
It would seem to me that the validateConnection could be improved as
shown below...
public void validateConnection(Connection conn) throws SQLException {
String query = _validationQuery;
if(conn.isClosed()) {
throw new SQLException("validateConnection: connection closed");
}
if(null != query) {
Statement stmt = null;
ResultSet rset = null;
try {
stmt = conn.createStatement();
rset = stmt.executeQuery(query);
if(!rset.next()) {
throw new SQLException("validationQuery didn't
return a row");
}
} finally {
if (rset != null) {
try {
rset.close();
} catch(Exception t) {
// ignored
}
}
if (stmt != null) {
try {
stmt.close();
} catch(Exception t) {
// ignored
}
}
// new code
if ( conn != null && !conn.getAutoCommit() ) {
try{
conn.commit();
} catch(Exception t) {
// ignored
}
}
// end of new code
}
}
}
This way would would not have to code the application to commit the
transaction before use.
Regards
Ben Short
On Fri, Sep 19, 2008 at 10:28 PM, Wes Clark <wc...@guidewire.com> wrote:
> Then you need to commit before changing transaction isolation level.
>
> -----Original Message-----
> From: ben short [mailto:jamin.short@gmail.com]
> Sent: Friday, September 19, 2008 2:09 PM
> To: Commons Users List
> Subject: Re: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL
>
> My configuration turns auto commit off which I need as I perform more
> than one operation in my transactions.
>
> On Fri, Sep 19, 2008 at 10:00 PM, Wes Clark <wc...@guidewire.com>
> wrote:
>> 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&passw
>> or
>> 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
>>
>>
>
> ---------------------------------------------------------------------
> 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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
RE: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL
Posted by Wes Clark <wc...@guidewire.com>.
Then you need to commit before changing transaction isolation level.
-----Original Message-----
From: ben short [mailto:jamin.short@gmail.com]
Sent: Friday, September 19, 2008 2:09 PM
To: Commons Users List
Subject: Re: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL
My configuration turns auto commit off which I need as I perform more
than one operation in my transactions.
On Fri, Sep 19, 2008 at 10:00 PM, Wes Clark <wc...@guidewire.com>
wrote:
> 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&passw
> or
> 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
>
>
---------------------------------------------------------------------
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
Re: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL
Posted by ben short <ja...@gmail.com>.
My configuration turns auto commit off which I need as I perform more
than one operation in my transactions.
On Fri, Sep 19, 2008 at 10:00 PM, Wes Clark <wc...@guidewire.com> wrote:
> 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&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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
RE: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL
Posted by Wes Clark <wc...@guidewire.com>.
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&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