I'm using PostgreSQL (via pyPgSQL) to deal with a database of tasks. I
want to have processes be able to "check out" a task, but I'm seeing some
kind of odd results. If I try to force two processes to check out tasks
at the same time, some of them get a response that would indicate no
further tasks.
Here are the details. The database is:
CREATE TABLE jobs (
id serial,
assignedto text default NULL
);
The SQL I'm using is:
1) SELECT id FROM jobs WHERE assignedto is NULL FOR UPDATE LIMIT 1;
2) UPDATE jobs SET assignedto = 'assigned' WHERE id = <ID gotten above>;
3) Commit
Each worker is only interested in a single job, hence the "LIMIT 1".
The "weirdness" is that if two processes do step 1 above at the same time,
the second one will get an empty result set. The second process to do step
1 will wait because of the update lock until process 1 gets to step 3. If
I set the limit to 2, then the same thing happens to the third process
that's simultaneously at step 1.
It would seem like the select is getting performed, but then the second
process is getting blocked, and then when the first process completes the
row that it updated is getting removed from the result set of the second
one.
I'm not sure if this is a bug or a feature, but it wasn't what I was
expecting to have happen. I'll probably modify the way it works so that
either I just use no limit (since I guess that wouldn't impact
performance), or make another table which has the job number and who it's
assigned to. That way, with a unique constraint on the job number, I can
get feedback that there was a collision (instead of it just appearing that
there's no jobs to work).
Sean
--
Well I've been to one world fair, a picnic and a rodeo, and that's the
stupidest thing I've heard come over a pair of earphones. -- Major Kong
Sean Reifschneider, Inimitably Superfluous <jafo@tummy.com>
tummy.com - Linux Consulting since 1995. Qmail, KRUD, Firewalls, Python
