Quantcast

PGXAConnection and autocommit problem

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

PGXAConnection and autocommit problem

Maurin, Marion

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

 


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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PGXAConnection and autocommit problem

Radosław Smogura-2
 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


--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE : [JDBC] PGXAConnection and autocommit problem

Maurin, Marion
Yes, I think I saw that too, that the autocommit shouldn't be set from outside. But shouldn't the driver itself set it to false at the beginning of the transaction?
________________________________________
De : [hidden email] [[hidden email]] de la part de rsmogura [[hidden email]]
Date d'envoi : mercredi 9 février 2011 21:13
À : [hidden email]
Objet : Re: [JDBC] PGXAConnection and autocommit problem

 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


--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc


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.



--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RE : [JDBC] PGXAConnection and autocommit problem

Heikki Linnakangas-3
On 10.02.2011 09:50, Maurin, Marion wrote:

> Yes, I think I saw that too, that the autocommit shouldn't be set from outside. But shouldn't the driver itself set it to false at the beginning of the transaction?
> ________________________________________
> De : [hidden email] [[hidden email]] de la part de rsmogura [[hidden email]]
> Date d'envoi : mercredi 9 février 2011 21:13
> À : [hidden email]
> Objet : Re: [JDBC] PGXAConnection and autocommit problem
>
>   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.

It's quite possible that there's a bug somewhere in the XA driver -
we've had several bugs related to autocommit and XA in the past.

And I think this one is still pending:
http://archives.postgresql.org/message-id/4D01DFA8.3060207@....
Could it be related?

Can you create a small self-contained test program to reproduce the
issue, and post it to the list?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PGXAConnection and autocommit problem

Achilleas Mantzios
Στις Thursday 10 February 2011 10:42:44 ο/η Heikki Linnakangas έγραψε:

> On 10.02.2011 09:50, Maurin, Marion wrote:
> > Yes, I think I saw that too, that the autocommit shouldn't be set from outside. But shouldn't the driver itself set it to false at the beginning of the transaction?
> > ________________________________________
> > De : [hidden email] [[hidden email]] de la part de rsmogura [[hidden email]]
> > Date d'envoi : mercredi 9 février 2011 21:13
> > À : [hidden email]
> > Objet : Re: [JDBC] PGXAConnection and autocommit problem
> >
> >   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.
>
> It's quite possible that there's a bug somewhere in the XA driver -
> we've had several bugs related to autocommit and XA in the past.
>

I remember my similar case one year ago :
http://archives.postgresql.org/message-id/201003021629.07368.achill%40matrix.gatewaynet.com

Anyways Heikki helped a lot on this one i remember, and also wrote a patch. Thanx Heikki!

> And I think this one is still pending:
> http://archives.postgresql.org/message-id/4D01DFA8.3060207@....
> Could it be related?
>
> Can you create a small self-contained test program to reproduce the
> issue, and post it to the list?
>
> --
>    Heikki Linnakangas
>    EnterpriseDB   http://www.enterprisedb.com
>



--
Achilleas Mantzios

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PGXAConnection and autocommit problem

Maurin, Marion
Thanks for those two inputs. I couldn't save time today to check but it seems interesting. I'll test to see if it's related.

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de Achilleas Mantzios
Envoyé : jeudi 10 février 2011 10:45
À : [hidden email]
Objet : Re: [JDBC] PGXAConnection and autocommit problem

Στις Thursday 10 February 2011 10:42:44 ο/η Heikki Linnakangas έγραψε:

> On 10.02.2011 09:50, Maurin, Marion wrote:
> > Yes, I think I saw that too, that the autocommit shouldn't be set from outside. But shouldn't the driver itself set it to false at the beginning of the transaction?
> > ________________________________________
> > De : [hidden email] [[hidden email]] de la part de rsmogura [[hidden email]]
> > Date d'envoi : mercredi 9 février 2011 21:13
> > À : [hidden email]
> > Objet : Re: [JDBC] PGXAConnection and autocommit problem
> >
> >   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.
>
> It's quite possible that there's a bug somewhere in the XA driver -
> we've had several bugs related to autocommit and XA in the past.
>

I remember my similar case one year ago :
http://archives.postgresql.org/message-id/201003021629.07368.achill%40matrix.gatewaynet.com

Anyways Heikki helped a lot on this one i remember, and also wrote a patch. Thanx Heikki!

> And I think this one is still pending:
> http://archives.postgresql.org/message-id/4D01DFA8.3060207@....
> Could it be related?
>
> Can you create a small self-contained test program to reproduce the
> issue, and post it to the list?
>
> --
>    Heikki Linnakangas
>    EnterpriseDB   http://www.enterprisedb.com
>



--
Achilleas Mantzios

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc


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.


--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PGXAConnection and autocommit problem

Maurin, Marion
In reply to this post by Achilleas Mantzios
Hi,

