Thread: Fwd: 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 ?
Hi Mariel,
Commands in the same transaction will see the effects of the committed concurrent transaction in any case.
Go through below link hope this will help you.
https://severalnines.com/blog/understanding-deadlocks-mysql-postgresql
On Mon, Apr 1, 2019 at 3:46 PM Mariel Cherkassky <mariel.cherkassky@gmail.com> 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 ms06: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 | trelation | a_a_idx | | | | | 51/156937 | RowExclusiveLock | trelation | a | | | | | 51/156937 | RowExclusiveLock | trelation | pg_locks | | | | | 53/39101 | AccessShareLock | trelation | a_a_idx | | | | | 52/29801 | AccessShareLock | trelation | a | | | | | 52/29801 | AccessShareLock | trelation | b | | | | | 52/29801 | RowExclusiveLock | ttuple | b | 0 | 1 | | | 51/156937 | ExclusiveLock | t(8 rows)What do you guys think ?