View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004643 | SymmetricDS | Bug | public | 2020-11-19 12:08 | 2025-04-14 14:00 |
| Reporter | pyotrk | Assigned To | pmarzullo | ||
| Priority | high | ||||
| Status | resolved | Resolution | fixed | ||
| Product Version | 3.12.5 | ||||
| Target Version | 3.15.16 | Fixed in Version | 3.15.16 | ||
| Summary | 0004643: Initial load fails with DB concurrent error | ||||
| Description | Initial load fails with the next error: 2020-11-19 14:46:44,890 ERROR [Primary-EED0D853-1771-2C49-B8CF-B25FE290ED67] [InitialLoadService] [Primary-EED0D853-1771-2C49-B8CF-B25FE290ED67-job-16] Error while queuing initial loads StackTraceKey.init [SqlException:985884203] org.jumpmind.db.sql.SqlException: ERROR: could not serialize access due to concurrent update at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:306) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:295) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:398) at org.jumpmind.db.sql.JdbcSqlTransaction.prepareAndExecute(JdbcSqlTransaction.java:364) at org.jumpmind.symmetric.service.impl.DataService.insertReloadEvents(DataService.java:1046) at org.jumpmind.symmetric.service.impl.InitialLoadService.processTableRequestLoads(InitialLoadService.java:282) at org.jumpmind.symmetric.service.impl.InitialLoadService.queueLoads(InitialLoadService.java:98) at org.jumpmind.symmetric.job.InitialLoadJob.doJob(InitialLoadJob.java:43) at org.jumpmind.symmetric.job.AbstractJob.invoke(AbstractJob.java:227) at org.jumpmind.symmetric.job.AbstractJob.run(AbstractJob.java:298) at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:522) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:319) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:191) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1160) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.lang.Thread.run(Thread.java:818) Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153) at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) at org.jumpmind.db.sql.JdbcSqlTransaction$6.execute(JdbcSqlTransaction.java:374) at org.jumpmind.db.sql.JdbcSqlTransaction$6.execute(JdbcSqlTransaction.java:364) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:396) ... 15 more The actual place where it fails is (DataService.java, 1046 line): transaction.prepareAndExecute(getSql("updateTableReloadStatusFinalizeCount"), finalizeBatchCount, new Date(), loadId); I checked the isolation level for Postgres JDBC transactions and it was TRANSACTION_READ_COMMITTED = 2. I also noticed that when setting the parameter: datareload.batch.insert.transactional from TRUE to FALSE initial load finishes. | ||||
| Steps To Reproduce | DB: Postgres 10.5 2 nodes. Fully default symmetric configuration. | ||||
| Tags | dialect: postgresql, initial/partial load | ||||
|
|
Any suggestion on this? Can it be the issue in configuration? |
|
|
Additional info: default_transaction_isolation='repeatable read' in postgresql.conf. It seems that symmetric uses the default database isolation level (REPEATABLE_READ) and not the one that defined in symmetric (READ_COMMITTED when it executes the mentioned in the description query. |
|
|
This is definitely related to the thing that we twice update the same row in concurrent transactions before execue affected query: if (reloadRequests != null && reloadRequests.size() > 0) { updateTableReloadStatusTableCount(platform.supportsMultiThreadedTransactions() ? null : transaction, loadId, totalTableCount + fileSyncBatches); } |
|
|
Seems similar to StackOverflow issue: https://stackoverflow.com/questions/50797097/postgres-could-not-serialize-access-due-to-concurrent-update |
|
|
Merged PR https://github.com/JumpMind/symmetric-ds/pull/246 |
|
SymmetricDS: 3.15 2a7f96e8 2025-04-14 12:42:30 philipmarzullo64 Committer: GitHub Details Diff |
0004643: Update table_reload_status with different transaction (0000246) 0004643: Update table_reload_status within a separate transaction |
Affected Issues 0004643 |
|
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataService.java | Diff File | ||
|
SymmetricDS: 3.16 dcd29eef 2025-04-14 12:42:30 philipmarzullo64 Committer: pmarzullo Details Diff |
0004643: Update table_reload_status with different transaction (0000246) 0004643: Update table_reload_status within a separate transaction |
Affected Issues 0004643 |
|
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataService.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2020-11-19 12:08 | pyotrk | New Issue | |
| 2020-11-25 09:13 | pyotrk | Note Added: 0001860 | |
| 2020-12-03 08:37 | pyotrk | Note Added: 0001867 | |
| 2020-12-03 09:41 | pyotrk | Note Added: 0001868 | |
| 2025-04-09 17:50 | pbelov | Note Added: 0002797 | |
| 2025-04-09 17:51 | pbelov | Tag Attached: dialect: postgresql | |
| 2025-04-10 13:17 | pmarzullo | Relationship added | related to 0006814 |
| 2025-04-10 13:18 | pmarzullo | Tag Attached: initial/partial load | |
| 2025-04-10 13:18 | pmarzullo | Assigned To | => pmarzullo |
| 2025-04-10 13:18 | pmarzullo | Status | new => assigned |
| 2025-04-10 13:18 | pmarzullo | Target Version | => 3.15.16 |
| 2025-04-14 12:43 | pbelov | Status | assigned => resolved |
| 2025-04-14 12:43 | pbelov | Resolution | open => fixed |
| 2025-04-14 12:43 | pbelov | Fixed in Version | => 3.15.16 |
| 2025-04-14 12:43 | pbelov | Note Added: 0002800 | |
| 2025-04-14 13:00 | Changeset attached | => SymmetricDS 3.15 2a7f96e8 | |
| 2025-04-14 14:00 | pmarzullo | Changeset attached | => SymmetricDS 3.16 dcd29eef |