I checked the two links you proposed.
Concerning this one: http://archives.postgresql.org/message-id/201003021629.07368.achill%40matrix.gatewaynet.com
The problem of autocommit described in the conclusion seemed to be caused by the reuse of a connexion by different transactions. So I tried to run my tests while assuring that the connection used was really a new one (checked the ids in debug mode), and nothing changed for me. Thus I don't think it is the same problem I'm facing. I also followed the advice of activating the debug mode for postgresql logs, and I got that:
-----
18:15:22.623 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@d458ab, maxRows=0, fetchSize=0, flags=17
18:15:22.623 (1)  FE=> Parse(stmt=null,query="   UPDATE ACTUALITE SET TITRE = $1,    CONTENU = $2,    IMPORTANTE = $3,    ETAT_ESPACE_DISCUSSION_ID = $4,    MODERATION_ID = $5,        AUTEUR_MAJ = $6,    ESPACE_DISCUSSION = $7,       DATE_PUBLICATION = $8,       DATE_EXPIRATION = $9,       DATE_MAJ = $10,       IMAGE_POSITION = $11,       IMAGE_DIMENSION = $12,       TYPE_ACTUALITE_ID = $13         WHERE ACTUALITE_ID = $14",oids={1043,1043,16,0,0,20,16,0,0,0,20,20,20,20})
18:15:22.623 (1)  FE=> Bind(stmt=null,portal=null,$1=<'transactions'>,$2=<'<p>test transactions mmau prepare modif</p>'>,$3=<'1'>,$4=<NULL>,$5=<NULL>,$6=<'110000000000007027'>,$7=<'0'>,$8=<'2011-01-17 00:00:00.000000 +01:00:00'>,$9=<'2011-07-05 00:00:00.000000 +02:00:00'>,$10=<'2011-02-15 18:15:21.733000 +01:00:00'>,$11=<'1'>,$12=<'100'>,$13=<'0'>,$14=<'182'>)
18:15:22.623 (1)  FE=> Describe(portal=null)
18:15:22.623 (1)  FE=> Execute(portal=null,limit=0)
18:15:22.623 (1)  FE=> Sync
18:15:22.639 (1)  <=BE ParseComplete [null]
18:15:22.639 (1)  <=BE BindComplete [null]
18:15:22.639 (1)  <=BE NoData
18:15:22.639 (1)  <=BE CommandStatus(UPDATE 1)
18:15:22.639 (1)  <=BE ReadyForQuery(I)
-----
As we can see, there is no BEGIN order before the update.

For the second link: http://archives.postgresql.org/message-id/4D01DFA8.3060207@...
I applied the patch you proposed for the prepare() method. Nothing changed either. The update was still commited and the postgresql logs did not change. I tried to check what it was actually doing in the prepare() method, to see if everything was going correctly, but I couldn't succeed in seeing either the breakpoints or the debug logs I added. I don't see the default PGXAConnection class logs either. Could it be possible that it doesn't even go in the prepare() method?

Thanks for any help.
Marion

-----Message d'origine-----
De : Maurin, Marion
Envoyé : jeudi 10 février 2011 19:45
À : 'Achilleas Mantzios'; [hidden email]
Objet : RE: [JDBC] PGXAConnection and autocommit problem

Thanks for those two inputs. I couldn't save time today to check but it seems interesting. I'll test to see if it's related.

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de Achilleas Mantzios
Envoyé : jeudi 10 février 2011 10:45
À : [hidden email]
Objet : Re: [JDBC] PGXAConnection and autocommit problem

Στις Thursday 10 February 2011 10:42:44 ο/η Heikki Linnakangas έγραψε:

> On 10.02.2011 09:50, Maurin, Marion wrote:
> > Yes, I think I saw that too, that the autocommit shouldn't be set from outside. But shouldn't the driver itself set it to false at the beginning of the transaction?
> > ________________________________________
> > De : [hidden email] [[hidden email]] de la part de rsmogura [[hidden email]]
> > Date d'envoi : mercredi 9 février 2011 21:13
> > À : [hidden email]
> > Objet : Re: [JDBC] PGXAConnection and autocommit problem
> >
> >   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.
>
> It's quite possible that there's a bug somewhere in the XA driver -
> we've had several bugs related to autocommit and XA in the past.
>

I remember my similar case one year ago :
http://archives.postgresql.org/message-id/201003021629.07368.achill%40matrix.gatewaynet.com

Anyways Heikki helped a lot on this one i remember, and also wrote a patch. Thanx Heikki!

> And I think this one is still pending:
> http://archives.postgresql.org/message-id/4D01DFA8.3060207@....
> Could it be related?
>
> Can you create a small self-contained test program to reproduce the
> issue, and post it to the list?
>
> --
>    Heikki Linnakangas
>    EnterpriseDB   http://www.enterprisedb.com
>



--
Achilleas Mantzios

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc


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.


--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PGXAConnection and autocommit problem

Radosław Smogura-2
"Maurin, Marion" <[hidden email]> Thursday 17 February 2011 14:02:28

