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:
- look for a job we could run
- mark it as running
- 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:
- This way: http://johtopg.blogspot.com/2010/12/queues-in-sql.html
- 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#c... [2]: https://docs.djangoproject.com/en/dev/topics/db/transactions/?from=olddocs#h...