org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.

Spring JIRA | Hendy Irawan | 3 years ago
  1. 0

    Note that care has been taken (via Quartz scheduler) to ensure no two same jobs are launched at the same time. Error happens when using PostgreSQL 9.1 database, since 9.1 supports SERIALIZABLE. (PostgreSQL 8.4 treated SERIALIZABLE isolation as REPEATABLE_READ) Note that we've tried using separate "batch" schema (not "public" schema) but the error occurs. Also we're also using the same database for app tables, however this shouldn't be an issue because Spring Batch and app would use different connection and hence different transactions. BTW the app also performs read-only queries to batch_* tables, using READ_COMMITTED level. Can this affect SERIALIZABLE isolation behavior? We're using a single c3p0 connection pool for both Spring Batch and the app. Sample stacktraces: {code} 15:12:16.423 | INFO | oryBean_Worker-1 | c.q.f.j.SchedulerConfig | rConfig$MoveIntakeIntoMergingJob 84 | Launching Spring Batch SimpleJob: [name=moveIntakeIntoMerging] using org.springframework.batch.core.launch.support.SimpleJobLauncher@15df257e 15:12:16.423 | INFO | oryBean_Worker-4 | ulerConfig$NowLocationVisitorJob | ulerConfig$NowLocationVisitorJob 259 | Launching Spring Batch SimpleJob: [name=locationVisitorAggregator] using org.springframework.batch.core.launch.support.SimpleJobLauncher@15df257e 15:12:16.423 | INFO | oryBean_Worker-2 | c.q.f.j.SchedulerConfig | SchedulerConfig$AugmentFieldsJob 123 | Launching Spring Batch SimpleJob: [name=augmentFieldsJob] using org.springframework.batch.core.launch.support.SimpleJobLauncher@15df257e 15:12:16.423 | INFO | oryBean_Worker-3 | c.q.f.j.SchedulerConfig | SchedulerConfig$LoadToArchiveJob 158 | Launching Spring Batch SimpleJob: [name=loadToArchive] using org.springframework.batch.core.launch.support.SimpleJobLauncher@15df257e 15:12:16.575 | ERROR | oryBean_Worker-4 | o.q.c.JobRunShell | org.quartz.core.JobRunShell 216 | Job DEFAULT.absLocationVisitorAggregator threw an unhandled Exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:271) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at com.sun.proxy.$Proxy31.createJobExecution(Unknown Source) ~[na:na] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_15] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_15] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_15] at java.lang.reflect.Method.invoke(Method.java:601) ~[na:1.7.0_15] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at com.sun.proxy.$Proxy32.run(Unknown Source) ~[na:na] at com.quikdo.freq.job.SchedulerConfig$NowLocationVisitorJob.execute(SchedulerConfig.java:273) ~[SchedulerConfig$NowLocationVisitorJob.class:na] at org.quartz.core.JobRunShell.run(JobRunShell.java:207) ~[quartz-2.2.0.jar:na] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560) [quartz-2.2.0.jar:na] Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:1032) ~[c3p0-0.9.5-pre3.jar:0.9.5-pre3] at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:268) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE] ... 25 common frames omitted 15:12:16.576 | ERROR | oryBean_Worker-4 | o.q.c.ErrorLogger | org.quartz.core.ErrorLogger 2460 | Job (DEFAULT.absLocationVisitorAggregator threw an exception. org.quartz.SchedulerException: Job threw an unhandled exception. at org.quartz.core.JobRunShell.run(JobRunShell.java:218) ~[quartz-2.2.0.jar:na] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560) [quartz-2.2.0.jar:na] Caused by: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:271) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at com.sun.proxy.$Proxy31.createJobExecution(Unknown Source) ~[na:na] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_15] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_15] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_15] at java.lang.reflect.Method.invoke(Method.java:601) ~[na:1.7.0_15] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at com.sun.proxy.$Proxy32.run(Unknown Source) ~[na:na] at com.quikdo.freq.job.SchedulerConfig$NowLocationVisitorJob.execute(SchedulerConfig.java:273) ~[SchedulerConfig$NowLocationVisitorJob.class:na] at org.quartz.core.JobRunShell.run(JobRunShell.java:207) ~[quartz-2.2.0.jar:na] ... 1 common frames omitted Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:1032) ~[c3p0-0.9.5-pre3.jar:0.9.5-pre3] at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:268) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE] ... 25 common frames omitted {code} {code} 03:52:41.141 | DEBUG | oryBean_Worker-6 | c.q.f.j.w.HomePage | com.quikdo.freq.job.web.HomePage 140 | JobToBeExecuted Job DEFAULT.augmentFields fired (by trigger DEFAULT.augmentFields_poll) at: 03:52:41 08/22/2013 03:52:41.144 | INFO | oryBean_Worker-6 | c.q.f.j.SchedulerConfig | SchedulerConfig$AugmentFieldsJob 129 | Launching Spring Batch SimpleJob: [name=augmentFieldsJob] using org.springframework.batch.core.launch.support.SimpleJobLauncher@69c5f9c9 03:52:41.269 | ERROR | oryBean_Worker-6 | o.q.c.JobRunShell | org.quartz.core.JobRunShell 216 | Job DEFAULT.augmentFields threw an unhandled Exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:271) ~[spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at com.sun.proxy.$Proxy39.createJobExecution(Unknown Source) ~[na:na] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at sun.reflect.GeneratedMethodAccessor74.invoke(Unknown Source) ~[na:na] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_25] at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_25] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at com.sun.proxy.$Proxy41.run(Unknown Source) ~[na:na] at com.quikdo.freq.job.SchedulerConfig$AugmentFieldsJob.execute(SchedulerConfig.java:135) ~[SchedulerConfig$AugmentFieldsJob.class:na] at org.quartz.core.JobRunShell.run(JobRunShell.java:207) ~[quartz-2.2.0.jar:na] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560) ~[quartz-2.2.0.jar:na] Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:1032) ~[c3p0-0.9.5-pre3.jar:0.9.5-pre3] at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:268) ~[spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE] ... 24 common frames omitted 03:52:41.271 | ERROR | oryBean_Worker-6 | o.q.c.ErrorLogger | org.quartz.core.ErrorLogger 2460 | Job (DEFAULT.augmentFields threw an exception. org.quartz.SchedulerException: Job threw an unhandled exception. at org.quartz.core.JobRunShell.run(JobRunShell.java:218) ~[quartz-2.2.0.jar:na] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560) ~[quartz-2.2.0.jar:na] Caused by: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:271) ~[spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at com.sun.proxy.$Proxy39.createJobExecution(Unknown Source) ~[na:na] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at sun.reflect.GeneratedMethodAccessor74.invoke(Unknown Source) ~[na:na] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_25] at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_25] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at com.sun.proxy.$Proxy41.run(Unknown Source) ~[na:na] at com.quikdo.freq.job.SchedulerConfig$AugmentFieldsJob.execute(SchedulerConfig.java:135) ~[SchedulerConfig$AugmentFieldsJob.class:na] at org.quartz.core.JobRunShell.run(JobRunShell.java:207) ~[quartz-2.2.0.jar:na] ... 1 common frames omitted Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:1032) ~[c3p0-0.9.5-pre3.jar:0.9.5-pre3] at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:268) ~[spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE] ... 24 common frames omitted 03:52:41.272 | DEBUG | oryBean_Worker-6 | c.q.f.j.w.HomePage | com.quikdo.freq.job.web.HomePage 146 | JobWasExecuted Job DEFAULT.augmentFields execution failed at 03:52:41 08/22/2013 and reports: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.] - org.quartz.JobExecutionException: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.] [See nested exception: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.]] {code} The problem goes away entire if we configure Spring Batch JobRepository to use ISOLATION_REPEATABLE_READ. While this is working workaround, it doesn't "feel good" : {code} /** * Configures Spring Batch infrastructure. * @author ceefour * @todo Transaction config: http://static.springsource.org/spring-batch/reference/html/configureJob.html#txConfigForJobRepository * http://stackoverflow.com/a/9672848/122441 */ @Configuration @EnableBatchProcessing public class InfrastructureConfig extends DefaultBatchConfigurer { @Inject private Environment env; @Inject private DataSource dataSource; @Bean public EventBus eventBus() { return new EventBus(); } @Override protected JobRepository createJobRepository() throws Exception { JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean(); factory.setDataSource(dataSource); factory.setTransactionManager(getTransactionManager()); factory.setTablePrefix("batch.batch_"); factory.setIsolationLevelForCreate("ISOLATION_REPEATABLE_READ"); // find a way to still use SERIALIZABLE factory.afterPropertiesSet(); return (JobRepository) factory.getObject(); } } {code}

    Spring JIRA | 3 years ago | Hendy Irawan
    org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.
  2. 0

    Note that care has been taken (via Quartz scheduler) to ensure no two same jobs are launched at the same time. Error happens when using PostgreSQL 9.1 database, since 9.1 supports SERIALIZABLE. (PostgreSQL 8.4 treated SERIALIZABLE isolation as REPEATABLE_READ) Note that we've tried using separate "batch" schema (not "public" schema) but the error occurs. Also we're also using the same database for app tables, however this shouldn't be an issue because Spring Batch and app would use different connection and hence different transactions. BTW the app also performs read-only queries to batch_* tables, using READ_COMMITTED level. Can this affect SERIALIZABLE isolation behavior? We're using a single c3p0 connection pool for both Spring Batch and the app. Sample stacktraces: {code} 15:12:16.423 | INFO | oryBean_Worker-1 | c.q.f.j.SchedulerConfig | rConfig$MoveIntakeIntoMergingJob 84 | Launching Spring Batch SimpleJob: [name=moveIntakeIntoMerging] using org.springframework.batch.core.launch.support.SimpleJobLauncher@15df257e 15:12:16.423 | INFO | oryBean_Worker-4 | ulerConfig$NowLocationVisitorJob | ulerConfig$NowLocationVisitorJob 259 | Launching Spring Batch SimpleJob: [name=locationVisitorAggregator] using org.springframework.batch.core.launch.support.SimpleJobLauncher@15df257e 15:12:16.423 | INFO | oryBean_Worker-2 | c.q.f.j.SchedulerConfig | SchedulerConfig$AugmentFieldsJob 123 | Launching Spring Batch SimpleJob: [name=augmentFieldsJob] using org.springframework.batch.core.launch.support.SimpleJobLauncher@15df257e 15:12:16.423 | INFO | oryBean_Worker-3 | c.q.f.j.SchedulerConfig | SchedulerConfig$LoadToArchiveJob 158 | Launching Spring Batch SimpleJob: [name=loadToArchive] using org.springframework.batch.core.launch.support.SimpleJobLauncher@15df257e 15:12:16.575 | ERROR | oryBean_Worker-4 | o.q.c.JobRunShell | org.quartz.core.JobRunShell 216 | Job DEFAULT.absLocationVisitorAggregator threw an unhandled Exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:271) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at com.sun.proxy.$Proxy31.createJobExecution(Unknown Source) ~[na:na] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_15] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_15] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_15] at java.lang.reflect.Method.invoke(Method.java:601) ~[na:1.7.0_15] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at com.sun.proxy.$Proxy32.run(Unknown Source) ~[na:na] at com.quikdo.freq.job.SchedulerConfig$NowLocationVisitorJob.execute(SchedulerConfig.java:273) ~[SchedulerConfig$NowLocationVisitorJob.class:na] at org.quartz.core.JobRunShell.run(JobRunShell.java:207) ~[quartz-2.2.0.jar:na] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560) [quartz-2.2.0.jar:na] Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:1032) ~[c3p0-0.9.5-pre3.jar:0.9.5-pre3] at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:268) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE] ... 25 common frames omitted 15:12:16.576 | ERROR | oryBean_Worker-4 | o.q.c.ErrorLogger | org.quartz.core.ErrorLogger 2460 | Job (DEFAULT.absLocationVisitorAggregator threw an exception. org.quartz.SchedulerException: Job threw an unhandled exception. at org.quartz.core.JobRunShell.run(JobRunShell.java:218) ~[quartz-2.2.0.jar:na] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560) [quartz-2.2.0.jar:na] Caused by: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:271) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at com.sun.proxy.$Proxy31.createJobExecution(Unknown Source) ~[na:na] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_15] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_15] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_15] at java.lang.reflect.Method.invoke(Method.java:601) ~[na:1.7.0_15] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117) ~[spring-batch-core-2.2.0.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE] at com.sun.proxy.$Proxy32.run(Unknown Source) ~[na:na] at com.quikdo.freq.job.SchedulerConfig$NowLocationVisitorJob.execute(SchedulerConfig.java:273) ~[SchedulerConfig$NowLocationVisitorJob.class:na] at org.quartz.core.JobRunShell.run(JobRunShell.java:207) ~[quartz-2.2.0.jar:na] ... 1 common frames omitted Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:1032) ~[c3p0-0.9.5-pre3.jar:0.9.5-pre3] at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:268) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE] ... 25 common frames omitted {code} {code} 03:52:41.141 | DEBUG | oryBean_Worker-6 | c.q.f.j.w.HomePage | com.quikdo.freq.job.web.HomePage 140 | JobToBeExecuted Job DEFAULT.augmentFields fired (by trigger DEFAULT.augmentFields_poll) at: 03:52:41 08/22/2013 03:52:41.144 | INFO | oryBean_Worker-6 | c.q.f.j.SchedulerConfig | SchedulerConfig$AugmentFieldsJob 129 | Launching Spring Batch SimpleJob: [name=augmentFieldsJob] using org.springframework.batch.core.launch.support.SimpleJobLauncher@69c5f9c9 03:52:41.269 | ERROR | oryBean_Worker-6 | o.q.c.JobRunShell | org.quartz.core.JobRunShell 216 | Job DEFAULT.augmentFields threw an unhandled Exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:271) ~[spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at com.sun.proxy.$Proxy39.createJobExecution(Unknown Source) ~[na:na] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at sun.reflect.GeneratedMethodAccessor74.invoke(Unknown Source) ~[na:na] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_25] at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_25] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at com.sun.proxy.$Proxy41.run(Unknown Source) ~[na:na] at com.quikdo.freq.job.SchedulerConfig$AugmentFieldsJob.execute(SchedulerConfig.java:135) ~[SchedulerConfig$AugmentFieldsJob.class:na] at org.quartz.core.JobRunShell.run(JobRunShell.java:207) ~[quartz-2.2.0.jar:na] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560) ~[quartz-2.2.0.jar:na] Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:1032) ~[c3p0-0.9.5-pre3.jar:0.9.5-pre3] at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:268) ~[spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE] ... 24 common frames omitted 03:52:41.271 | ERROR | oryBean_Worker-6 | o.q.c.ErrorLogger | org.quartz.core.ErrorLogger 2460 | Job (DEFAULT.augmentFields threw an exception. org.quartz.SchedulerException: Job threw an unhandled exception. at org.quartz.core.JobRunShell.run(JobRunShell.java:218) ~[quartz-2.2.0.jar:na] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560) ~[quartz-2.2.0.jar:na] Caused by: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:271) ~[spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at com.sun.proxy.$Proxy39.createJobExecution(Unknown Source) ~[na:na] at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at sun.reflect.GeneratedMethodAccessor74.invoke(Unknown Source) ~[na:na] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_25] at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_25] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117) ~[spring-batch-core-2.2.1.RELEASE.jar:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] at com.sun.proxy.$Proxy41.run(Unknown Source) ~[na:na] at com.quikdo.freq.job.SchedulerConfig$AugmentFieldsJob.execute(SchedulerConfig.java:135) ~[SchedulerConfig$AugmentFieldsJob.class:na] at org.quartz.core.JobRunShell.run(JobRunShell.java:207) ~[quartz-2.2.0.jar:na] ... 1 common frames omitted Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:1032) ~[c3p0-0.9.5-pre3.jar:0.9.5-pre3] at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:268) ~[spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE] ... 24 common frames omitted 03:52:41.272 | DEBUG | oryBean_Worker-6 | c.q.f.j.w.HomePage | com.quikdo.freq.job.web.HomePage 146 | JobWasExecuted Job DEFAULT.augmentFields execution failed at 03:52:41 08/22/2013 and reports: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.] - org.quartz.JobExecutionException: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.] [See nested exception: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.]] {code} The problem goes away entire if we configure Spring Batch JobRepository to use ISOLATION_REPEATABLE_READ. While this is working workaround, it doesn't "feel good" : {code} /** * Configures Spring Batch infrastructure. * @author ceefour * @todo Transaction config: http://static.springsource.org/spring-batch/reference/html/configureJob.html#txConfigForJobRepository * http://stackoverflow.com/a/9672848/122441 */ @Configuration @EnableBatchProcessing public class InfrastructureConfig extends DefaultBatchConfigurer { @Inject private Environment env; @Inject private DataSource dataSource; @Bean public EventBus eventBus() { return new EventBus(); } @Override protected JobRepository createJobRepository() throws Exception { JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean(); factory.setDataSource(dataSource); factory.setTransactionManager(getTransactionManager()); factory.setTablePrefix("batch.batch_"); factory.setIsolationLevelForCreate("ISOLATION_REPEATABLE_READ"); // find a way to still use SERIALIZABLE factory.afterPropertiesSet(); return (JobRepository) factory.getObject(); } } {code}

    Spring JIRA | 3 years ago | Hendy Irawan
    org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.
  3. 0

    1.2RC1 is available for testing

    Google Groups | 6 years ago | Guillaume Bort
    org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Problem in reactivateIt custom window

    adempiere | 7 years ago | jairah
    org.postgresql.util.PSQLException: ERROR: update or delete on table "m_locator" violates foreign key constraint "mlocator_minventorycount" on table "m_transaction"   Detail: Key (m_locator_id)=(1000011) is still referenced from table "m_transaction".; State=23503; ErrorCode=0
  6. 0

    GL Journal can not complete

    adempiere | 6 years ago | andijani
    org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.; State=08006; ErrorCode=0

    1 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. org.postgresql.util.PSQLException

      ERROR: could not serialize access due to read/write dependencies among transactions Detail: Reason code: Canceled on commit attempt with conflict in from prepared pivot. Hint: The transaction might succeed if retried.

      at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse()
    2. PostgreSQL JDBC Driver
      AbstractJdbc2Connection.commit
      1. org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)[postgresql-9.2-1003-jdbc4.jar:na]
      2. org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)[postgresql-9.2-1003-jdbc4.jar:na]
      3. org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)[postgresql-9.2-1003-jdbc4.jar:na]
      4. org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:793)[postgresql-9.2-1003-jdbc4.jar:na]
      5. org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:817)[postgresql-9.2-1003-jdbc4.jar:na]
      5 frames
    3. c3p0:JDBC DataSources/Resource Pools
      NewProxyConnection.commit
      1. com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:1032)[c3p0-0.9.5-pre3.jar:0.9.5-pre3]
      1 frame
    4. Spring Framework
      DataSourceTransactionManager.doCommit
      1. org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:268)[spring-jdbc-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      1 frame
    5. Spring Tx
      TransactionInterceptor.invoke
      1. org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755)[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      2. org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724)[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      3. org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475)[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      4. org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270)[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      5. org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      5 frames
    6. Spring AOP
      ReflectiveMethodInvocation.proceed
      1. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      1 frame
    7. Spring Batch Core
      AbstractJobRepositoryFactoryBean$1.invoke
      1. org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172)[spring-batch-core-2.2.1.RELEASE.jar:na]
      1 frame
    8. Spring AOP
      JdkDynamicAopProxy.invoke
      1. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      2. org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      2 frames
    9. com.sun.proxy
      $Proxy39.createJobExecution
      1. com.sun.proxy.$Proxy39.createJobExecution(Unknown Source)[na:na]
      1 frame
    10. Spring Batch Core
      SimpleJobLauncher.run
      1. org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124)[spring-batch-core-2.2.1.RELEASE.jar:na]
      1 frame
    11. Java RT
      Method.invoke
      1. sun.reflect.GeneratedMethodAccessor74.invoke(Unknown Source)[na:na]
      2. sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)[na:1.7.0_25]
      3. java.lang.reflect.Method.invoke(Method.java:606)[na:1.7.0_25]
      3 frames
    12. Spring AOP
      ReflectiveMethodInvocation.proceed
      1. org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      2. org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      3. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      3 frames
    13. Spring Batch Core
      SimpleBatchConfiguration$PassthruAdvice.invoke
      1. org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117)[spring-batch-core-2.2.1.RELEASE.jar:na]
      1 frame
    14. Spring AOP
      JdkDynamicAopProxy.invoke
      1. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      2. org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE]
      2 frames
    15. com.sun.proxy
      $Proxy41.run
      1. com.sun.proxy.$Proxy41.run(Unknown Source)[na:na]
      1 frame
    16. com.quikdo.freq
      SchedulerConfig$AugmentFieldsJob.execute
      1. com.quikdo.freq.job.SchedulerConfig$AugmentFieldsJob.execute(SchedulerConfig.java:135)[SchedulerConfig$AugmentFieldsJob.class:na]
      1 frame
    17. quartz
      SimpleThreadPool$WorkerThread.run
      1. org.quartz.core.JobRunShell.run(JobRunShell.java:207)[quartz-2.2.0.jar:na]
      2. org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560)[quartz-2.2.0.jar:na]
      2 frames