Thread: "postgresql-9.0-801.jdbc4.jar" Causing "Error committing transaction. Cause: org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled." Exception

Hello list,

. Postgresql8.3

. mybatis-3.0.5-SNAPSHOT.jar
. mybatis-spring-1.0.1-SNAPSHOT.jar
. spring3.0.5

. postgresql-9.0-801.jdbc4.jar


SqlSession sql_session = sqlSessionFactory.openSession(false);
....
sql_session.commit();



applicationContext-mybatis.xml

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       <property name="driverClassName" value="${driverClassName}" />
       <property name="url"             value="${url}"      />
       <property name="username"        value="${username}" />
       <property name="password"        value="${password}" />
</bean>



When update/insert/delete is called, always got:
===================================================
### Error committing transaction.  Cause:
org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.



While for "8.4-702 JDBC 4", the same codes, no error at all.

Is there anything need to be configured if using
"postgresql-9.0-801.jdbc4.jar"?

Thanks a lot!
Emi

On 06/01/2011 01:07 AM, Emi Lu wrote:

> When update/insert/delete is called, always got:
> ===================================================
> ### Error committing transaction. Cause:
> org.postgresql.util.PSQLException: Cannot commit when autoCommit is
> enabled.

Sounds like MyBatis expects autocommit to be disabled because it does
its own explicit BEGIN/COMMIT, but you've got it turned on. The newer
JDBC driver detects this and complains about it, where the older one
presumably ignored a COMMIT issued while in autocommit mode.

Add "autoCommit=false" to your connection parameters.

"autoCommit" doesn't appear to be documented in
http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
, which is surprising.

--
Craig Ringer

On 1 June 2011 12:18, Craig Ringer <craig@postnewspapers.com.au> wrote:

> "autoCommit" doesn't appear to be documented in
> http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
> , which is surprising.

It's a datasource parameter, not a connection parameter.

Possibly mybatis is trying to handle the old datasource default that
had autocommit=false for connections obtained from a datasource.
That default was changed quite a long time ago.

Oliver

On 05/31/2011 08:18 PM, Craig Ringer wrote:
> On 06/01/2011 01:07 AM, Emi Lu wrote:
>
>> When update/insert/delete is called, always got:
>> ===================================================
>> ### Error committing transaction. Cause:
>> org.postgresql.util.PSQLException: Cannot commit when autoCommit is
>> enabled.
>
> Sounds like MyBatis expects autocommit to be disabled because it does
> its own explicit BEGIN/COMMIT, but you've got it turned on. The newer
> JDBC driver detects this and complains about it, where the older one
> presumably ignored a COMMIT issued while in autocommit mode.

Thank you for clearing it, and I will use jdbc9.


> Add "autoCommit=false" to your connection parameters.
>
> "autoCommit" doesn't appear to be documented in
> http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
> , which is surprising.

As Oliver said, it's a datasource parameter. The hidden part that is
nowhere to find is "how to configure SpringFramework + mybatis + JDBC9"
to autoCommit = false.

It's just a simple set for me, but it seems there is no easy and
explicit way for user to specify it in the spring_config.xml file.

As an alternative for now, I did:

SqlSession sql_session = sqlSessionFactory.openSession(false); //in
mybatis doc, this line should auto Disable commit, but not indeed!

Connection conn        = sql_session.getConnection();   //newly added
try
{
    conn.setAutoCommit(false); //newly added
    ....
    conn.commit();  //newly added
    sql_session.commit();

}catch(...){}
finally
{
    try{
      conn.close(); //newly added
    } ...
    try
    {
       sql_session.close();
    }
}


The above codes work fine. Before I can find a simple way to set
autoCommit=false in spring_config.xml, I will do this way.

Thanks  alot for explanation!

Emi