Re: triggers? when they execute and locking

[Ben De Luca <suppressed>]

Ill try? and make some thing more simple thats what I have actually  
running,
Can some one clarify that what I am doing should work though?




On 05/12/2005, at 9:54 PM, Gleb Paharenko wrote:

> 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/mysql? 
> suppressed


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