I have a query that will run on multiple servers, and possible also have multiple instances on each server. Please note that the query will only be run at the start of the script, however it is possible that two servers initiate the same query at the same time.
This is for an asyncron email system, basically every email is added to the que system when its initially sent and then we push them to the email server using cron jobs.
The "sync_server_id" and "instance_id" below is used to tie a specific email to this process, the "sync_server_id" is unique for that specific server, and the "instance_id" will be unique for that specific call of the cron job.
sync_server_id IS NULL
To my knowledge the query will not cause a race condition since the WHERE arguments does not use specific ids, and if it is called from multiple servers at exact the same time, the requests will enter the que on the database cluster and execute after turn, not affecting the same rows.
But, I am far from an expert on how exactly the MySQL database works, so if anyone know how exactly this query will behave in regards to race conditions, please let me know.
Oh, I almost forgot to mention that the table is InnoDB.
I am certain someone here has dealt with race conditions like this before.
Any comments will be welcomed