Im am trying to implement a task running system with mysql and Im
coming across a few problems with what I am trying to do.
Simply I am trying to insert a list of tasks into the DB for various
computers to run, I want to track when they ran and where they ran. I
also have to limit some of the types of tasks that can run, so no
more than 5 of TYPEA can run concurrently.
What i have is a table called tasks
TID (int)
status enum(waiting, completed, failed)
ResourceID (int)
task descriptor varchar (so I know what to run)
I also have a table
Resource
ResourceID (int)
total (int)
used (int)
So I do a select
select * from tasks left join resource on
tasks.resourceID=Resource.Resource.id
where status='waiting'
and ((resource.used<=Resource.total +1) or
resource.id in null)
limit 1 for update;
Next my code runs an update
update tasks set status='waiting' where TID=<value from above>;
NOTE: that I have to use Resource.used<=Resource.total +1 because
Resource.used<Resource.total does not seem to be equivilent
Though I still need to update the Resource counter.
So I have a trigger on BEFORE UPDATE tasks; That calls a Stored
procedure. that increments the Resource.used
The Way I understand it the order for operations is
Select
Triggered from Update
Stored Procedure (called from the trigger)
Update
And rows used in the Select should be locked until after the update.
This all happens very fast but when I have around 10 threads all
connected to the DB running tasks, I tend to find that the locking
does not work. The Trigger that exists before UPDATE actualy happens
after.
Is this a bug or is it meant to be like this?
bd
