trying to analyze deadlock

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: trying to analyze deadlock
Date: 2019-03-27 12:52:28
Message-ID: CA+t6e1n_o_0=3NQXM_b4vPSQjv+1p6n_1q-nqH6XkVrormXYfg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-performance

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 ?

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Shreeyansh Dba 2019-03-27 13:16:13 Re: pgsql database size
Previous Message Pepe TD Vo 2019-03-27 12:36:04 Re: pgsql database size

Browse pgsql-performance by date

  From Date Subject
Next Message Laurenz Albe 2019-03-27 13:17:49 Re: trying to analyze deadlock
Previous Message Justin Pryzby 2019-03-22 20:07:19 Re: EXPLAIN PLAN for DELETE CASCADE or DELETE not using pkey index despite EXPLAINing that it would?