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@xxxxxxxxxxxxxx/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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
