Multiple Scripts With the Same MySQL Query Running At Once => Will There Be Interference?

Question

I want to have multiple scripts running from cron at the same time. They will all update, say, 500 rows with a unique status ID for that particular script to use.

If each script grabs the same type of rows to update, and the scripts run at once, will there be interference between the queries run at the same time? For example, using this query:

UPDATE table SET status = <unique script ID> WHERE status = 0 LIMIT 500

Will I get the same rows updating more than once? I'm trying to get each script to get unique rows. I was thinking of using sleep() to make the queries run at different times, but I'm not sure if that's necessary.

Thanks.

Answer 1

I think queries are executed in sequential order. You might hit a problem if you asked for the list of rows to update first, for instance, but this one should be fine.

A problem scenario: Scripts 1 and 2 are started at almost exactly the same time.

• Script 1: SELECT id FROM table WHERE status = 0 LIMIT 500
• Script 2: SELECT id FROM table WHERE status = 0 LIMIT 500
• Script 1: UPDATE table SET status = 123 WHERE id IN (1,2,3...)
• Script 2: UPDATE table SET status = 124 WHERE id IN (1,2,3...)

Your scenario, however, with just one query per script, will prevent such issues. Script 1 will update all of the statuses, and Script 2 will find that it has none to update.

Answer 2

You should be fine as long as your underlying storage engine has transactional semantics. InnoDB will work, but MyIsam will not.