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
orwhatever 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@mancz.com
--- Msg sent via WebMail@mancz.com - http://mail.mancz.com
