Cooperative Locks

MySQL has other kinds of locking arrangements. You can use cooperative or advisory locks so that client threads can cooperate with one another.

Cooperative locks have nothing to do with table locks; they have no power to block access to tables, and have an effect only if all relevant applications look for the presence of an advisory lock before proceeding. That's why they're called cooperative.

You can even think of them as ways of passing information between different instances of an application, but using MySQL to convey the message.

Cooperative locks are implemented by the functions GET_LOCK() and RELEASE_LOCK(). Here's the syntax:

GET_LOCK(string,timeout)

RELEASE_LOCK(string)

GET_LOCK() attempts to gain a lock with the name given by string. This is the agreed-upon lock name, and all clients must use the same lock name for the cooperation to be successful. RELEASE_LOCK() releases the cooperative lock.

In GET_LOCK(), you must specify a timeout given in seconds by timeout. The function returns 1 if the attempt is successful, 0 if a timeout occurs, or NULL if an error occurs.

A lock is released when the client thread finishes, when it issues another GET_LOCK(), or when it issues a RELEASE_LOCK().

Here's an example. You may want to follow this yourself using two mysql console windows. Imagine that client A issues the following:

mysql> SELECT GET_LOCK("my_lock",5);
+-----------------------+
| GET_LOCK("my_lock",5) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

The result is 1 because the lock is granted successfully on the string my_lock.

At this point, your client may perform some application code, which it wants to do without interference from other clients. This might be any code; for example, writing to a file.

Now client B issues a request for the same lock:

mysql> SELECT GET_LOCK("my_lock",5);
+-----------------------+
| GET_LOCK("my_lock",5) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (5.10 sec)

The result is 0, and the query takes five seconds to run. This is because we passed it a timeout of five seconds; it waits this time for client A to unlock (which it doesn't in this case) before giving up. Client A still has the lock.

Now let's make client A release its lock:

mysql> SELECT RELEASE_LOCK("my_lock");
+-------------------------+
| RELEASE_LOCK("my_lock") |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

The query returns a 1 to indicate that the lock is released successfully.

Now client B tries again:

mysql> SELECT GET_LOCK("my_lock",5);
+-----------------------+
| GET_LOCK("my_lock",5) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

This time the result is 1 because the lock on my_lock is granted to client B (and it happens immediately).

To test whether a lock is in place without waiting, you can use GET_LOCK(string,0). Giving a timeout of zero means that the lock will be obtained if it is not currently in force, but the client will have to release the lock using RELEASE_LOCK(string) if it was successful.