postgresql93-9.3.5: deadlock when updating parent table expected?

Поиск
Список
Период
Сортировка
От Dmitry O Litvintsev
Тема postgresql93-9.3.5: deadlock when updating parent table expected?
Дата
Msg-id C15F9B88FF75254CACAE9A261BE9CD7A03EEF92A@MAIL02.fnal.gov
обсуждение исходный текст
Ответы Re: postgresql93-9.3.5: deadlock when updating parent table expected?  (Bill Moran <wmoran@potentialtech.com>)
Re: postgresql93-9.3.5: deadlock when updating parent table expected?  (Alvaro Herrera <alvherre@2ndquadrant.com>)
Список pgsql-general
Hi,

I recently updated to postgresql93-9.3.5 (from 9.2.9). I see frequent deadlocks
when updating parent table in insert into child table. There is foreign key constraint between
child table and parent table. Parent table is updated on by trigger in insert into child table. So
pretty much standard thing. Is it expected to deadlock?

A simplified version:

 create table volume (
   id serial primary key,
   name varchar,
   counter integer default(0));

 create table file (
   id serial primary key,
   name varchar,
   volume bigint, foreign key (volume) references volume(id));

 create or replace function update_volume_file_counter()
 returns "trigger" as $$
 begin
 if (tg_op='INSERT') then
  update volume set counter=counter+1 where volume.id=new.volume;
  return new;
 elseif (tg_op='DELETE') then
  update volume set counter=counter-1 where volume.id=old.volume;
  return old;
 end if;
 end;
 $$
 language plpgsql;

 create trigger update_volume_counter
    after insert or delete on file
    for each row
    execute procedure update_volume_file_counter();

So record is inserted into file table and counter gets updated in volume table. Nothing
fancy.

insert into volume (name) values ('foo');
insert into file(name,volume) values ('f1',(select id from volume where name='foo'));
insert into file(name,volume) values ('f2',(select id from volume where name='foo'));

select * from volume;
 id | name | counter
----+------+---------
  2 | foo  |       2
(1 row)

delete from file where name='f2';
DELETE 1
billing=# select * from volume;
 id | name | counter
----+------+---------
  2 | foo  |       1
(1 row)

So, counter increments/decrements as it should.
Works fine.
But in real life application where multiple threads are inserting into file
table I see sometimes:

CSTERROR:  deadlock detected
 Process 24611 waits for ExclusiveLock on tuple (1749,58) of relation 138328329 of database 138328263; blocked by
process25082. 
 Process 25082 waits for ShareLock on transaction 14829630; blocked by process 24611.
 Process 24611:             update volume set counter=counter+1 where id=new.volume;
 Process 25082:             insert into file(name,volume) values('f10000',(select id from volume where name='foo'));
  CSTHINT:  See server log for query details.

(not a "real" log file excerpt).

This does not happen all the time, happens sometimes when multiple threads "add" file to the same volume;.

Question - am I doing something wrong or this deadlock is expected? ( I read somewhere
that when inserting into  child table the corresponding record of parent table is locked).
I did not seem to encounter this issue in postgresql 9.2 and 8.4 which I had before.

Should I drop foreign key constraint ?

Thanks,
Dmitry

В списке pgsql-general по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: which is better- storing data as array or json?
Следующее
От: Brian Sutherland
Дата:
Сообщение: Failure loading materialized view with pg_restore