bug with select for update

From: Barry Lind <barry(at)xythos(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: bug with select for update
Date: 2002-02-11 06:39:29
Message-ID: 3C676721.30704@xythos.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have just isolated a bug in doing a 'select for update'. I have
included a test case below that reproduces the problem on a 7.2
database. The bug is as follows: after issuing a select for update that
blocks on a record being locked by a second process, when that second
process commits, the first process begins using 100% of the cpu and
never completes.

The test case is as follows:

drop table test1;
drop table test2;

create table test1 (cola int);
create unique index test1_u1 on test1(cola);
create table test2 (cola int);
create unique index test2_u1 on test2(cola);
insert into test1 values (1);
insert into test2 values (1);

Then in one psql session issue the following:

begin;
UPDATE TEST1 SET COLA = 1
WHERE COLA = 1;

Then in a second psql issue the following:

begin;
SELECT T1.COLA
FROM TEST1 T1, TEST2 T2
WHERE T1.COLA = T2.COLA
AND T2.COLA = 1 FOR UPDATE;

Now go back to the first psql and issue the following:

commit;

Now you will see the cpu usage spike up to 100% for the second process
and it will never return.

Running analyze on these newly created tables will cause the indexes not
to be used and will result in the select working correctly. However in
my real code the indexes are necessary and thus that isn't a workaround
for me.

thanks,
--Barry

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Philip Warner 2002-02-11 07:47:59 Re: Idea for making COPY data Microsoft-proof
Previous Message Brent Verner 2002-02-11 03:49:23 Re: RTLD_LAZY considered harmful (Re: pltlc and pltlcu