Trx issues: SELECT FOR UPDATE LIMIT

Is the behaviour I describe below expected? I'm thinking that it isn't,
especially when compared with the comparable behaviour from Oracle and Sybase.

I'm running PostgreSQL 7.3.2.

I believe the problem is with transaction behaviour when using SELECT ... FOR
UPDATE with LIMIT.

I'll describe this by way of SQL to reproduce the issue:

Preparation:

CREATE TABLE work
(id int,
alloc int null);

INSERT INTO work VALUES (1, NULL);
INSERT INTO work VALUES (2, NULL);

Basically, the idea is that the work table in reality includes millions of rows
of 'work' for various clients to share. The client will grab a batch of rows,
process them, and then write the rows back to the database.

To grab a batch of rows to process, the client will SELECT a number of them, and
then update the alloc field to the ID of that client, thus marking them as being
worked on/work completed so that other clients don't process the same rows.

So, each client would do

BEGIN;
SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE;
(of course, in reality, the LIMIT value here would be 1000 or whatever and the
work table would include lots of additional data for the client to process)
UPDATE work SET alloc = 99 WHERE id = 1;
COMMIT;

The problem occurs when one or more SELECTs occur while another SELECT is in
progress; use this to reproduce:

So, client 1 sends:

BEGIN;
SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE;

at this time the table values are:

id alloc
----- --------
1 NULL
2 NULL

meanwhile client 2 sends:

BEGIN;
SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE;

This, of course, waits for client 1 to finish.

client 1 sends:

UPDATE work SET alloc = 99 WHERE id = 1;
COMMIT;

at this time the table values are:

id alloc
----- --------
1 99
2 NULL

client 2 can carry on processing now,
and its SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE; **returns no
rows** despite the fact the second row matches.

It _should_ return:

id alloc
----- --------
2 NULL

When similar functionality it tried on Oracle and Sybase (using their "versions"
of LIMIT -- rownum and SET rowcount respectively), they both return the second
row.

Thoughts?

james(at)mancz(dot)com

--- Msg sent via WebMail(at)mancz(dot)com - http://mail.mancz.com