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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
