SELECT FOR UPDATE and LIMIT 1 behave oddly

Поиск
Список
Период
Сортировка
От Josh Berkus
Тема SELECT FOR UPDATE and LIMIT 1 behave oddly
Дата
Msg-id 200410131805.43594.josh@agliodbs.com
обсуждение исходный текст
Ответы Re: SELECT FOR UPDATE and LIMIT 1 behave oddly  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Guys,

Summary:  SELECT FOR UPDATE and LIMIT behave oddly when combined
Affects: 7.4.3 (not tested yet on other versions)
Severity:  Annoyance
Description:
If you attempt to lock a row "off the top" of a table by using SELECT ... FOR
UPDATE LIMIT 1, any blocked transaction will have no rows returned when the
lock ends.   This is counter-intuitive and wierd.   It is easily worked
around, though, since the LIMIT 1 is really superfluous; possibly we don't
want to fix it, just put a warning in the docs.

Test Case:
primer=# create table some_que ( sequence int, done boolean );
CREATE TABLE
primer=# insert into some_que values ( 1, false );
primer=# insert into some_que values ( 2, false );
primer=# insert into some_que values ( 3, false );
primer=# insert into some_que values ( 4, false );
primer=# insert into some_que values ( 5, false );
primer=# insert into some_que values ( 6, false );

TRANSACTION A:
primer=# begin;
BEGIN
primer=# select * from some_que where done = false order by sequence limit 1
for update;
 sequence | done
----------+------
        1 | f

TRANSACTION B:
primer=# begin;
BEGIN
primer=# select * from some_que where done = false order by sequence limit 1
for update;

TRANSACTION A:
primer=# update some_que set done = true where sequence = 1;
UPDATE 1
primer=# commit;
COMMIT

TRANSACTION B:
 sequence | done
----------+------
(0 rows)

... as you can see, it falsely reports no rows.

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

Предыдущее
От: Gaetano Mendola
Дата:
Сообщение: Re: overloaded functions and NULL
Следующее
От: Tom Lane
Дата:
Сообщение: Re: SELECT FOR UPDATE and LIMIT 1 behave oddly