> Hi,
>
> I checked the two links you proposed.
> Concerning this one:
> http://archives.postgresql.org/message-id/201003021629.07368.achill%40matr
> ix.gatewaynet.com The problem of autocommit described in the conclusion
> seemed to be caused by the reuse of a connexion by different transactions.
> So I tried to run my tests while assuring that the connection used was
> really a new one (checked the ids in debug mode), and nothing changed for
> me. Thus I don't think it is the same problem I'm facing. I also followed
> the advice of activating the debug mode for postgresql logs, and I got
> that: -----
> 18:15:22.623 (1) simple execute,
> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler
> @d458ab, maxRows=0, fetchSize=0, flags=17 18:15:22.623 (1)  FE=>
> Parse(stmt=null,query="   UPDATE ACTUALITE SET TITRE = $1,    CONTENU =
> $2,    IMPORTANTE = $3,    ETAT_ESPACE_DISCUSSION_ID = $4,  
> MODERATION_ID = $5,        AUTEUR_MAJ = $6,    ESPACE_DISCUSSION = $7,    
>   DATE_PUBLICATION = $8,       DATE_EXPIRATION = $9,       DATE_MAJ = $10,
>       IMAGE_POSITION = $11,       IMAGE_DIMENSION = $12,      
> TYPE_ACTUALITE_ID = $13         WHERE ACTUALITE_ID =
> $14",oids={1043,1043,16,0,0,20,16,0,0,0,20,20,20,20}) 18:15:22.623 (1)
> FE=> Bind(stmt=null,portal=null,$1=<'transactions'>,$2=<'<p>test
> transactions mmau prepare
> modif</p>'>,$3=<'1'>,$4=<NULL>,$5=<NULL>,$6=<'110000000000007027'>,$7=<'0'
> >,$8=<'2011-01-17 00:00:00.000000 +01:00:00'>,$9=<'2011-07-05
> 00:00:00.000000 +02:00:00'>,$10=<'2011-02-15 18:15:21.733000
> +01:00:00'>,$11=<'1'>,$12=<'100'>,$13=<'0'>,$14=<'182'>) 18:15:22.623 (1)
> FE=> Describe(portal=null)
> 18:15:22.623 (1)  FE=> Execute(portal=null,limit=0)
> 18:15:22.623 (1)  FE=> Sync
> 18:15:22.639 (1)  <=BE ParseComplete [null]
> 18:15:22.639 (1)  <=BE BindComplete [null]
> 18:15:22.639 (1)  <=BE NoData
> 18:15:22.639 (1)  <=BE CommandStatus(UPDATE 1)
> 18:15:22.639 (1)  <=BE ReadyForQuery(I)
> -----
> As we can see, there is no BEGIN order before the update.
>
> For the second link:
> http://archives.postgresql.org/message-id/4D01DFA8.3060207@... I
> applied the patch you proposed for the prepare() method. Nothing changed
> either. The update was still commited and the postgresql logs did not
> change. I tried to check what it was actually doing in the prepare()
> method, to see if everything was going correctly, but I couldn't succeed
> in seeing either the breakpoints or the debug logs I added. I don't see
> the default PGXAConnection class logs either. Could it be possible that it
> doesn't even go in the prepare() method?
>
> Thanks for any help.
> Marion
>
> -----Message d'origine-----
> De : Maurin, Marion
> Envoyé : jeudi 10 février 2011 19:45
> À : 'Achilleas Mantzios'; [hidden email]
> Objet : RE: [JDBC] PGXAConnection and autocommit problem
>
> Thanks for those two inputs. I couldn't save time today to check but it
> seems interesting. I'll test to see if it's related.
>
> -----Message d'origine-----
> De : [hidden email]
> [mailto:[hidden email]] De la part de Achilleas Mantzios
> Envoyé : jeudi 10 février 2011 10:45
> À : [hidden email]
> Objet : Re: [JDBC] PGXAConnection and autocommit problem
>
> Στις Thursday 10 February 2011 10:42:44 ο/η Heikki Linnakangas έγραψε:
> > On 10.02.2011 09:50, Maurin, Marion wrote:
> > > Yes, I think I saw that too, that the autocommit shouldn't be set from
> > > outside. But shouldn't the driver itself set it to false at the
> > > beginning of the transaction? ________________________________________
> > > De : [hidden email] [[hidden email]]
> > > de la part de rsmogura [[hidden email]] Date d'envoi :
> > > mercredi 9 février 2011 21:13
> > > À : [hidden email]
> > > Objet : Re: [JDBC] PGXAConnection and autocommit problem
> > >
> > >   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.
> >
> > It's quite possible that there's a bug somewhere in the XA driver -
> > we've had several bugs related to autocommit and XA in the past.
>
> I remember my similar case one year ago :
> http://archives.postgresql.org/message-id/201003021629.07368.achill%40matri
> x.gatewaynet.com
>
> Anyways Heikki helped a lot on this one i remember, and also wrote a patch.
> Thanx Heikki!
>
> > And I think this one is still pending:
> > http://archives.postgresql.org/message-id/4D01DFA8.3060207@....
> > Could it be related?
> >
> > Can you create a small self-contained test program to reproduce the
> > issue, and post it to the list?

Looking at code there is no way to set auto commit to true (actually you can
do this using some back door and plain JDBC), but point is that auto commit is
set to false in start(), and changed to true on prepare().

Things that comes to my head are:
1. Transaction Managers call prepare or rollback in meantime of your code,
this can be due to transaction timout - it should close connection too.
2. Like above but from different reasons. In some way connection may be shared
and tried to be used in two transactions.
3. Maybe connection is used without XA, i mean missing call to start, or after
prepare from TM?

I think you should put break point on setAutoCommit in
AbstractJdbc2Connection, or prepare/rollback/commit and check all call stacks
to see what actully calls this.

I saw one time simillar error, but didn't noticed preconditions,

You wrote You use Tomcat + Atomikos, I see Spring there too. Is your bug
reproducible using simpler case only with tomcat / atomikos?

I don't know Spring at all, but I see user transactions, do You call begin or
Spring do it? Do you use multiple (or nested) transactions per call, or many
XA resources?

Regards,
Radek

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PGXAConnection and autocommit problem

Maurin, Marion
I actually tried to see if I could see which calls were made and how, but I didn't succeed in seeing any logs. My breakpoints do not work for the driver classes (though it works for any other class), and the logger.logDebug() of the PGXAConnection always returns false (Though the debug mode is activated in log4j.xml and in the driver conf), so I can't see anything logged in those classes.

Spring starts and closes the transaction itself. I'm not doing anything but setting a "@transactional" annotation on the method I want to encapsulate with a transaction. I use a single transaction in which there are select calls on several XA datasources.

-----Message d'origine-----
De : Radosław Smogura [mailto:[hidden email]]
Envoyé : samedi 19 février 2011 11:47
À : [hidden email]
Cc : Maurin, Marion; Achilleas Mantzios
Objet : Re: [JDBC] PGXAConnection and autocommit problem

"Maurin, Marion" <[hidden email]> Thursday 17 February 2011 14:02:28

> Hi,
>
> I checked the two links you proposed.
> Concerning this one:
> http://archives.postgresql.org/message-id/201003021629.07368.achill%40matr
> ix.gatewaynet.com The problem of autocommit described in the conclusion
> seemed to be caused by the reuse of a connexion by different transactions.
> So I tried to run my tests while assuring that the connection used was
> really a new one (checked the ids in debug mode), and nothing changed for
> me. Thus I don't think it is the same problem I'm facing. I also followed
> the advice of activating the debug mode for postgresql logs, and I got
> that: -----
> 18:15:22.623 (1) simple execute,
> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler
> @d458ab, maxRows=0, fetchSize=0, flags=17 18:15:22.623 (1)  FE=>
> Parse(stmt=null,query="   UPDATE ACTUALITE SET TITRE = $1,    CONTENU =
> $2,    IMPORTANTE = $3,    ETAT_ESPACE_DISCUSSION_ID = $4,  
> MODERATION_ID = $5,        AUTEUR_MAJ = $6,    ESPACE_DISCUSSION = $7,    
>   DATE_PUBLICATION = $8,       DATE_EXPIRATION = $9,       DATE_MAJ = $10,
>       IMAGE_POSITION = $11,       IMAGE_DIMENSION = $12,      
> TYPE_ACTUALITE_ID = $13         WHERE ACTUALITE_ID =
> $14",oids={1043,1043,16,0,0,20,16,0,0,0,20,20,20,20}) 18:15:22.623 (1)
> FE=> Bind(stmt=null,portal=null,$1=<'transactions'>,$2=<'<p>test
> transactions mmau prepare
> modif</p>'>,$3=<'1'>,$4=<NULL>,$5=<NULL>,$6=<'110000000000007027'>,$7=<'0'
> >,$8=<'2011-01-17 00:00:00.000000 +01:00:00'>,$9=<'2011-07-05
> 00:00:00.000000 +02:00:00'>,$10=<'2011-02-15 18:15:21.733000
> +01:00:00'>,$11=<'1'>,$12=<'100'>,$13=<'0'>,$14=<'182'>) 18:15:22.623 (1)
> FE=> Describe(portal=null)
> 18:15:22.623 (1)  FE=> Execute(portal=null,limit=0)
> 18:15:22.623 (1)  FE=> Sync
> 18:15:22.639 (1)  <=BE ParseComplete [null]
> 18:15:22.639 (1)  <=BE BindComplete [null]
> 18:15:22.639 (1)  <=BE NoData
> 18:15:22.639 (1)  <=BE CommandStatus(UPDATE 1)
> 18:15:22.639 (1)  <=BE ReadyForQuery(I)
> -----
> As we can see, there is no BEGIN order before the update.
>
> For the second link:
> http://archives.postgresql.org/message-id/4D01DFA8.3060207@... I
> applied the patch you proposed for the prepare() method. Nothing changed
> either. The update was still commited and the postgresql logs did not
> change. I tried to check what it was actually doing in the prepare()
> method, to see if everything was going correctly, but I couldn't succeed
> in seeing either the breakpoints or the debug logs I added. I don't see
> the default PGXAConnection class logs either. Could it be possible that it
> doesn't even go in the prepare() method?
>
> Thanks for any help.
> Marion
>
> -----Message d'origine-----
> De : Maurin, Marion
> Envoyé : jeudi 10 février 2011 19:45
> À : 'Achilleas Mantzios'; [hidden email]
> Objet : RE: [JDBC] PGXAConnection and autocommit problem
>
> Thanks for those two inputs. I couldn't save time today to check but it
> seems interesting. I'll test to see if it's related.
>
> -----Message d'origine-----
> De : [hidden email]
> [mailto:[hidden email]] De la part de Achilleas Mantzios
> Envoyé : jeudi 10 février 2011 10:45
> À : [hidden email]
> Objet : Re: [JDBC] PGXAConnection and autocommit problem
>
> Στις Thursday 10 February 2011 10:42:44 ο/η Heikki Linnakangas έγραψε:
> > On 10.02.2011 09:50, Maurin, Marion wrote:
> > > Yes, I think I saw that too, that the autocommit shouldn't be set from
> > > outside. But shouldn't the driver itself set it to false at the
> > > beginning of the transaction? ________________________________________
> > > De : [hidden email] [[hidden email]]
> > > de la part de rsmogura [[hidden email]] Date d'envoi :
> > > mercredi 9 février 2011 21:13
> > > À : [hidden email]
> > > Objet : Re: [JDBC] PGXAConnection and autocommit problem
> > >
> > >   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.
> >
> > It's quite possible that there's a bug somewhere in the XA driver -
> > we've had several bugs related to autocommit and XA in the past.
>
> I remember my similar case one year ago :
> http://archives.postgresql.org/message-id/201003021629.07368.achill%40matri
> x.gatewaynet.com
>
> Anyways Heikki helped a lot on this one i remember, and also wrote a patch.
> Thanx Heikki!
>
> > And I think this one is still pending:
> > http://archives.postgresql.org/message-id/4D01DFA8.3060207@....
> > Could it be related?
> >
> > Can you create a small self-contained test program to reproduce the
> > issue, and post it to the list?

Looking at code there is no way to set auto commit to true (actually you can
do this using some back door and plain JDBC), but point is that auto commit is
set to false in start(), and changed to true on prepare().

Things that comes to my head are:
1. Transaction Managers call prepare or rollback in meantime of your code,
this can be due to transaction timout - it should close connection too.
2. Like above but from different reasons. In some way connection may be shared
and tried to be used in two transactions.
3. Maybe connection is used without XA, i mean missing call to start, or after
prepare from TM?

I think you should put break point on setAutoCommit in
AbstractJdbc2Connection, or prepare/rollback/commit and check all call stacks
to see what actully calls this.

I saw one time simillar error, but didn't noticed preconditions,

You wrote You use Tomcat + Atomikos, I see Spring there too. Is your bug
reproducible using simpler case only with tomcat / atomikos?

I don't know Spring at all, but I see user transactions, do You call begin or
Spring do it? Do you use multiple (or nested) transactions per call, or many
XA resources?

Regards,
Radek


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.


--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PGXAConnection and autocommit problem

Radosław Smogura-2
 On Mon, 21 Feb 2011 10:05:04 +0000, Maurin, Marion wrote:

> I actually tried to see if I could see which calls were made and how,
> but I didn't succeed in seeing any logs. My breakpoints do not work
> for the driver classes (though it works for any other class), and the
> logger.logDebug() of the PGXAConnection always returns false (Though
> the debug mode is activated in log4j.xml and in the driver conf), so
> I
> can't see anything logged in those classes.
>
> Spring starts and closes the transaction itself. I'm not doing
> anything but setting a "@transactional" annotation on the method I
> want to encapsulate with a transaction. I use a single transaction in
> which there are select calls on several XA datasources.
>
> -----Message d'origine-----
> De : Radosław Smogura [mailto:[hidden email]]
> Envoyé : samedi 19 février 2011 11:47
> À : [hidden email]
> Cc : Maurin, Marion; Achilleas Mantzios
> Objet : Re: [JDBC] PGXAConnection and autocommit problem
>
> "Maurin, Marion" <[hidden email]> Thursday 17 February 2011
> 14:02:28
>> Hi,
>>
>> I checked the two links you proposed.
>> Concerning this one:
>>
>> http://archives.postgresql.org/message-id/201003021629.07368.achill%40matr
>> ix.gatewaynet.com The problem of autocommit described in the
>> conclusion
>> seemed to be caused by the reuse of a connexion by different
>> transactions.
>> So I tried to run my tests while assuring that the connection used
>> was
>> really a new one (checked the ids in debug mode), and nothing
>> changed for
>> me. Thus I don't think it is the same problem I'm facing. I also
>> followed
>> the advice of activating the debug mode for postgresql logs, and I
>> got
>> that: -----
>> 18:15:22.623 (1) simple execute,
>>
>> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler
>> @d458ab, maxRows=0, fetchSize=0, flags=17 18:15:22.623 (1)  FE=>
>> Parse(stmt=null,query="   UPDATE ACTUALITE SET TITRE = $1,    
>> CONTENU =
>> $2,    IMPORTANTE = $3,    ETAT_ESPACE_DISCUSSION_ID = $4,
>> MODERATION_ID = $5,        AUTEUR_MAJ = $6,    ESPACE_DISCUSSION =
>> $7,
>>   DATE_PUBLICATION = $8,       DATE_EXPIRATION = $9,       DATE_MAJ
>> = $10,
>>       IMAGE_POSITION = $11,       IMAGE_DIMENSION = $12,
>> TYPE_ACTUALITE_ID = $13         WHERE ACTUALITE_ID =
>> $14",oids={1043,1043,16,0,0,20,16,0,0,0,20,20,20,20}) 18:15:22.623
>> (1)
>> FE=> Bind(stmt=null,portal=null,$1=<'transactions'>,$2=<'<p>test
>> transactions mmau prepare
>>
>> modif</p>'>,$3=<'1'>,$4=<NULL>,$5=<NULL>,$6=<'110000000000007027'>,$7=<'0'
>> >,$8=<'2011-01-17 00:00:00.000000 +01:00:00'>,$9=<'2011-07-05
>> 00:00:00.000000 +02:00:00'>,$10=<'2011-02-15 18:15:21.733000
>> +01:00:00'>,$11=<'1'>,$12=<'100'>,$13=<'0'>,$14=<'182'>)
>> 18:15:22.623 (1)
>> FE=> Describe(portal=null)
>> 18:15:22.623 (1)  FE=> Execute(portal=null,limit=0)
>> 18:15:22.623 (1)  FE=> Sync
>> 18:15:22.639 (1)  <=BE ParseComplete [null]
>> 18:15:22.639 (1)  <=BE BindComplete [null]
>> 18:15:22.639 (1)  <=BE NoData
>> 18:15:22.639 (1)  <=BE CommandStatus(UPDATE 1)
>> 18:15:22.639 (1)  <=BE ReadyForQuery(I)
>> -----
>> As we can see, there is no BEGIN order before the update.
>>
>> For the second link:
>> http://archives.postgresql.org/message-id/4D01DFA8.3060207@... 
>> I
>> applied the patch you proposed for the prepare() method. Nothing
>> changed
>> either. The update was still commited and the postgresql logs did
>> not
>> change. I tried to check what it was actually doing in the prepare()
>> method, to see if everything was going correctly, but I couldn't
>> succeed
>> in seeing either the breakpoints or the debug logs I added. I don't
>> see
>> the default PGXAConnection class logs either. Could it be possible
>> that it
>> doesn't even go in the prepare() method?
>>
>> Thanks for any help.
>> Marion
>>
>> -----Message d'origine-----
>> De : Maurin, Marion
>> Envoyé : jeudi 10 février 2011 19:45
>> À : 'Achilleas Mantzios'; [hidden email]
>> Objet : RE: [JDBC] PGXAConnection and autocommit problem
>>
>> Thanks for those two inputs. I couldn't save time today to check but
>> it
>> seems interesting. I'll test to see if it's related.
>>
>> -----Message d'origine-----
>> De : [hidden email]
>> [mailto:[hidden email]] De la part de Achilleas
>> Mantzios
>> Envoyé : jeudi 10 février 2011 10:45
>> À : [hidden email]
>> Objet : Re: [JDBC] PGXAConnection and autocommit problem
>>
>> Στις Thursday 10 February 2011 10:42:44 ο/η Heikki Linnakangas
>> έγραψε:
>> > On 10.02.2011 09:50, Maurin, Marion wrote:
>> > > Yes, I think I saw that too, that the autocommit shouldn't be
>> set from
>> > > outside. But shouldn't the driver itself set it to false at the
>> > > beginning of the transaction?
>> ________________________________________
>> > > De : [hidden email]
>> [[hidden email]]
>> > > de la part de rsmogura [[hidden email]] Date d'envoi :
>> > > mercredi 9 février 2011 21:13
>> > > À : [hidden email]
>> > > Objet : Re: [JDBC] PGXAConnection and autocommit problem
>> > >
>> > >   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.
>> >
>> > It's quite possible that there's a bug somewhere in the XA driver
>> -
>> > we've had several bugs related to autocommit and XA in the past.
>>
>> I remember my similar case one year ago :
>>
>> http://archives.postgresql.org/message-id/201003021629.07368.achill%40matri
>> x.gatewaynet.com
>>
>> Anyways Heikki helped a lot on this one i remember, and also wrote a
>> patch.
>> Thanx Heikki!
>>
>> > And I think this one is still pending:
>> >
>> http://archives.postgresql.org/message-id/4D01DFA8.3060207@....
>> > Could it be related?
>> >
>> > Can you create a small self-contained test program to reproduce
>> the
>> > issue, and post it to the list?
>
> Looking at code there is no way to set auto commit to true (actually
> you can
> do this using some back door and plain JDBC), but point is that auto
> commit is
> set to false in start(), and changed to true on prepare().
>
> Things that comes to my head are:
> 1. Transaction Managers call prepare or rollback in meantime of your
> code,
> this can be due to transaction timout - it should close connection
> too.
> 2. Like above but from different reasons. In some way connection may
> be shared
> and tried to be used in two transactions.
> 3. Maybe connection is used without XA, i mean missing call to start,
> or after
> prepare from TM?
>
> I think you should put break point on setAutoCommit in
> AbstractJdbc2Connection, or prepare/rollback/commit and check all
> call stacks
> to see what actully calls this.
>
> I saw one time simillar error, but didn't noticed preconditions,
>
> You wrote You use Tomcat + Atomikos, I see Spring there too. Is your
> bug
> reproducible using simpler case only with tomcat / atomikos?
>
> I don't know Spring at all, but I see user transactions, do You call
> begin or
> Spring do it? Do you use multiple (or nested) transactions per call,
> or many
> XA resources?
>
> Regards,
> Radek
>
>
> 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.


 Will be hard to investigate this without test case.

 If you are using Netbeans download sources appropriate for your JDBC
 version. You may need to import sources to NetBeans (new free form
 project), You don't need to compile those. You may need to go to
 Window->Debugging->Sources right click attach sources (On 7 beta 2
 version). Then use socket attach to attach debugger to tomcat.

 Regards,
 Radek.

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PGXAConnection and autocommit problem

Achilleas Mantzios
Στις Monday 21 February 2011 12:34:26 ο/η rsmogura έγραψε:

>
>  Will be hard to investigate this without test case.
>

I second that. A self-enclosed .war or .ear file which could be directly deployed to a common app server
and simple instructions as to how reproduce the problem would be great help for any person interested in solving this.

>
>  Regards,
>  Radek.
>



--
Achilleas Mantzios

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PGXAConnection and autocommit problem

Maurin, Marion
My debugging (in eclipse with remote debug attached to the driver's sources) still does not work.
However I'll try to make a simple self-contained test case as you said.
Thanks

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de Achilleas Mantzios
Envoyé : mardi 22 février 2011 09:17
À : [hidden email]
Objet : Re: [JDBC] PGXAConnection and autocommit problem

Στις Monday 21 February 2011 12:34:26 ο/η rsmogura έγραψε:

>
>  Will be hard to investigate this without test case.
>

I second that. A self-enclosed .war or .ear file which could be directly deployed to a common app server
and simple instructions as to how reproduce the problem would be great help for any person interested in solving this.

>
>  Regards,
>  Radek.
>



--
Achilleas Mantzios

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc


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.


--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

use of savepoint in containter managed transaction

Amar Dhole
In reply to this post by Achilleas Mantzios
HI All,

I have requirement to use save point. But I am using session bean whose transaction is managed by container. While executing sql insert I got unique key constraint because of this my other command following this exception got ignored. I want to continue my transaction even after this as we ignore this insert and make an entry in other table for this error. And proceed head. with Postgres this is not possible. I cant user plsql block as behaviour is spread in all my application.  Need some suggestion to achieve this with savepoint.

I have some question.
1) Can I use savepoint in Container Managed transaction ?
2) Can I make some custom change to jdbc driver to ignore duplicate key exception or to raise warning instead ERROR.

Thanks
Amar

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: use of savepoint in containter managed transaction

Radosław Smogura-2
On Fri, 23 Sep 2011 06:31:13 +0000, Amar Dhole wrote:

> HI All,
>
> I have requirement to use save point. But I am using session bean
> whose transaction is managed by container. While executing sql insert
> I got unique key constraint because of this my other command
> following
> this exception got ignored. I want to continue my transaction even
> after this as we ignore this insert and make an entry in other table
> for this error. And proceed head. with Postgres this is not possible.
> I cant user plsql block as behaviour is spread in all my application.
> Need some suggestion to achieve this with savepoint.
>
> I have some question.
> 1) Can I use savepoint in Container Managed transaction ?
Yes, You can, but I don't know result of this. ;) One day I worked on
couple of patches, maybe I have those somewhere to acquire this what I
assume You want.

> 2) Can I make some custom change to jdbc driver to ignore duplicate
> key exception or to raise warning instead ERROR.
I assume, You want your transaction to proceed when error will occur.
Yes you can make some change to ignore this, but this will not give you
expected behaviour, because error is generated on server side, and
server will rollback transaction.

But please specify more details mainly what You want achieve and what
are preconditions.
> Thanks
> Amar

Regards,
Radosław Smogura
http://softperience.eu

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: use of savepoint in containter managed transaction

Andrew Hastie
Agree that raising any form of checked exception in a container managed
transaction is simply very bad news. You could try changing over to
using bean managed transactions, but this will likely raise other issues
in how you application is structured.

Surely attempting to insert a record with a duplicate key is bad design?
Could you not do something like the following pseudo-code :-

ResultSet rs  = SELECT * FROM table WHERE pk = 'some_value';
if (rs.next())
{
     // Exists - go and insert into error table
}
else
{
    // Doesn't exist - insert in now
}
rs.close

My personal rule for anything JDBC related when running inside a JEE
container is "keep it simple and clean". That way you'll avoid numerous
problems if you ever have to port your application to another database
provider or even different JDBC driver.

Andrew

On 23/09/11 12:54, Radosław Smogura wrote:

> On Fri, 23 Sep 2011 06:31:13 +0000, Amar Dhole wrote:
>> HI All,
>>
>> I have requirement to use save point. But I am using session bean
>> whose transaction is managed by container. While executing sql insert
>> I got unique key constraint because of this my other command following
>> this exception got ignored. I want to continue my transaction even
>> after this as we ignore this insert and make an entry in other table
>> for this error. And proceed head. with Postgres this is not possible.
>> I cant user plsql block as behaviour is spread in all my application.
>> Need some suggestion to achieve this with savepoint.
>>
>> I have some question.
>> 1) Can I use savepoint in Container Managed transaction ?
> Yes, You can, but I don't know result of this. ;) One day I worked on
> couple of patches, maybe I have those somewhere to acquire this what I
> assume You want.
>
>> 2) Can I make some custom change to jdbc driver to ignore duplicate
>> key exception or to raise warning instead ERROR.
> I assume, You want your transaction to proceed when error will occur.
> Yes you can make some change to ignore this, but this will not give
> you expected behaviour, because error is generated on server side, and
> server will rollback transaction.
>
> But please specify more details mainly what You want achieve and what
> are preconditions.
>> Thanks
>> Amar
>
> Regards,
> Radosław Smogura
> http://softperience.eu
>

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: use of savepoint in containter managed transaction

Amar Dhole
In reply to this post by Radosław Smogura-2
Hi,

I have tried to use savepoint in jboss I got exception ,
"Since it is managed transaction rollback not allowed"
It will be great help how to avoid this error.

2) I have session bean where my transaction started it has assume 20 insert in one table at 10th insert I get duplicate key exception what we do in our application increase key by catching exception and proceed ahead by in postgress when this exception occurs my all insert gets error out. So I thought to use savepoint or any other way to avoid this exception and proceed in my transaction.

