Re: Select for update with offset interferes with concurrent transactions

If random sampling is desirable would the following construct limit locking
only to the sampled rows?

SELECT id
FROM tasktable
WHERE id IN (SELECT random_id_sample())
FOR UPDATE

The "random_id_sample" would supply a configurable group of IDs off of
tasktable which the FOR UPDATE would then lock

I guess the issue remains that "random_id_sample()" would still end up
blocking if any of the rows it wants to return are already locked.

I too am using this basic protocol of maintaining state info within the
database and sending every query against it. As I ponder this more it
really seems as if moving some of this logic into the application layer
would possibly make more sense in Yngve's situation (or at least something
to consider). Continue to use the database as a persistence mechanism but
code the "dispatching" of tasks in the application layer and then as each
task is dispatched you simply do an "UPDATE table SET state = 'dispatch'
WHERE id = 'ID'" and a similar UPDATE when the task is returned completed.
This somewhat presumes you still only ever hand off one task at a time. If
you are indeed handing off tasks in batches then it would make sense to have
a "batch" table and operate at the batch level instead of individual tasks -
assigning tasks to a given batch via some standard mechanism.

Either way if you truly want true parallel processing then you need to
create the parallel paths that can operate without clobbering each other and
thus each path needs to have its own pool of tasks since as soon as you have
a shared resource the only true way to make sure it is only allocated once
is to serialize access to it. An alternative method would be to allow
multiple dispatches but have a "write-once" method that is called and sets
an immutable handler_id and then when the processing begins only the handler
with the matching id would be able allow to perform the actual processing.

I say the above with certainty but at the moment I am using and fairly happy
with my limited serialization - especially since I have specific
sub-properties that I can use to limit how many records are locked AND also
because the locking time is very short (I cap around 20 or so active tasks
to dispatch - and only infrequently at that) so my experience and insight to
high-demand situations is limited.

Dave

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, February 01, 2011 12:18 PM
To: Yngve Nysaeter Pettersen
Cc: pgsql-general(at)postgresql(dot)org
Subject: 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