bug with select for update

Поиск
Список
Период
Сортировка
От Barry Lind
Тема bug with select for update
Дата
Msg-id 3C676721.30704@xythos.com
обсуждение исходный текст
Ответы Re: bug with select for update  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Brent Verner
Дата:
Сообщение: Re: RTLD_LAZY considered harmful (Re: pltlc and pltlcu
Следующее
От: Philip Warner
Дата:
Сообщение: Re: Idea for making COPY data Microsoft-proof