Thanks,
Amar

----- Original Message -----
From: Radosław Smogura [mailto:[hidden email]]
Sent: Friday, September 23, 2011 04:54 AM
To: Amar Dhole
Cc: [hidden email] <[hidden email]>
Subject: Re: [JDBC] use of savepoint in containter managed transaction

On Fri, 23 Sep 2011 06:31:13 +0000, Amar Dhole wrote:

> HI All,
>
> I have requirement to use save point. But I am using session bean
> whose transaction is managed by container. While executing sql insert
> I got unique key constraint because of this my other command
> following
> this exception got ignored. I want to continue my transaction even
> after this as we ignore this insert and make an entry in other table
> for this error. And proceed head. with Postgres this is not possible.
> I cant user plsql block as behaviour is spread in all my application.
> Need some suggestion to achieve this with savepoint.
>
> I have some question.
> 1) Can I use savepoint in Container Managed transaction ?
Yes, You can, but I don't know result of this. ;) One day I worked on
couple of patches, maybe I have those somewhere to acquire this what I
assume You want.

> 2) Can I make some custom change to jdbc driver to ignore duplicate
> key exception or to raise warning instead ERROR.
I assume, You want your transaction to proceed when error will occur.
Yes you can make some change to ignore this, but this will not give you
expected behaviour, because error is generated on server side, and
server will rollback transaction.

