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 ?
