PGXAConnection and autocommit problem - Mailing list pgsql-jdbc

Hi,

I hope I’m in the right mailing list.

I’m currently facing a problem with Postgresql XA connections. I’m trying to have my transactions working but no rollback seems to take effect. After searching, I think the problem is about the autoCommit mode which is set to true even during the transaction.

Here are the details:

Database version: postgresql 8.3.5

Driver version: postgresql-8.3-603.jdbc4 (tried also with postgresql-9.0-801.jdbc4 and same results)

Application server: Tomcat 6, with Atomikos 3.6.6 for the xa transaction manager

Server.xml:

<Resource

        name="jdbc/actualite_dev"

        auth="Container"

        type="com.atomikos.jdbc.AtomikosDataSourceBean"

        factory="com.atomikos.tomcat.BeanFactory"

        uniqueResourceName="jdbc/actualite_dev"

        xaDataSourceClassName="org.postgresql.xa.PGXADataSource"

        xaProperties.databaseName="actualite_dev"

        xaProperties.serverName="10.85……"

        xaProperties.portNumber="5432"

        xaProperties.user="*****"

        xaProperties.password="*****"

    />

Context.xml:

<Transaction factory="com.atomikos.icatch.jta.UserTransactionFactory" />

<ResourceLink name="jdbc/actualite_dev" global="jdbc/actualite_dev" type="com.atomikos.jdbc.AtomikosDataSourceBean"/>

I’m testing from a webapp in which the spring configuration uses JtaTransactionManager pointing to Atomikos. The transactional mode is set to my business method by spring annotations. This part seems to be ok. With breakpoints and logs, I can see the transaction is created; when I throw an exception, it does decide to rollback and seems to initiate it. Apparently no rollback error but nothing happens then in the database.

The problem is apparently before that: When I check the database before throwing the exception, the update has already been commited. When I check the value of the autoCommit during the transaction, it seems to be true and thus the root cause of the problem.

I saw this old post about a similar problem: http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00043.php

When comparing the patch to the most recent driver (9-801), I saw the changes were already integrated. But the autoCommit problem is still there for me even with this version.

Have I missed something in the configuration?

Should I explicitly set the autocommit mode to false somewhere? How?

The problem seems to be on the driver’s side, but maybe it’s also due to Atomikos. I’m a bit confused now.

I also read posts and docs about the autocommit set to true in Postgresql since any “begin” instruction has not been sent. Is there something about that?

I would really appreciate some help. I can provide more details if needed.

Thank you.

Marion.

Logs in debug (the transaction is set around the ActualiteBusinessImpl.modifierActualiteDto method, containing calls to ActualitoDaoImpl):

2011-02-09 14:24:57,498 DEBUG [ActualiteDaoImpl.java:102] : Entree dans la methode

2011-02-09 14:24:57,498 DEBUG [JakartaCommonsLoggingImpl.java:27] : {conn-100891} Connection

2011-02-09 14:24:57,498 DEBUG [JakartaCommonsLoggingImpl.java:27] : {conn-100891} Preparing Statement:    UPDATE ACTUALITE SET TITRE = ?,    CONTENU = ?,    IMPORTANTE = ?,    ETAT_ESPACE_DISCUSSION_ID = ?,    MODERATION_ID = ?,        AUTEUR_MAJ = ?,    ESPACE_DISCUSSION = ?,       DATE_PUBLICATION = ?,       DATE_EXPIRATION = ?,       DATE_MAJ = ?,       IMAGE_POSITION = ?,       IMAGE_DIMENSION = ?,       TYPE_ACTUALITE_ID = ?         WHERE ACTUALITE_ID = ?; 

2011-02-09 14:24:57,498 DEBUG [DataSourceUtils.java:112] : Fetching JDBC Connection from DataSource

2011-02-09 14:24:57,513 DEBUG [DataSourceUtils.java:116] : Registering transaction synchronization for JDBC Connection

2011-02-09 14:24:57,513 DEBUG [TransactionSynchronizationManager.java:168] : Bound value [org.springframework.jdbc.datasource.ConnectionHolder@5a8d05] for key [AtomikosDataSoureBean 'jdbc/actualite_dev'] to thread [http-8080-1]

2011-02-09 14:24:57,513 DEBUG [TransactionSynchronizationManager.java:140] : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@5a8d05] for key [AtomikosDataSoureBean 'jdbc/actualite_dev'] bound to thread [http-8080-1]

2011-02-09 14:24:57,513 DEBUG [JakartaCommonsLoggingImpl.java:27] : {pstm-100892} Executing Statement:    UPDATE ACTUALITE SET TITRE = ?,    CONTENU = ?,    IMPORTANTE = ?,    ETAT_ESPACE_DISCUSSION_ID = ?,    MODERATION_ID = ?,        AUTEUR_MAJ = ?,    ESPACE_DISCUSSION = ?,       DATE_PUBLICATION = ?,       DATE_EXPIRATION = ?,       DATE_MAJ = ?,       IMAGE_POSITION = ?,       IMAGE_DIMENSION = ?,       TYPE_ACTUALITE_ID = ?         WHERE ACTUALITE_ID = ?; 