But please specify more details mainly what You want achieve and what
are preconditions.
> Thanks
> Amar

Regards,
Radosław Smogura
http://softperience.eu

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: use of savepoint in containter managed transaction

Radosław Smogura-2
I may suggest You, to try to manually use savepoints and rollbacks by
explicite specifying such queryies

createStatemnet().executeUpdate("SAVEPOINT ...")
createStatemnet().executeUpdate(ROLLBACK TO SAVEPOINT....)
createStatemnet().executeUpdate(RELEASE SAVEPOINT....)
but do not use COMMIT, nor ROLLBACK, nor PREPARE TRANSACTION.

You will need to put some work to arm your code in necessary try/catch.

If this will work, and consequences of this I don't know (I suppose at
80-90% that this should not affect XA, it's really hard to tell at eye
glance).

To your 1st topic
"Since it is managed transaction rollback not allowed" I couldn't find
itin JDBC source code, so I assume, JBoss wrapped driver. PG driver will
prohibit this behaviour too, but with different message.

Regards,
Radosław Smogura
http://softperience.eu

On Fri, 23 Sep 2011 13:19:53 +0000, Amar Dhole wrote:

> Hi,
>
> I have tried to use savepoint in jboss I got exception ,
> "Since it is managed transaction rollback not allowed"
> It will be great help how to avoid this error.
>
> 2) I have session bean where my transaction started it has assume 20
> insert in one table at 10th insert I get duplicate key exception what
> we do in our application increase key by catching exception and
> proceed ahead by in postgress when this exception occurs my all
> insert
> gets error out. So I thought to use savepoint or any other way to
> avoid this exception and proceed in my transaction.
>
> Thanks,
> Amar
>
> ----- Original Message -----
> From: Radosław Smogura [mailto:[hidden email]]
> Sent: Friday, September 23, 2011 04:54 AM
> To: Amar Dhole
> Cc: [hidden email] <[hidden email]>
> Subject: Re: [JDBC] use of savepoint in containter managed
> transaction
>
> On Fri, 23 Sep 2011 06:31:13 +0000, Amar Dhole wrote:
>> HI All,
>>
>> I have requirement to use save point. But I am using session bean
>> whose transaction is managed by container. While executing sql
>> insert
>> I got unique key constraint because of this my other command
>> following
>> this exception got ignored. I want to continue my transaction even
>> after this as we ignore this insert and make an entry in other table
>> for this error. And proceed head. with Postgres this is not
>> possible.
>> I cant user plsql block as behaviour is spread in all my
>> application.
>> Need some suggestion to achieve this with savepoint.
>>
>> I have some question.
>> 1) Can I use savepoint in Container Managed transaction ?
> Yes, You can, but I don't know result of this. ;) One day I worked on
> couple of patches, maybe I have those somewhere to acquire this what
> I
> assume You want.
>
>> 2) Can I make some custom change to jdbc driver to ignore duplicate
>> key exception or to raise warning instead ERROR.
> I assume, You want your transaction to proceed when error will occur.
> Yes you can make some change to ignore this, but this will not give
> you
> expected behaviour, because error is generated on server side, and
> server will rollback transaction.
>
> But please specify more details mainly what You want achieve and what
> are preconditions.
>> Thanks
>> Amar
>
> Regards,
> Radosław Smogura
> http://softperience.eu


