With ISOLATION_SERIALIZABLE (default) on PostgreSQL 9.1, launching new job sometimes fails with org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction [BATCH-2084]

[spring-projects-issues]

trafficstars

Hendy Irawan opened BATCH-2084 and commented

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:

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

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.]]

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" :

/**
 * 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();
	}

}

---

Affects: 2.2.1

3 votes, 5 watchers

[spring-projects-issues]

Michael Minella commented

Are you using two different transaction managers (one on each data source)?

[spring-projects-issues]

Hendy Irawan commented

Single DataSourceTransactionManager and single reused c3p0 DataSource.

[cppwfs]

Thank you for opening the issue. Can you retry with the latest release of Spring Batch(5.0.2) as well as the currently supported Postgresql and report back the results?
If you are still seeing the issue, can you provide a sample project that uses the latest release of Spring Batch and that exhibits the behavior? To help you in reporting your issue, we have prepared a project template that you can use as a starting point. Please check the Issue Reporting Guidelines for more details about this.