[DBCP] BasicDataSource, testOnBorrow and PostgreSQL

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

[DBCP] BasicDataSource, testOnBorrow and PostgreSQL

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

Reply | Threaded
Open this post in threaded view
|

RE: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL

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


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL

ben short-2
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 <[hidden email]> 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:[hidden email]]
> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

RE: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL

wesclark
Then you need to commit before changing transaction isolation level.

-----Original Message-----
From: ben short [mailto:[hidden email]]
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 <[hidden email]>
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:[hidden email]]
> 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;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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL

James Carman
In reply to this post by ben short-2
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 <[hidden email]> 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 <[hidden email]> 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:[hidden email]]
>> 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: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL

ben short-2
In reply to this post by wesclark
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 <[hidden email]> wrote:

> Then you need to commit before changing transaction isolation level.
>
> -----Original Message-----
> From: ben short [mailto:[hidden email]]
> 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 <[hidden email]>
> 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:[hidden email]]
>> 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;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: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]