WordPress and multithreading

Question

I'm working on a plugin. I've created an API that reads and writes to a custom table in the DB. This API can be called potentially from N clients simultaneously (and it really happens). I noticed that in this case problems arise. To give a specific example:

if (is_null($wpdb->get_var("SELECT column FROM my_table_name WHERE condition"))) {
    // branch 1
}
else {
    // branch 2
}

In the branch 1 I innocently assume that the variable obtained from the DB is NULL, but maybe this is no longer true because it has been changed by another thread. I need the guarantee that, at least until the end of branch 1, this condition has not changed.

What's the WordPress-way to solve this problem? Semaphores, file locking, MySQL LOCK TABLE? Or there are better solutions? I searched for a long time on the web, but I wasn't able to find anything definitive. How does WordPress itself do (or the main plugins) in a case like this?

Answer 1

Is your plugin intended for use by others, or is it only going to be used on servers you control? If the latter, then there might be a few ways to achieve what you want. For instance, the Sync extension. With that, I think you could use a mutex or semaphore to set a flag, which you could then use in your read/write functions.

If you already have memcache available, then that might be a lightweight way to do the same thing, without needing to install a new extension.

Failing that, you might be able to use a db table lock, but that seems really heavy-handed, and I'd worry about the impact on database performance.