On Thu, Mar 10, 2022 at 12:38 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> With the amount of detail you've provided (viz: none) > This is an example of the error we are seeing from our application. Sorry, I cannot find the postgresql log entry for this one. 2020-11-30T13:16:08,835 ERROR [foo/bar/01EF2.W01E/55159] GlobalControllerAdvice: Caught exception ( https://noc.iglass.net/networkMachDelete.htm <https://noc.iglass.net/jglass/admin/networkMachDelete.htm>): org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [DELETE FROM mach WHERE (machid=?)]; ERROR: deadlock detected Detail: Process 27442 waits for ShareLock on transaction 1895244982; blocked by process 21064. Process 21064 waits for ShareLock on transaction 1895245026; blocked by process 27442. Hint: See server log for query details. Where: while deleting tuple (5,18) in relation "status" SQL statement "DELETE FROM ONLY "public"."status" WHERE $1 OPERATOR(pg_catalog.=) "pollid""; nested exception is org.postgresql.util.PSQLException: ERROR: deadlock detected Detail: Process 27442 waits for ShareLock on transaction 1895244982; blocked by process 21064. Process 21064 waits for ShareLock on transaction 1895245026; blocked by process 27442. Hint: See server log for query details. Where: while deleting tuple (5,18) in relation "status" SQL statement "DELETE FROM ONLY "public"."status" WHERE $1 OPERATOR(pg_catalog.=) "pollid"" org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [DELETE FROM mach WHERE (machid=?)]; ERROR: deadlock detected Detail: Process 27442 waits for ShareLock on transaction 1895244982; blocked by process 21064. Process 21064 waits for ShareLock on transaction 1895245026; blocked by process 27442. Hint: See server log for query details. Where: while deleting tuple (5,18) in relation "status" SQL statement "DELETE FROM ONLY "public"."status" WHERE $1 OPERATOR(pg_catalog.=) "pollid""; nested exception is org.postgresql.util.PSQLException: ERROR: deadlock detected Detail: Process 27442 waits for ShareLock on transaction 1895244982; blocked by process 21064. Process 21064 waits for ShareLock on transaction 1895245026; blocked by process 27442. Hint: See server log for query details. Where: while deleting tuple (5,18) in relation "status" SQL statement "DELETE FROM ONLY "public"."status" WHERE $1 OPERATOR(pg_catalog.=) "pollid"" The tables are involved are CREATE TABLE mach ( machid serial, constraint mach_pkey primary key (machid) ... ); CREATE TABLE pollgrpinfo ( pollgrpid serial, constraint pollgrpinfo_pkey primary key (pollgrpid), machid int4 NOT NULL, constraint mach_exists FOREIGN KEY(machid) REFERENCES mach ON DELETE CASCADE, ... ); CREATE TABLE poll ( pollid serial, constraint poll_pkey primary key (pollid), pollgrpid int4 not null, constraint pollgrp_exists FOREIGN KEY(pollgrpid) REFERENCES pollgrpinfo (pollgrpid) ON DELETE CASCADE, ...); CREATE TABLE status ( statusid serial, constraint status_pkey primary key (statusid), pollid int4 not null, constraint poll_exists FOREIGN KEY(pollid) REFERENCES poll ON DELETE CASCADE, ...); We are updating the entire status table every 5 minutes with BEGIN; UPDATE status SET () WHERE pollid = $1; COMMIT; The issue is arriving when some does a DELETE during the UPDATE of status DELETE FROM mach WHERE machid=$1; I don't know if this sheds any more light on it. George iGLASS Networks