2011-02-09 14:24:57,513 DEBUG [JakartaCommonsLoggingImpl.java:27] : {pstm-100892} Parameters: [transactions, <p>test transactions mmau jcomprends plus</p>, true, null, null, 110000000000007027, false, 2011-01-17 00:00:00.0, 2011-07-05 00:00:00.0, 2011-02-09 14:24:56.763, 1, 100, 0, 182]

2011-02-09 14:24:57,513 DEBUG [JakartaCommonsLoggingImpl.java:27] : {pstm-100892} Types: [java.lang.String, java.lang.String, java.lang.Boolean, null, null, java.lang.Long, java.lang.Boolean, java.sql.Timestamp, java.sql.Timestamp, java.sql.Timestamp, java.lang.Long, java.lang.Long, java.lang.Long, java.lang.Long]

2011-02-09 14:24:57,529 DEBUG [TransactionSynchronizationManager.java:140] : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@5a8d05] for key [AtomikosDataSoureBean 'jdbc/actualite_dev'] bound to thread [http-8080-1]

(the following exception is on purpose to test the rollback)

2011-02-09 14:25:07,967 ERROR [ActualitesBusinessImpl.java:161] :

java.lang.NullPointerException

                at org.lilie.services.actualite.business.impl.ActualitesBusinessImpl.modifierActualiteDto(ActualitesBusinessImpl.java:145)

                at org.lilie.services.actualite.business.impl.ActualitesBusinessImpl$$FastClassByCGLIB$$be9db12c.invoke(<generated>)

                …

                at java.lang.Thread.run(Thread.java:619)

2011-02-09 14:25:09,248 DEBUG [TransactionAspectSupport.java:327] : Completing transaction for [org.lilie.services.actualite.business.impl.ActualitesBusinessImpl.modifierActualiteDto] after exception: org.lilie.socle.core.business.exception.ServiceTechniqueException: java.lang.NullPointerException

2011-02-09 14:25:09,248 DEBUG [RuleBasedTransactionAttribute.java:130] : Applying rules to determine whether transaction should rollback on org.lilie.socle.core.business.exception.ServiceTechniqueException: java.lang.NullPointerException

2011-02-09 14:25:09,248 DEBUG [RuleBasedTransactionAttribute.java:148] : Winning rollback rule is: RollbackRuleAttribute with pattern [java.lang.Exception]

2011-02-09 14:25:09,263 DEBUG [AbstractPlatformTransactionManager.java:846] : Triggering beforeCompletion synchronization

2011-02-09 14:25:09,263 DEBUG [TransactionSynchronizationManager.java:193] : Removed value [org.springframework.jdbc.datasource.ConnectionHolder@5a8d05] for key [AtomikosDataSoureBean 'jdbc/actualite_dev'] from thread [http-8080-1]

2011-02-09 14:25:09,263 DEBUG [DataSourceUtils.java:312] : Returning JDBC Connection to DataSource

2011-02-09 14:25:09,263 DEBUG [AbstractPlatformTransactionManager.java:751] : Initiating transaction rollback

2011-02-09 14:25:09,263 DEBUG [Slf4jConsole.java:88] : getCompositeTransaction()  returning instance with id 10.84.213.150.tm0000700057

2011-02-09 14:25:09,263 DEBUG [Slf4jConsole.java:88] : getCompositeTransaction()  returning instance with id 10.84.213.150.tm0000700057

2011-02-09 14:25:09,279 DEBUG [Slf4jConsole.java:88] : getCompositeTransaction()  returning instance with id 10.84.213.150.tm0000700057

2011-02-09 14:25:09,279 DEBUG [Slf4jConsole.java:88] : getCompositeTransaction()  returning instance with id 10.84.213.150.tm0000700057

2011-02-09 14:25:09,310 DEBUG [Slf4jConsole.java:88] : Coordinator 10.84.213.150.tm0000700057 entering state: ABORTING

2011-02-09 14:25:09,310 DEBUG [Slf4jConsole.java:88] : Coordinator 10.84.213.150.tm0000700057 entering state: TERMINATED

2011-02-09 14:25:09,326 DEBUG [Slf4jConsole.java:88] : Coordinator 10.84.213.150.tm0000700057 : stopping timer...

2011-02-09 14:25:09,326 DEBUG [Slf4jConsole.java:88] : Coordinator 10.84.213.150.tm0000700057 : disposing statehandler TERMINATED...

2011-02-09 14:25:09,326 DEBUG [Slf4jConsole.java:88] : Coordinator 10.84.213.150.tm0000700057 : disposed.

2011-02-09 14:25:09,326 INFO  [Slf4jConsole.java:85] : rollback() done of transaction 10.84.213.150.tm0000700057

2011-02-09 14:25:09,326 DEBUG [AbstractPlatformTransactionManager.java:875] : Triggering afterCompletion synchronization

2011-02-09 14:25:09,326 DEBUG [TransactionSynchronizationManager.java:276] : Clearing transaction synchronization