Re: triggers? when they execute and locking

[Gleb Paharenko <suppressed>]

Hello.

Please could you provide a repeatable test case, so it will be easier
for list members to diagnose this weird behavior?



Ben De Luca wrote:
> 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
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   suppressed
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/suppressed