Thread: 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
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

James Mancz <james@mancz.com> writes:
> 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.

This is annoying but I do not think it is readily fixable, because the
LIMIT filter acts before the FOR UPDATE processing.  When FOR UPDATE
rejects the first row because it's been changed by the previous updater,
that's all she wrote --- the LIMIT's not gonna produce more rows.

I have some old notes suggesting that maybe FOR UPDATE could be turned
into a plan node that could be stuck underneath the LIMIT node, but
I'm not sure that's workable.  It'd be a nontrivial change certainly,
and arguably unintuitive (the effects of SQL clauses normally act
left-to-right, but you'd be forcing LIMIT to occur after FOR UPDATE).

Those other databases presumably implement LIMIT in a different place
... I bet they don't handle LIMIT in sub-selects though, which is the
advantage to treating it as a plan step.
        regards, tom lane

> > 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.
>
> This is annoying but I do not think it is readily fixable, because the
> LIMIT filter acts before the FOR UPDATE processing.  When FOR UPDATE
> rejects the first row because it's been changed by the previous updater,
> that's all she wrote --- the LIMIT's not gonna produce more rows.

At least there is a workaround for this:
Use a cursor without (or with a high eg 10000) LIMIT and stop fetching after
the 1000 wanted rows :-)

Andreas