At 12:51 AM 19-02-2001 -0500, Tom Lane wrote:
>Lincoln Yeoh <lyeoh@pop.jaring.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.