Обсуждение: "postgresql-9.0-801.jdbc4.jar" always 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();


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 this a bug for "postgresql-9.0-801.jdbc4.jar"?

Thanks a lot!
Emi

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

We'll presume that you intend (intentionally or otherwise) for auto-commit
to be on since you do not reference any actual JDBC method calls here...

> While for "8.4-702 JDBC 4", the same codes, no error at all.
>
> Is this a bug for "postgresql-9.0-801.jdbc4.jar"?
>
> Thanks a lot!
> Emi
>

Arguably 8.4-702 was the bugged version and 9.0-801  corrects the behavior -
or rather enforces the fact you should not be in auto-commit mode AND
committing manually.  Since this is a major version change such a behavioral
change is to be expected.  It should also at least be documented - but
whether it is or not I do not know.

I would recommend disabling auto-commit and leaving your commit() calls in
place.  You are generally much better off dealing with transaction logic
explicitly instead of relying upon the driver to do it for you; though there
are always exceptions but you should code is so that you can request an
auto-commit session when you know you need one.

David J.

David,

>> SqlSession sql_session = sqlSessionFactory.openSession(false);
>> ....
>> sql_session.commit();
>>
>
> We'll presume that you intend (intentionally or otherwise) for auto-commit
> to be on since you do not reference any actual JDBC method calls here...

I'd like always "autocommit = false"

jdbc8.4 does keep autocommit= false;

While 9.0 set default autocommit = true -> this is NOT what I want.

Setup is in spring configuration file:
=========================================
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>

mybatis does not have a parameter needed for autoCommit=false. The
default is false for JDBC8.4 driver.


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

> Arguably 8.4-702 was the bugged version and 9.0-801 corrects the behavior -
> or rather enforces the fact you should not be in auto-commit mode AND
> committing manually.

For Spring3.0.5 + mybatis3 + jdbc9, how do you setup autoCommit = false?

The default for 8.4 is false, while jdbc9 always get "Cannot commit when
autoCommit is enabled" Exception". Where should I specify autoCommit =
false for jdbc9 in spring frame work?


> I would recommend disabling auto-commit and leaving your commit() calls in
> place.

This is exactly what I had and I need for jdbc9 as well. But jdbc9
returns autoCommit = true ?




> explicitly instead of relying upon the driver to do it for you; though there
> are always exceptions but you should code is so that you can request an
> auto-commit session when you know you need one.

Exactly.

I need to know in spring3.0.5 + mybatis + jdbc9 where to setup
autocommit= false.

For spring3.0.5 + mybatis + jdbc8, the default is autocommit = false.

Thank you,
Emi

> -----Original Message-----
> From: Emi Lu [mailto:emilu@encs.concordia.ca]
> Sent: Tuesday, May 31, 2011 2:06 PM
> To: David Johnston
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] "postgresql-9.0-801.jdbc4.jar" always cause
> "org.postgresql.util.PSQLException: Cannot commit when autoCommit is
> enabled" Exception
>
> Exactly.
>
> I need to know in spring3.0.5 + mybatis + jdbc9 where to setup
> autocommit= false.
>
> For spring3.0.5 + mybatis + jdbc8, the default is autocommit = false.
>
> Thank you,
> Emi

[Note: treat the following as pseudo code, i.e., the syntax may be
incorrect]

Don't know about the framework settings but since you seem to be wrapping
your "getConnection()" and similar calls can you just modify your code to
call "connection.setAutoCommit(false)" prior to returning the connection
instance to the caller?

You may want to ask (or search) on the appropriate framework list/faq since
I would guess this question has been previously asked.  The issue is not
PostgreSQL specific so a PostgreSQL oriented list, even the JDBC one, may
not yield someone who uses the framework in question.

Even if you can setup the framework to default auto-commit if you are not
already wrapping your "getConnection()" calls you'd be wise to consider
doing so.  If you want to toggle auto-commit on a per-request basis you
would want to centralize that particular logic.

I am not sure but you could also turn off auto-commit just before you
"execute()" the statement if you are centralizing that part instead.

I am not familiar with any of the persistence frameworks but your question
HAS to have been asked and answered previously; it is just a matter of
either finding the answer via search or waiting for someone more
knowledgeable to respond.  Otherwise you can at least ponder the
alternatives (getConnection() or execute() centralization) which may be
useful even if you find the more direct solution to this particular problem.

David J.