Re: Select for update with offset interferes with concurrent transactions

"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 :-(

regards, tom lane