Search Postgresql Archives

Re: Foreign Keys and Deadlocks

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

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux