Yuriy Zisin Development as a lifestyle

Application semaphores using MySQL

Many tasks require some application part to run in one instance per time, such as mail delivery cron job. And each time developers should invent some mechanism to determine if another copy is running. Usually no of these mechanisms are 100% reliable.

The solution is already found by MySQL development team. It is "named locks".

Application can try to acquire a named lock on its start and check if another copy is running.

How can it be implemented:

$res = mysql_query("SELECT GET_LOCK('lockname', 2)");
$result = mysql_fetch_array($res);
if (!intval($result[0])) {
    echo "Cannot run until previous instance finish!";
    exit;
}

// Do anything we need

mysql_query("SELECT RELEASE_LOCK('lockname')");

What we get using this mechanism:

• GET_LOCK will fail if a lock with specified name already set and script can stop;
• Script can work as long time as it needs;
• RELEASE_LOCK will free acquired lock and script will run successfully next time;
• If the script will fail with fatal error, time restriction or somehow else, MySQL will free its lock automatically on connection close;
• No additional files, table records, no stupid checks. Everything is done in 2 queries;
• We can be 100% sure another copy is running now, no lost locks;

Note: All the above is true if you use only one lock per script (and probably without persistent connection) because callng GET_LOCK second time during the same session will release previous lock. This feature is very usefull for cronjobs.

To read more about these functions - visit MySQL Manual