Re: Foreign Keys and Deadlocks

On Thu, Nov 03, 2011 at 03:30:20PM -0700, David Kerr wrote:
- Howdy,
-
- We have a process that's deadlocking frequently. It's basically multiple threads inserting data into a single table.
-
- That table has FK constraints to 3 other tables.
-
- I understand how an FK check will cause a sharelock to be acquired on the reference table and in some instances that
- leads to or at least participates in a deadlock.
-
- I don't think that's the case here, (or at least not the entire case) but I could use some assistance in helping
- to convince my developers of that ;). They'd like to just remove the FK and be done with it.

[snip]

So it appears that I'm the big dummy, and that you can deadlock with just inserts.

I did more digging and found some good discussions on the subject in general, but
most of the examples out there contain explicit updates (which is why i was confused)
but it looks like it's being addressed.

http://justatheory.com/computers/databases/postgresql/fk-locks-project.html
http://www.mail-archive.com/pgsql-hackers(at)postgresql(dot)org/msg158205.html
http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/

Attached is the script to reproduce it with only inserts (for postarities sake)

drop table a;
drop table b;
drop table c;
drop table d;

create table b ( bref int, description text);
alter table b add primary key (bref);

create table c ( cref int, description text);
alter table c add primary key (cref);

create table d ( dref int, description text);
alter table d add primary key (dref);

create table a ( bref int, cref int, dref int, description text);
alter table a add primary key (bref, cref);
alter table a add foreign key (bref) REFERENCES b(bref);
alter table a add foreign key (cref) REFERENCES c(cref);
alter table a add foreign key (dref) REFERENCES d(dref);

insert into b values (1,'hello');
insert into b values (2,'hello2');
insert into b values (3,'hello3');
insert into b values (4,'hello4');

insert into c values (1,'hello');
insert into c values (2,'hello2');
insert into c values (3,'hello3');
insert into c values (4,'hello4');

insert into d values (1,'hello');
insert into d values (2,'hello2');
insert into d values (3,'hello3');
insert into d values (4,'hello4');

Fire up 2 psqls
#SESSION1
## STEP1
begin;
insert into a values (1,1,1,'hello');
##STEP3
insert into a values (1,2,1,'hello2');

#SESSION2
## STEP2
begin;
insert into a values (1,2,1,'hello2');
## STEP4
insert into a values (1,1,1,'hello');

You'll get:
ERROR: deadlock detected
DETAIL: Process 8382 waits for ShareLock on transaction 7222455; blocked by process 6981.
Process 6981 waits for ShareLock on transaction 7222456; blocked by process 8382.
HINT: See server log for query details