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
|
Список | 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 по дате отправления: