Re: Select for update with offset interferes with concurrent transactions

On Tue, 01 Feb 2011 18:18:17 +0100, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Yngve Nysaeter Pettersen" <yngve(at)opera(dot)com> writes:
>> To avoid having the processes trample each other's queries (the first
>> attempt was to select the first matching entries of the table, which
>> caused one to block all other transactions), one of the steps I took was
>> to select a set of idle rows at a random offset into the table from the
>> project, mark them for update, then update each record's state as
>> started.
>
>> SELECT record_id FROM queue WHERE project_id = my_project AND state =
>> idle LIMIT n OFFSET i FOR UPDATE
>
>> At present "n" is 100-150, "i" is a random value in the range 0-10000.
>
>> There is, intentionally, no ordering specified, since that would just
>> slow
>> down the query, and is not necessary.
>
> This seems like a pretty bad design.

Well, I don't claim to be a database expert ;).

While there might be better ways, the current one have worked OK in the
year since it was implemented.

> There are recognized ways to solve
> this problem with more predictability and much less chance of different

I'd appreciate it if you could provide a couple of pointers.

> processes blocking each other. In particular, this query seems be based
> on some untenable assumptions about the physical row order being stable.

No, it does not assume that the row order is stable; I don't really care
about the order of the elements, since the actual order of task execution
depends much more significantly on other variables, and the actual order
isn't important at all (although further design changes might impose some
limited category grouping on the queue, that would still not make the
ordering important within the group).

>> What I've discovered when using Postgres 9.0 is that the processes are
>> now
>> blocking every other query into this table,
>
> In 9.0, LIMIT/OFFSET processing is done after FOR UPDATE locking, which
> means that rows skipped over by OFFSET still get locked, which means
> that different sessions executing this query are now practically certain
> to block each other, rather than just likely to block each other.
> This was an intentional change to improve the predictability of FOR
> UPDATE's interactions with LIMIT/OFFSET, and indeed it's improved the
> predictability of the behavior for you, just not in the direction you'd
> like :-(

That might be, but is is necessary to continue locking (which is what it
sounds like to me) the elements that are not used in the final response
past completing the query?

What happens now, if I understand it correctly, is that if a "select foo
from bar limit 1 order by whatever offset tablelen-1 for update" is
performed, the effective operation is also "LOCK bar", not just a row lock
on item tablelen-1 in that table. Was that the intention? (and yes, I am
aware that ordering might be used to reverse that sequence so offset 0 can
be used, but wouldn't that just as much block the query for offset 1?)

--
Sincerely,
Yngve N. Pettersen
********************************************************************
Senior Developer Email: yngve(at)opera(dot)com
Opera Software ASA http://www.opera.com/
Phone: +47 23 69 32 60 Fax: +47 23 69 24 01
********************************************************************