--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: use of savepoint in containter managed transaction

Amar Dhole
Hi Radosław,

This approach works fine.
Thanks for your valuable inputs.

Thanks
Amar

-----Original Message-----
From: Radosław Smogura [mailto:[hidden email]]
Sent: 23 September 2011 19:29
To: Amar Dhole
Cc: '[hidden email]'
Subject: Re: [JDBC] use of savepoint in containter managed transaction

I may suggest You, to try to manually use savepoints and rollbacks by
explicite specifying such queryies

createStatemnet().executeUpdate("SAVEPOINT ...")
createStatemnet().executeUpdate(ROLLBACK TO SAVEPOINT....)
createStatemnet().executeUpdate(RELEASE SAVEPOINT....)
but do not use COMMIT, nor ROLLBACK, nor PREPARE TRANSACTION.

You will need to put some work to arm your code in necessary try/catch.

If this will work, and consequences of this I don't know (I suppose at
80-90% that this should not affect XA, it's really hard to tell at eye
glance).

To your 1st topic
"Since it is managed transaction rollback not allowed" I couldn't find
itin JDBC source code, so I assume, JBoss wrapped driver. PG driver will
prohibit this behaviour too, but with different message.

Regards,
Radosław Smogura
http://softperience.eu

On Fri, 23 Sep 2011 13:19:53 +0000, Amar Dhole wrote:

