Re: trying to analyze deadlock

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux