inserts on a transaction blocking other inserts

Started by Rachit Siamwallaover 24 years ago2 messages
#1Rachit Siamwalla
rachit@ensim.com

I am having problems with transactions and foreign key constraints in
postgres 7.0-3 (RPM distribution). . The foreign key constraints were
blocking concurrent transactions. Here is an example where something blocked
but shouldn't have blocked:

create table hello10 (myid serial primary key, myvalue int4);

create table hello11(myvalue int4, foreign key (myvalue) references
hello10);

insert into hello10 (myvalue) values (1);

---- ok, now everything is set up for the blocking problem.

Now have two logins to psql:

psql1# begin;
psql1# insert into hello11 (myvalue) values (1)
psql1#

switch to the other login
psql2# begin;
psql2# insert into hello11 (myvalue) values (1)
*** block ***

It shouldn't block there. Basically it happens when two transactions try to
insert something into tables (doesn't have to be the same one) which both
have a foreign key constraint to a common key. I did some poking around and
luckily did find something in the archives that was similar here:

http://fts.postgresql.org/db/mw/msg.html?mid=30149

It was mentioned that it was a problem, and there was a workaround (add
INITIALLY DEFFERED to the constraint). The workaround works. My question is,
is this fixed in Postgres 7.1 (i don't have a spare machine to test, sorry)?

-rchit

#2Jan Wieck
JanWieck@Yahoo.com
In reply to: Rachit Siamwalla (#1)
Re: inserts on a transaction blocking other inserts

Rachit Siamwalla wrote:

[...]

It shouldn't block there. Basically it happens when two transactions try to
insert something into tables (doesn't have to be the same one) which both
have a foreign key constraint to a common key. I did some poking around and
luckily did find something in the archives that was similar here:

The required lock to ensure that the PK doesn't get changed
after the constraint checked for it's existence would be a
shared read lock. Unfortunately, there is no SQL syntax
doing a SELECT that does it. So the only way for now is
doing an exclusive write lock with SELECT FOR UPDATE.

Not fixed in 7.1.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com