Skip links
Main content

MySQL GET_LOCK() explained

Wednesday 02 June 2010 18:10

I've seen many questions arise around the MySQL GET_LOCK function. Some people complain it's broken, but it's not: You need to harness the power of GET_LOCK(). It's able to do unique things.

By Casper Langemeijer
GET_LOCK('TrekkiesAreCool', 10);

The MySQL statement above acquires a lock named 'TrekkiesAreCool' or, if the lock is already held by another connection, waits 10 seconds to get it. The returned value is 1 or 0 depending on whether we got the lock or not.

This lock has got nothing to do with any data in a mysql database, it's just a named mutex lock. For information what a mutex is see this stackoverflow question or this wikipedia article.

This all seems very simple, but why do guys like this fail to get it working in PHP? The answer to that is also very simple:

A PHP request closes the mysql connection when it's done. Because a lock is held for a connection, it is released.

If this seems annoying to you, you are probably not using mysql get_lock() what it was intended for. If you want to have a lock that is persistent over multiple requests, you might want to consider writing a file, or use a mysql table to write it to. To avoid disk writes, you could set some variable in APC shared memory.

You are probably thinking: What about this great power you were talking about? 

Well: you can 'monitor' the health and existence of another PHP background process. Reliably. Without much complicated code. MySQL connections are closed by the server when an application terminates. This happens on a normal exit, a PHP fatal error, segfault or any other ending of you application. Because the connection is closed the lock is freed.

We start our background process in cron, the process is started every minute. It starts and tries to acquire a lock, with a 60 second timeout. If we succeed to get our lock, we start doing our business. Some while(true){}-like functionality, say, wait for incoming star trek transporter beams. If we'd done some bad coding, (we probably do) it's likely that fatal transporter accidents happen because PHP is going to kill us.

If we died more than a minute after we started, cron already started a second instance, trying to get the lock we are holding. As we are dying the lock gets released and the new instance will take over where we left off.

If by pure chance we didn't die, the newer instance won't acquire the lock, and because of that exit();

Because 4 lines of pseudo-code is worth a thousand words:

$sql = 'SELECT GET_LOCK("BeamMeUpScotty", 60)';
$result = $mysql->query($sql);
list($gotLock) = $result->fetch_row();
if (!$gotLock)  exit(); 
while(true) {
    // This is where the beaming happens
}

For further in-depth analysis of the beaming code see get the manual.

« Back

Reactions on "MySQL GET_LOCK() explained"

garfix
Placed on: 06-04-2010 09:19
Patrick van Bergen
User icon
to be continuum
What surprised me about GET_LOCK() was that when a GET_LOCK('a') is followed by a GET_LOCK('b') in the same session, this second call automatically releases the first lock ('a'). This means that GET_LOCK() can be used to obtain only a single lock at any time in your PHP script.
charles
Placed on: 06-10-2011 14:23
Although it's connection based, so making GET_LOCK('a', 0) on connection id. 1 and then GET_LOCK('b', 0) on connection 2 will not free the first one. One interesting thing would be to test when the mysql thread handling is not set to one thread per connection.
Alex
Placed on: 05-03-2012 20:41
my server is being overload due to

SELECT GET_LOCK('holstei_moodle-mdl_-session-37621',120)

What is wrong on this string and how can it be fixed?
GDmac
Placed on: 09-10-2012 21:14
Beware when using persistent connections in your framework or webapp. I had problems with the transporter-room being occupied all the time.

And then there is a bug in mysql < 5.5.3, where, if you try to get a lock, but the lock is not free, any script requesting the same lock will wait for the entire requested time, grmbl (see the note in the mysql manual for GET_LOCK.)

So it is probably wise to first check IS_FREE_LOCK() or IS_USED_LOCK(), just to make sure Scotty has left the transporter-room.

Log in to comment on news articles.