Re: select ... for update limit 1

At 12:51 AM 19-02-2001 -0500, Tom Lane wrote:
>Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
>> Right now select for update limit 1 seems to lock all rows in the where
>> clause, not just retrieved rows.
>
>This claim surprises me quite a lot, since the FOR UPDATE locking occurs
>at the outer level of the executor where rows are about to be returned.
>And I can't duplicate your result in a simple test. Could you post a
>reproducible example?

You're right, I'm wrong. Sorry.

It doesn't lock all the rows, but it blocks other similar selects. That's
cool. I may try order by random :). Wondering if there's a way to select
and update at the same time. Maybe I should try a different approach.

**Version:
select version();
version
---------------------------------------------------------------------
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)

**Method
create table sch_todo_q (taskid int, pid int, posted timestamp, status text);
insert into sch_todo_q (taskid,pid,posted,status) values (1,0,now(),'A');
insert into sch_todo_q (taskid,pid,posted,status) values (2,0,now(),'A');
insert into sch_todo_q (taskid,pid,posted,status) values (3,0,now(),'A');
insert into sch_todo_q (taskid,pid,posted,status) values (4,0,now(),'A');
insert into sch_todo_q (taskid,pid,posted,status) values (5,0,now(),'A');

**psql connection 1
begin;
select * from sch_todo_q where pid=0 for update limit 1;
taskid | pid | posted | status

--------+-----+------------------------+--------
1 | 0 | 2001-02-19 14:21:20+08 | A
(1 row)

**psql connection 2
begin;
select * from sch_todo_q where pid=0 for update limit 1;
(blocks)

**psql connection 3
begin;
select * from sch_todo_q where pid=0 order by taskid desc for update limit 1;
taskid | pid | posted | status

--------+-----+------------------------+--------
5 | 0 | 2001-02-19 14:21:20+08 | A
(1 row)

Have fun,
Link.