Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h98T9-0003FK-Oh
	for pgsql-performance@arkaria.postgresql.org; Wed, 27 Mar 2019 13:19:51 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h98T8-0007dB-6u
	for pgsql-performance@arkaria.postgresql.org; Wed, 27 Mar 2019 13:19:50 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <laurenz.albe@cybertec.at>)
	id 1h98T7-0007Y8-R5
	for pgsql-performance@lists.postgresql.org; Wed, 27 Mar 2019 13:19:49 +0000
Received: from mx1.mailbox.org ([80.241.60.212])
	by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <laurenz.albe@cybertec.at>)
	id 1h98T4-0000cR-Nw
	for pgsql-performance@lists.postgresql.org; Wed, 27 Mar 2019 13:19:48 +0000
Received: from smtp1.mailbox.org (smtp1.mailbox.org [80.241.60.240])
	(using TLSv1.2 with cipher ECDHE-RSA-CHACHA20-POLY1305 (256/256 bits))
	(No client certificate requested)
	by mx1.mailbox.org (Postfix) with ESMTPS id 91DBA4D9BD;
	Wed, 27 Mar 2019 14:19:28 +0100 (CET)
X-Virus-Scanned: amavisd-new at heinlein-support.de
Received: from smtp1.mailbox.org ([80.241.60.240])
	by spamfilter01.heinlein-hosting.de (spamfilter01.heinlein-hosting.de
 [80.241.56.115]) (amavisd-new, port 10030)
	with ESMTP id niu2F7B3JN2A; Wed, 27 Mar 2019 14:19:22 +0100 (CET)
Message-ID: <bd6a3425f981311daa322cd8f580d6071a6824c2.camel@cybertec.at>
Subject: Re: trying to analyze deadlock
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>,
	pgsql-performance@lists.postgresql.org
Date: Wed, 27 Mar 2019 14:17:49 +0100
In-Reply-To: 
 <CA+t6e1n_o_0=3NQXM_b4vPSQjv+1p6n_1q-nqH6XkVrormXYfg@mail.gmail.com>
References: 
	<CA+t6e1n_o_0=3NQXM_b4vPSQjv+1p6n_1q-nqH6XkVrormXYfg@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

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