On Tue, 01 Feb 2011 18:18:17 +0100, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"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.
Well, I don't claim to be a database expert ;).
While there might be better ways, the current one have worked OK in the
year since it was implemented.
There are recognized ways to solve
this problem with more predictability and much less chance of different
I'd appreciate it if you could provide a couple of pointers.
processes blocking each other. In particular, this query seems be based
on some untenable assumptions about the physical row order being stable.
No, it does not assume that the row order is stable; I don't really care
about the order of the elements, since the actual order of task execution
depends much more significantly on other variables, and the actual order
isn't important at all (although further design changes might impose some
limited category grouping on the queue, that would still not make the
ordering important within the group).
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 :-(
That might be, but is is necessary to continue locking (which is what it
sounds like to me) the elements that are not used in the final response
past completing the query?
What happens now, if I understand it correctly, is that if a "select foo
from bar limit 1 order by whatever offset tablelen-1 for update" is
performed, the effective operation is also "LOCK bar", not just a row lock
on item tablelen-1 in that table. Was that the intention? (and yes, I am
aware that ordering might be used to reverse that sequence so offset 0 can
be used, but wouldn't that just as much block the query for offset 1?)
--
Sincerely,
Yngve N. Pettersen
********************************************************************
Senior Developer Email: yngve@xxxxxxxxx
Opera Software ASA http://www.opera.com/
Phone: +47 23 69 32 60 Fax: +47 23 69 24 01
********************************************************************
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
