I've seen occasional deadlocks reported, and I'm wondering how I
should avoid them, or if my approach is just wrong.

I have two client processes running in PHP on different machines
accessing the same database (all InnoDB on MySQL 5.0.24a). Each has
an integer daemon_id which is used to grab a chunk of a list of tasks
which it can take away and process before coming back for more. The
task grabbing process goes like this (it has failure detection and
rollback if necessary):

BEGIN;
UPDATE task SET daemon_id = $my_daemon_id where daemon_id = 0 and
status = 'new' LIMIT 100;
COMMIT;

Then it grabs the tasks it has claimed:

SELECT * FROM task WHERE daemon_id = $my_daemon_id AND status = 'new';

These tasks should never be locked by another daemon because the
daemon_id doesn't match.

As each process completes its tasks, it issues single updates:

UPDATE task SET status = 'complete' where id = 123;

In theory, this mechanism is intended to allow me to add more client
processes to increase capacity without them treading on each other.

I don't see where the deadlock can occur, but there is one in the
initial task grab apparently. If the two processes try to grab the
same tasks at once, I would expect the later of the two to fail to do
the update, or simply fail to find tasks in common because the
transaction is atomic. Have I got this all wrong? Do I need to be
more explicit about locking during the update? Is there a better way
of doing this?

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
marcus@synchromedia.co.uk | http://www.synchromedia.co.uk/