Got it, thanks Laurenz ! בתאריך יום ד׳, 27 במרץ 2019 ב-15:20 מאת Laurenz Albe < laurenz.a...@cybertec.at>:
> Mariel Cherkassky wrote: > > Hi all, > > I'm trying to analyze a deadlock that I have in one of our environments. > > The deadlock message : > > > > 06:15:49 EET db 14563 DETAIL: Process 14563 waits for ShareLock on > transaction 1017405468; blocked by process 36589. > > Process 36589 waits for ShareLock on transaction 1017403840; > blocked by process 14563. > > Process 14563: delete from tableB where a in (select id from > tableA where c in (....) > > Process 36589: delete from tableA where c in (....) > > 06:15:49 EET db 14563 HINT: See server log for query details. > > 06:15:49 EET db 14563 STATEMENT: delete from tableB where a in (select > id from tableA where c in (....) > > 06:15:49 EET db 36589 LOG: process 36589 acquired ShareLock on > transaction 1017403840 after 1110158.778 ms > > 06:15:49 EET db 36589 STATEMENT: delete from tableA where c in (....) > > 06:15:49 EET db 36589 LOG: duration: 1110299.539 ms execute > <unnamed>: delete from tableA where c in (...) > > > > tableA : (id int, c int references c(id)) > > tableB : (id int, a int references a(id) on delete cascade) > > tableC(id int...) > > > > One A can have Many B`s connected to (One A to Many B). > > > > deadlock_timeout is set to 5s. > > > > Now I'm trying to understand what might cause this deadlock. I think > that its related to the foreign keys... > > You can get that if the foreign key is defined as ON CASCADE DELETE or ON > CASCADE SET NULL: > > CREATE TABLE a (a_id integer PRIMARY KEY); > > INSERT INTO a VALUES (1), (2); > > CREATE TABLE b (b_id integer PRIMARY KEY, a_id integer NOT NULL > REFERENCES a ON DELETE CASCADE); > > INSERT INTO b VALUES (100, 1), (101, 1), (102, 2), (103, 2); > > Transaction 1: > > BEGIN; > > DELETE FROM b WHERE b_id = 100; > > Transaction 2: > > BEGIN; > > DELETE FROM a WHERE a_id = 2; > > DELETE FROM a WHERE a_id = 1; -- hangs > > Transaction 1: > > DELETE FROM b WHERE b_id = 102; > > ERROR: deadlock detected > DETAIL: Process 10517 waits for ShareLock on transaction 77325; blocked > by process 10541. > Process 10541 waits for ShareLock on transaction 77323; blocked by process > 10517. > HINT: See server log for query details. > CONTEXT: while deleting tuple (0,3) in relation "b" > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
