I'm using postgres. I write code in java. Made a thread pool which periodically deletes rows from the database (deleteJobPeriodMs = 10 seconds):
long DELETEJOBPERIOD_MS_DEFAULT = 10000;
long deleteJobPeriodMs = userVariableDeleteJobPeriod.haveVariable()? deleteJobPeriodMsVar.getVariable(): DELETEJOBPERIOD_MS_DEFAULT;
executorService = new ScheduledThreadPoolExecutor(coreThreadsCount, this::createDeleteJobThread);
executorService.scheduleAtFixedRate(this::deleteMethod, 0,
deleteJobPeriodMs, TimeUnit.MILLISECONDS);
The deleteMethod uses the following request: DELETE FROM field WHERE delete_date < ?
At the same time, new entries are constantly being added to the program that require further deletion. No other operations are performed. Deadlock occurs about 5-10 times per hour of operation. Here's what I get in the logs:
2021-05-22 00:16:01 MSK [117890]: [1-1] ERROR: deadlock detected
2021-05-22 00:16:01 MSK [117890]: [2-1] DETAIL: Process 117890 waits for
AccessExclusiveLock on tuple (17724,12) of relation 471906 of database 471895;
blocked by process 116805.
Process 116805 waits for ShareLock on transaction 1923461368; blocked by process 115793.
Process 115793 waits for ShareLock on transaction 1923460316; blocked by process 109269.
Process 109269 waits for ShareLock on transaction 1923457105; blocked by process 117890.
Process 117890: DELETE FROM field WHERE delete_date < $1
Process 116805: DELETE FROM field WHERE delete_date < $1
Process 115793: DELETE FROM field WHERE delete_date < $1
Process 109269: DELETE FROM field WHERE delete_date < $1
2021-05-22 00:16:01 MSK [117890]: [3-1] HINT: See server log for query details.
2021-05-22 00:16:01 MSK [117890]: [4-1] STATEMENT: DELETE FROM jobs WHERE delete_date < $1
Part of one of the stacktrace:
Exception: operation: DeleteService.onDeleteFields, cause:
org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 127116 waits for ShareLock on transaction 1610026614; blocked by process 6433.
Process 6433 waits for ShareLock on transaction 1610014636; blocked by process 5957.
Process 5957 waits for AccessExclusiveLock on tuple (3,5) of relation 471906 of database 471895; blocked by process 127116.
Hint: See server log for query details.
Where: while deleting tuple (3,5) in relation "field"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
at jdk.internal.reflect.GeneratedMethodAccessor38.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:428)
at com.sun.proxy.$Proxy7.executeUpdate(Unknown Source)
it is important to note that several of these applications are deployed on the same database
Changed the request from DELETE FROM field WHERE delete_date < ? to DELETE FROM JOBS WHERE fid IN (SELECT fid FROM JOBS WHERE delete_date < ? FOR UPDATE SKIP LOCKED). At the moment, the error does not occur.