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@xxxxxxxxxxxxx] Sent: Tuesday, February 01, 2011 12:18 PM To: Yngve Nysaeter Pettersen Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Select for update with offset interferes with concurrent transactions "Yngve Nysaeter Pettersen" <yngve@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
