query concurrency

[btaber]

In theory, if I had two programs perform the same query at the exact same time: ($d = daemon PID)

UPDATE table SET daemon=$d WHERE daemon IS NULL LIMIT 1

Is there any chance they could hit the same record? I am using this to do job caching and execution. The damon field is null when inserted by the client and I have 4 backend processes to handle the actual jobs by first "claiming" the job entry with the first update statement and then doing:

SELECT * FROM table WHERE daemon=$d

to retrieve the entry. I just want to make sure that there is no way that any job could be processed more than once...

 

[TonyGroves]

If you want to make sure that no other process reads the table while the current process is using it, then you would need to use table locking. "LOCK TABLE tblname WRITE" will prevent any reads by other processes while the lock is in place. Then, when you are finished, you _must_ do an "UNLOCK TABLES" statement.

 

[btaber]

I was thinking about doing that but then the client side actually adding the queue items will be affected. I can't take the chance that the table will remained locked. (because of some program error) Not really concerned about the selectes but the update statements. When mysql receives update statements, does it do them in order or all at the same time?

 

[TonyGroves]

MySQL executes one query at a time (unless it decides that there is no chance of a clash), so two updates are never going to interfere with each other.

My reference to table locking was irrelevant really; it's not an issue here. What you're proposing is perfectly safe.

 

[btaber]

Awsome. Thank you very much. I was going to do the locking at first untill I really thought about it. Just wanted to make sure....