Re: Select for update with offset interferes with concurrent transactions

On Tue, Feb 1, 2011 at 11:18 AM, 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.  There are recognized ways to solve
> this problem with more predictability and much less chance of different
> processes blocking each other.  In particular, this query seems be based
> on some untenable assumptions about the physical row order being stable.
>
>> 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 :-(

You can get something approximating the old behavior with a CTE:
with q as (select id from foo where <something> limit x offset y)
select * from foo join q using(id) order by id for update;

not that this is a good idea -- it isn't -- but if you must do it that
way, the above might work. CTE are always a something to consider
when dealing with order of execution problems which seem to be burning
just about everyone these days.

merlin