Обсуждение: trying to analyze deadlock
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... I tried to do a simulation in my env :
transaction 1 :
delete from a;
<left in the background, no commit yet >
transaction 2 :
delete from b;
but I couldnt recreate the deadlock, I only had some raw exclusive locks :
postgres=# select locktype,relation::regclass,page,tuple,virtualxid,transactionid,virtualtransaction,mode,granted from pg_locks where database=12870;
locktype | relation | page | tuple | virtualxid | transactionid | virtualtransaction | mode | granted
----------+----------+------+-------+------------+---------------+--------------------+------------------+---------
relation | b | | | | | 51/156937 | RowExclusiveLock | t
relation | a_a_idx | | | | | 51/156937 | RowExclusiveLock | t
relation | a | | | | | 51/156937 | RowExclusiveLock | t
relation | pg_locks | | | | | 53/39101 | AccessShareLock | t
relation | a_a_idx | | | | | 52/29801 | AccessShareLock | t
relation | a | | | | | 52/29801 | AccessShareLock | t
relation | b | | | | | 52/29801 | RowExclusiveLock | t
tuple | b | 0 | 1 | | | 51/156937 | ExclusiveLock | t
(8 rows)
What do you guys think ?
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
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
Got it, thanks Laurenz !
בתאריך יום ד׳, 27 במרץ 2019 ב-15:20 מאת Laurenz Albe <laurenz.albe@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