Re: PGXAConnection and autocommit problem - Mailing list pgsql-jdbc

Driver should prevent set auto commit, or change it value in any case.
 I thought I saw exception in code preventing this.
 On Wed, 9 Feb 2011 14:28:44 +0000, Maurin, Marion wrote:
> 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:
>
> __
>
> _ _
>
> Context.xml:
>
> __
>
> __
>
> _ _
>
> 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 [1]
>
> 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, test transactions mmau
> jcomprends plus, 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()_
>
>
> _ …_
>
> _ 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_
>
>  Think green - keep it on the screen. This e-mail and any attachment
> is for authorised use by the intended recipient(s) only. It may
> contain proprietary material, confidential information and/or be
> subject to legal privilege. It should not be copied, disclosed to,
> retained or used by, any other party. If you are not an intended
> recipient then please promptly delete this e-mail and any attachment
> and all copies and inform the sender. Thank you.
>
> Links:
> ------
> [1] http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00043.php