Re: Select for update with offset interferes with concurrent transactions

Hello David,

On Wed, 02 Feb 2011 01:36:15 +0100, David Johnston <polobo(at)yahoo(dot)com>
wrote:

> 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.

My immediate guess is that this would work, and I might explore it once I
get my new fullscale test-db up and running

> 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.

If I read you correctly that is what my system does (dispatch = started,
marked by the node that is to do the task).

The reason I am allocating tasks in batches is that there are so many
processes involved that if they pick one at a time they would block each
other. With the block allocation they only need to fetch the tasks once,
meaning that there are not as many requests to the queue at a time, on
average.

> 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

That is what the offset part of the query was supposed to achieve.

At the moment I have worked around the problem by breaking the task list
into 2000 subgroups, and each process picks one at random. That limits the
number of processes that get in each others way, and the measured speed is
now 4-5 times what I saw on Monday, and back in the old range of
performance. However, it is a hack I had hoped to avoid (and I might get
rid of it with the above suggestion)

> 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.

This requires the handlers to have a unique ID, which my system has not
needed so far.

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

--
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
********************************************************************