Re: postgresql93-9.3.5: deadlock when updating parent table expected?

On Wed, 18 Feb 2015 04:55:47 +0000
Dmitry O Litvintsev <litvinse@fnal.gov> wrote:

> 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.

Operations on the file table will take out a sharelock on the
corresponding row in the volume table, to ensure the foreign
key isn't made invalid by another process while this transaction
is in progress.

In order for this to deadlock, I believe you would have to have
2 processes operating on the same volume id at the same time.
You're using ambiguous terms like "sometimes" to describe the
frequency. The chance of it happening is a factor of how much
INSERT/DELETE traffic you have on the file table, and how often
those INSERT/DELETEs center around a single volume id.

> Should I drop foreign key constraint ?

If you don't feel that the relational guarantees provided by the
constraint are necessary, then you should delete it. You should
NOT delete the foreign key in an attempt to reduce deadlocks, as
you'd simply be avoiding one relational problem by allowing another
to happen.

Deadlocks are a perfectly normal consequence of high write activity.
It's possible to design schemas and access patterns that can't
deadlock, but it's time-consuming, complex, and generally performs
poorly.

In this case, however, I think you can avoid the deadlock with the
following:

BEGIN;
SELECT id FROM volume WHERE id = $? FOR UPDATE;
-- INSERT or DELETE here
COMMIT;

I don't believe this will create a significant performance
degradation, but you'll have to test it against your workload
to be sure.

A more general solution is to have your application code catch
deadlocks and replay the applicable transaction when they happen.
A deadlock can generally be consider "I can't do that right now,
please try again later" and unless the server is under a
tremendous load, the second attemp usually succeeds (of course,
there is a chance that it will deadlock again, so you have to
take into account that it might take an arbitrary number of
attempts before it succeeds)

I've seen this all too many times: many application developers
assume that a deadlock is an error that should never happen, and
this seems to result from the fact that most application developers
have only worked on applications that are 99% read and only 1%
write, thus they see deadlock scenarios so seldom that they have
no experience with them. Education is really the key here, and
teaching developers that the following pattern is terrible
design:

try {
  // SQL operations here
}
catch (SQLException e) {
  e.printStackTrace();
}

--
Bill Moran