Re: trying to analyze deadlock

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

Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com

On Mon, Apr 1, 2019 at 3:46 PM Mariel Cherkassky <
mariel(dot)cherkassky(at)gmail(dot)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 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 ?
>
>