Обсуждение: foreign key on delete cascade order?
When using FOREIGN KEY ON DELETE CASCADE, is there an order the entries are being deleted?
We are seeing intermittent deadlocks with trying to update a table with the foreign key entry being deleted.
We have 4 levels of tables chained by foreign keys.
machine -> point -> poll -> status
The status is the only one updated constantly and we are trying to figure out how to order the update to avoid the deadlock when the machine is deleted.
Thanks for your help
George
iGLASS Networks
www.iglass.net
www.iglass.net
George Woodring <george.woodring@iglass.net> writes:
> When using FOREIGN KEY ON DELETE CASCADE, is there an order the entries are
> being deleted?
No, there's no particular attempt to order the deletions. Each cascaded
delete ought to be removing a disjoint set of rows in the referencing
table, so I'm not quite sure why order should matter.
regards, tom lane
On Thu, Mar 10, 2022 at 10:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Each cascaded delete ought to be removing a disjoint set of rows in the referencing
table, so I'm not quite sure why order should matter.
regards, tom lane
I have always thought the way to avoid deadlocks was to update rows in the same order by the different updaters. Is there a better chain of thought for updating and deleting rows at the same time? Do we need to put a lock on the table to update, then have the delete queue up waiting for the lock to be removed?
Thanks,
George
George Woodring <george.woodring@iglass.net> writes:
> I have always thought the way to avoid deadlocks was to update rows in the
> same order by the different updaters. Is there a better chain of thought
> for updating and deleting rows at the same time? Do we need to put a lock
> on the table to update, then have the delete queue up waiting for the lock
> to be removed?
With the amount of detail you've provided (viz: none), it's impossible
for anyone to guess what your problem actually is, let alone speculate
on suitable solutions.
regards, tom lane
On Thu, Mar 10, 2022 at 12:38 PM Tom Lane <tgl@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.
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 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, ... );
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, ...);
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, ...);
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
We are updating the entire status table every 5 minutes withBEGIN;UPDATE status SET () WHERE pollid = $1;COMMIT;The issue is arriving when some does a DELETE during the UPDATE of statusDELETE FROM mach WHERE machid=$1;
Could you set lock_timeout, lock table explicitly for SHARE UPDATE EXCLUSIVE (pretty sure that would be the proper level), then retry if it fails because a delete is already going on?
Also, are you confident that before you call 'begin' to do the update, you are not already in a transaction which might have some lock on row(s) in mach, or one of the other tables involved?
For the update processes, all other tables are read-only reference tables, I don't think they would be locked.
On Fri, Mar 11, 2022 at 10:03 PM Michael Lewis <mlewis@entrata.com> wrote:
Could you set lock_timeout, lock table explicitly for SHARE UPDATE EXCLUSIVE (pretty sure that would be the proper level), then retry if it fails because a delete is already going on?
I don't think I can take a lock on the table, there are multiple process that update the status table. While each process does not overlap, I think locking the table would cause them to block each other.
I think we would just have to retry the delete after the deadlock, which is currently done manually by refreshing the web page. The update never seems to be interupted, probably because it longer running and starts before the random delete.
Also, are you confident that before you call 'begin' to do the update, you are not already in a transaction which might have some lock on row(s) in mach, or one of the other tables involved?
Thank you for your help.
George