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
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
