spring-projects-issues
commented
10 years ago Michael Minella commented
Are you using two different transaction managers (one on each data source)?
Open spring-projects-issues opened 11 years ago
spring-projects-issues
commented
10 years ago Michael Minella commented
Are you using two different transaction managers (one on each data source)?
spring-projects-issues
commented
10 years ago Hendy Irawan commented
Single DataSourceTransactionManager and single reused c3p0 DataSource.
cppwfs
commented
1 year ago 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.
dlehammer
commented
1 day ago Hi @cppwfs ,
I can confirm the described symptom ERROR: could not serialize access due to read/write dependencies among transactions still occurs.
I just encountered it during upgrade from Spring Batch 4.3.10 -> 5.0.6, with a postgresql:14.4.
Stacktrace:
org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
Detail: Reason code: Canceled on identification as a pivot, during commit attempt.
Hint: The transaction might succeed if retried.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) ~[postgresql-42.6.2.jar!/:42.6.2]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) ~[postgresql-42.6.2.jar!/:42.6.2]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) ~[postgresql-42.6.2.jar!/:42.6.2]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:327) ~[postgresql-42.6.2.jar!/:42.6.2]
at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:965) ~[postgresql-42.6.2.jar!/:42.6.2]
at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:987) ~[postgresql-42.6.2.jar!/:42.6.2]
at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:361) ~[HikariCP-3.2.0.jar!/:na]
at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java) ~[HikariCP-3.2.0.jar!/:na]
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:86) ~[hibernate-core-6.2.4.Final.jar!/:6.2.4.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:268) ~[hibernate-core-6.2.4.Final.jar!/:6.2.4.Final]
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101) ~[hibernate-core-6.2.4.Final.jar!/:6.2.4.Final]
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:561) ~[spring-orm-6.0.21.jar!/:6.0.21]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:744) ~[spring-tx-6.0.21.jar!/:6.0.21]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:712) ~[spring-tx-6.0.21.jar!/:6.0.21]
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:660) ~[spring-tx-6.0.21.jar!/:6.0.21]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:410) ~[spring-tx-6.0.21.jar!/:6.0.21]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.0.21.jar!/:6.0.21]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.21.jar!/:6.0.21]
at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean.lambda$getObject$0(AbstractJobRepositoryFactoryBean.java:204) ~[spring-batch-core-5.0.6.jar!/:5.0.6]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.21.jar!/:6.0.21]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244) ~[spring-aop-6.0.21.jar!/:6.0.21]
at jdk.proxy2/jdk.proxy2.$Proxy168.createJobExecution(Unknown Source) ~[na:na]
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:145) ~[spring-batch-core-5.0.6.jar!/:5.0.6]
at org.springframework.batch.core.launch.support.TaskExecutorJobLauncher.run(TaskExecutorJobLauncher.java:59) ~[spring-batch-core-5.0.6.jar!/:5.0.6]
at org.springframework.batch.integration.launch.JobLaunchingMessageHandler.launch(JobLaunchingMessageHandler.java:53) ~[spring-batch-integration-5.0.6.jar!/:5.0.6]
at org.springframework.batch.integration.launch.JobLaunchingGateway.handleRequestMessage(JobLaunchingGateway.java:71) ~[spring-batch-integration-5.0.6.jar!/:5.0.6]
at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:136) ~[spring-integration-core-6.1.9.jar!/:6.1.9]
at org.springframework.integration.handler.AbstractMessageHandler.doHandleMessage(AbstractMessageHandler.java:105) ~[spring-integration-core-6.1.9.jar!/:6.1.9]
at org.springframework.integration.handler.AbstractMessageHandler.handleWithMetrics(AbstractMessageHandler.java:90) ~[spring-integration-core-6.1.9.jar!/:6.1.9]
at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:70) ~[spring-integration-core-6.1.9.jar!/:6.1.9]
SNIP..Workaround:
Override default ISOLATION_SERIALIZABLE with ISOLATION_READ_COMMITTED as described in the documentation.
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:
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" :
Affects: 2.2.1
3 votes, 5 watchers