From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: trying to analyze deadlock |
Date: | 2019-04-01 11:20:25 |
Message-ID: | CA+t6e1k_84y2ce4XnCOQFWdESk424NjFnYRST3GeaoP_F9MZXg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
Got it, thanks Laurenz !
בתאריך יום ד׳, 27 במרץ 2019 ב-15:20 מאת Laurenz Albe <
laurenz(dot)albe(at)cybertec(dot)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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Raj Gandhi | 2019-04-01 13:56:05 | Re: LIMIT OFFSET with DB view vs plain SQL |
Previous Message | Mariel Cherkassky | 2019-04-01 10:16:22 | Fwd: trying to analyze deadlock |
From | Date | Subject | |
---|---|---|---|
Next Message | Rafia Sabih | 2019-04-01 13:03:43 | Re: Good afternoon. |
Previous Message | Christoph Berg | 2019-04-01 10:54:35 | Re: Postgresql Sort cost Poor performance? |