> Hi,
>
> I have tried to use savepoint in jboss I got exception ,
> "Since it is managed transaction rollback not allowed"
> It will be great help how to avoid this error.
>
> 2) I have session bean where my transaction started it has assume 20
> insert in one table at 10th insert I get duplicate key exception what
> we do in our application increase key by catching exception and
> proceed ahead by in postgress when this exception occurs my all
> insert
> gets error out. So I thought to use savepoint or any other way to
> avoid this exception and proceed in my transaction.
>
> Thanks,
> Amar
>
> ----- Original Message -----
> From: Radosław Smogura [mailto:[hidden email]]
> Sent: Friday, September 23, 2011 04:54 AM
> To: Amar Dhole
> Cc: [hidden email] <[hidden email]>
> Subject: Re: [JDBC] use of savepoint in containter managed
> transaction
>
> On Fri, 23 Sep 2011 06:31:13 +0000, Amar Dhole wrote:
>> HI All,
>>
>> I have requirement to use save point. But I am using session bean
>> whose transaction is managed by container. While executing sql
>> insert
>> I got unique key constraint because of this my other command
>> following
>> this exception got ignored. I want to continue my transaction even
>> after this as we ignore this insert and make an entry in other table
>> for this error. And proceed head. with Postgres this is not
>> possible.
>> I cant user plsql block as behaviour is spread in all my
>> application.
>> Need some suggestion to achieve this with savepoint.
>>
>> I have some question.
>> 1) Can I use savepoint in Container Managed transaction ?
> Yes, You can, but I don't know result of this. ;) One day I worked on
> couple of patches, maybe I have those somewhere to acquire this what
> I
> assume You want.
>
>> 2) Can I make some custom change to jdbc driver to ignore duplicate
>> key exception or to raise warning instead ERROR.
> I assume, You want your transaction to proceed when error will occur.
> Yes you can make some change to ignore this, but this will not give
> you
> expected behaviour, because error is generated on server side, and
> server will rollback transaction.
>
> But please specify more details mainly what You want achieve and what
> are preconditions.
>> Thanks
>> Amar
>
> Regards,
> Radosław Smogura
> http://softperience.eu


--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: use of savepoint in containter managed transaction

Craig Ringer-2
In reply to this post by Radosław Smogura-2
On 23/09/2011 9:58 PM, Radosław Smogura wrote:

> I may suggest You, to try to manually use savepoints and rollbacks by
> explicite specifying such queryies
>
> createStatemnet().executeUpdate("SAVEPOINT ...")
> createStatemnet().executeUpdate(ROLLBACK TO SAVEPOINT....)
> createStatemnet().executeUpdate(RELEASE SAVEPOINT....)
> but do not use COMMIT, nor ROLLBACK, nor PREPARE TRANSACTION.
>
> You will need to put some work to arm your code in necessary try/catch.
>
> If this will work, and consequences of this I don't know (I suppose at
> 80-90% that this should not affect XA, it's really hard to tell at eye
> glance).

Alternately, tell your container you want a non-JTA-managed tansaction
so you don't need to worry about disturbing its view of the transaction
state. That's probably safer, since you want to manage your own
transactions so you don't really want JTA anyway.

--
Craig Ringer

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Loading...