MySQL named locks

Until recently, I’ve only run PHP sites on single host setups, which has kept things reasonably simple. However, I’ve moved one project to a load balanced solution, which randomly sends requests to one of two servers – unless one server is down, in which case all requests go to the other machine. This is great for keeping the site online – although a single point of failure still exists in the form of the load balancer – but produces some interesting problems.

One such problem is that the same cron jobs need to be configured on both machines, because if one goes down you still want the cron job to run. However, if both sites are up, they will both try and run the same cron job, which will cause problems in many cases – e.g. if the script sends out emails, you’ll end up with two copies.

In order to solve the problem, you ideally want scripts to be able to obtain a lock which prevents the other scripts from running at the same time, using the following pseudocode:

lock = get_lock();
if (lock)
  do_stuff();
endif
free_lock(lock);

You might be tempted to start writing your own locking functionality, but this is a task fraught with potential problems. Fortunately, MySQL has already come up with a solution in the form of named locks, which is not widely publicised but incredibly useful.

Note: Calling this feature ‘named locks’ is a bit misleading, as it does not actually lock the database, it merely allows you to check whether another script has obtained a ‘lock’. MySQL will not prevent you from issuing queries just because a named lock has been obtained in another script.

Using this feature is incredibly easy – here’s an example of how it might be done (this code assumes you have established a database connection and, for reasons of space, does not contain any error checking code):

// Define the two functions for locking
function get_named_lock($lockname) {
  $rs = $db->query("SELECT IS_FREE_LOCK($lockname) AS isfree";
  $result = $rs->fetchRow();

  if ($result['isfree']) {
    $rs = $db->query("SELECT GET_LOCK($lockname, 0) AS locked");
    $result = $rs->fetchRow();
    return $result['locked'];
  } else {
    return false;
  }
}

function release_named_lock($lockname) {
  $db->query("DO RELEASE_LOCK($lockname);
}

// In each script, have this code:
$locked = get_named_lock('mylock');
if (!$locked) {
  // Could not obtain the lock, so exit
  exit;
}
release_named_lock('mylock');

Warning: You can only create one named lock per connection. If you execute a GET_LOCK query a second time in the same connection, it will release the previous lock, even if the second query has a different lock name.

For example:

$db->query("SELECT GET_LOCK('lock1', 0)");
$db->query("SELECT GET_LOCK('lock2', 0)");

After the second query, you will be left with one lock (lock2) – the previous lock will have been released automatically. If you must have two locks in the same script, make sure you are using two separate connections, and that they are not persistent.

Further reading

In the course of trying to find out about named locks, I’ve come across a few other links which may be of interest:

• MySQL reference manual for miscellaneous functions (including GET_LOCK and RELEASE_LOCK)
• How to coordinate distributed work with MySQL’s GET_LOCK
• MySQL GET_LOCK() explained

Hat tip also goes to Matthew from Bytemark, who suggested them in an email to me.