W dniu 21.06.2011 07:30, Michael Hudson-Doyle pisze:
Hi guys (who else should I be emailing this sort of thing to?),
After making rapid progress on the scheduler last week, today I've hit a
bit of a stumbling block. Basically, we're implementing a queue in SQL
with this sort of pseudo-code:
1) look for a job we could run
2) mark it as running
3) commit
There's an obvious race condition here if two requests overlap which
could result in the same job running on two boards. I know how to
prevent this in two ways for postgres:
1) This way: http://johtopg.blogspot.com/2010/12/queues-in-sql.html
2) Relying on SERIALIZABLE isolation level and retrying request that
fail to commit (this is what Launchpad does)
3) I never tried this solution and I don't know how this is different
from 2) but let's try this. Hopefully it would still work on SQLite.
A Queue has to operations: get() and put(). Put appends an item to the
queue, get removes an item from the front of the list.
CREATE TABLE queue
id PRIMARY KEY,
aid UNIQUE INTEGER,
...
Put is trivial, as in the code you linked to, just insert, keep the
helper 'aid' (allocation ID) NULL.
Get is trickier as we need to make sure that no two consumers allocate
the same queue item. Regardless of isolation levels you use a unique
constraint will always work, only one client would succeed in getting
the UPDATE query below to work. The other will get an IntegrityError and
can try again (to fetch another item).
# First we need to get an allocation id. We could use something
different here (like a sequence or some other good identifier). In this
example I'll just take next largest existing 'aid'.
SELECT IFNULL(MAX(aid)+1, 1) AS new_aid FROM QUEUE;
# Then we need to allocate a row for processing. We do that by trying
# to update it with the 'aid' we computed above. We only update one row
# - the oldest one (here designated by a row with the smallest ID) that
# is still not allocated.
UPDATE queue
SET aid=new_aid
WHERE
id=(SELECT MIN(id) FROM queue WHERE aid IS NULL);
The problem is that in tests we run against SQLite, and I don't know if
either technique really applies to django+sqlite (the first approach has
postgres specific syntax and for the second, django appears to really
really want to run in autocommit mode).
Fortunately django almost never runs in autocommit mode. There is an
implicit transaction around the whole request. It is easy to control the
transaction processing around a piece of code, see [1]. On PostgreSQL we
also need to properly implement handling of IngegrityError as it differs
significantly from SQLite [2]
This will probably work out OK, but we won't be able to test the
postgres variant.
The test script that was in dashboard tree tested the app in sqlite and
postgresql. If you want to test that the queue is indeed working let's
move it to a dedicated app (django reusability :-) setup a small
instance on postgress and bombard it with queries.
Does this sound sane to you guys?
I would rather have a single solution for both databases if possible. If
not then perhaps SQL is not the right way to implement a queue but that
would significantly complicate our work.
CCing to linaro-dev, somebody might be interested.
Best regards
ZK
[1]:
https://docs.djangoproject.com/en/dev/topics/db/transactions/?from=olddocs#controlling-transaction-management-in-views
[2]:
https://docs.djangoproject.com/en/dev/topics/db/transactions/?from=olddocs#handling-exceptions-within-postgresql-transactions
_______________________________________________
linaro-dev mailing list
linaro-dev@lists.linaro.org
http://lists.linaro.org/mailman/listinfo/linaro-dev
