Thread: PGXAConnection and autocommit problem
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.
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
Yes, I think I saw that too, that the autocommit shouldn't be set from outside. But shouldn't the driver itself set it tofalse at the beginning of the transaction? ________________________________________ De : pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] de la part de rsmogura [rsmogura@softperience.eu] Date d'envoi : mercredi 9 février 2011 21:13 À : pgsql-jdbc@postgresql.org 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 (pgsql-jdbc@postgresql.org) 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 orused by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachmentand all copies and inform the sender. Thank you.
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 itto false at the beginning of the transaction? > ________________________________________ > De : pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] de la part de rsmogura [rsmogura@softperience.eu] > Date d'envoi : mercredi 9 février 2011 21:13 > À : pgsql-jdbc@postgresql.org > 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@emaze.net. 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
Στις 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 itto false at the beginning of the transaction? > > ________________________________________ > > De : pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] de la part de rsmogura [rsmogura@softperience.eu] > > Date d'envoi : mercredi 9 février 2011 21:13 > > À : pgsql-jdbc@postgresql.org > > 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@emaze.net. > 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
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 : pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] De la part de Achilleas Mantzios Envoyé : jeudi 10 février 2011 10:45 À : pgsql-jdbc@postgresql.org 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 itto false at the beginning of the transaction? > > ________________________________________ > > De : pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] de la part de rsmogura [rsmogura@softperience.eu] > > Date d'envoi : mercredi 9 février 2011 21:13 > > À : pgsql-jdbc@postgresql.org > > 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@emaze.net. > 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 (pgsql-jdbc@postgresql.org) 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 orused by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachmentand all copies and inform the sender. Thank you.
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 indebug mode), and nothing changed for me. Thus I don't think it is the same problem I'm facing. I also followed the adviceof 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-1700:00:00.000000 +01:00:00'>,$9=<'2011-07-0500: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@emaze.net I applied the patch you proposed for the prepare() method. Nothing changed either. The update was still commited and thepostgresql logs did not change. I tried to check what it was actually doing in the prepare() method, to see if everythingwas going correctly, but I couldn't succeed in seeing either the breakpoints or the debug logs I added. I don'tsee 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'; pgsql-jdbc@postgresql.org 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 : pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] De la part de Achilleas Mantzios Envoyé : jeudi 10 février 2011 10:45 À : pgsql-jdbc@postgresql.org 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 itto false at the beginning of the transaction? > > ________________________________________ > > De : pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] de la part de rsmogura [rsmogura@softperience.eu] > > Date d'envoi : mercredi 9 février 2011 21:13 > > À : pgsql-jdbc@postgresql.org > > 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@emaze.net. > 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 (pgsql-jdbc@postgresql.org) 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 orused by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachmentand all copies and inform the sender. Thank you.
"Maurin, Marion" <marion.maurin@logica.com> 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@emaze.net 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'; pgsql-jdbc@postgresql.org > 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 : pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] De la part de Achilleas Mantzios > Envoyé : jeudi 10 février 2011 10:45 > À : pgsql-jdbc@postgresql.org > 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 : pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] > > > de la part de rsmogura [rsmogura@softperience.eu] Date d'envoi : > > > mercredi 9 février 2011 21:13 > > > À : pgsql-jdbc@postgresql.org > > > 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@emaze.net. > > 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
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 breakpointsdo not work for the driver classes (though it works for any other class), and the logger.logDebug() of the PGXAConnectionalways returns false (Though the debug mode is activated in log4j.xml and in the driver conf), so I can't seeanything logged in those classes. Spring starts and closes the transaction itself. I'm not doing anything but setting a "@transactional" annotation on themethod I want to encapsulate with a transaction. I use a single transaction in which there are select calls on severalXA datasources. -----Message d'origine----- De : Radosław Smogura [mailto:rsmogura@softperience.eu] Envoyé : samedi 19 février 2011 11:47 À : pgsql-jdbc@postgresql.org Cc : Maurin, Marion; Achilleas Mantzios Objet : Re: [JDBC] PGXAConnection and autocommit problem "Maurin, Marion" <marion.maurin@logica.com> 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@emaze.net 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'; pgsql-jdbc@postgresql.org > 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 : pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] De la part de Achilleas Mantzios > Envoyé : jeudi 10 février 2011 10:45 > À : pgsql-jdbc@postgresql.org > 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 : pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] > > > de la part de rsmogura [rsmogura@softperience.eu] Date d'envoi : > > > mercredi 9 février 2011 21:13 > > > À : pgsql-jdbc@postgresql.org > > > 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@emaze.net. > > 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 orused by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachmentand all copies and inform the sender. Thank you.
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:rsmogura@softperience.eu] > Envoyé : samedi 19 février 2011 11:47 > À : pgsql-jdbc@postgresql.org > Cc : Maurin, Marion; Achilleas Mantzios > Objet : Re: [JDBC] PGXAConnection and autocommit problem > > "Maurin, Marion" <marion.maurin@logica.com> 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@emaze.net >> 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'; pgsql-jdbc@postgresql.org >> 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 : pgsql-jdbc-owner@postgresql.org >> [mailto:pgsql-jdbc-owner@postgresql.org] De la part de Achilleas >> Mantzios >> Envoyé : jeudi 10 février 2011 10:45 >> À : pgsql-jdbc@postgresql.org >> 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 : pgsql-jdbc-owner@postgresql.org >> [pgsql-jdbc-owner@postgresql.org] >> > > de la part de rsmogura [rsmogura@softperience.eu] Date d'envoi : >> > > mercredi 9 février 2011 21:13 >> > > À : pgsql-jdbc@postgresql.org >> > > 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@emaze.net. >> > 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.
Στις 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
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 : pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] De la part de Achilleas Mantzios Envoyé : mardi 22 février 2011 09:17 À : pgsql-jdbc@postgresql.org 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 (pgsql-jdbc@postgresql.org) 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 orused by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachmentand all copies and inform the sender. Thank you.
HI All, I have requirement to use save point. But I am using session bean whose transaction is managed by container. While executingsql insert I got unique key constraint because of this my other command following this exception got ignored. Iwant 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
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
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 keyexception what we do in our application increase key by catching exception and proceed ahead by in postgress when thisexception occurs my all insert gets error out. So I thought to use savepoint or any other way to avoid this exceptionand proceed in my transaction. Thanks, Amar ----- Original Message ----- From: Radosław Smogura [mailto:rsmogura@softperience.eu] Sent: Friday, September 23, 2011 04:54 AM To: Amar Dhole Cc: pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org> 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
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
>
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:rsmogura@softperience.eu]
> Sent: Friday, September 23, 2011 04:54 AM
> To: Amar Dhole
> Cc: pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>
> 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
Hi Radosław,
This approach works fine.
Thanks for your valuable inputs.
Thanks
Amar
-----Original Message-----
From: Radosław Smogura [mailto:rsmogura@softperience.eu]
Sent: 23 September 2011 19:29
To: Amar Dhole
Cc: 'pgsql-jdbc@postgresql.org'
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:rsmogura@softperience.eu]
> Sent: Friday, September 23, 2011 04:54 AM
> To: Amar Dhole
> Cc: pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>
> 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
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