List:General Discussion« Previous MessageNext Message »
From:Ben De Luca Date:December 4 2005 9:57pm
Subject:triggers? when they execute and locking
View as plain text  
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



















Thread
triggers? when they execute and lockingBen De Luca4 Dec
  • Re: triggers? when they execute and lockingGleb Paharenko5 Dec
    • Re: triggers? when they execute and lockingBen De Luca5 Dec