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
/ /|_/ / // /\ \/ /_/ / /__ [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/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
