commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ben short" <jamin.sh...@gmail.com>
Subject Re: [DBCP] BasicDataSource, testOnBorrow and PostgreSQL
Date Fri, 19 Sep 2008 21:37:40 GMT
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 <wclark@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 <wclark@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&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: 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


Mime
View raw message