28.3. Advisory Locking

An advisory lock is a cooperative lock. That is, an advisory lock has no power to prevent data access by other clients, but instead is based on the concept that all clients will use an agreed-upon convention to cooperate for use of a resource. The convention is the lock name, which is simply a string. While the name is locked, the advisory lock is considered to be in place and every other cooperating client refrains from whatever action it would perform if it held the lock itself.

Advisory locks are implemented using a set of function calls. To acquire a lock, use the GET_LOCK() function:

 mysql> SELECT GET_LOCK('my lock', 5); +------------------------+ | GET_LOCK('my lock', 5) | +------------------------+ |                      1 | +------------------------+ 

The first argument is a string that specifies the name to be locked, and the second argument is a timeout value in seconds that indicates how long to wait for the lock if it cannot be acquired immediately. GET_LOCK() returns 1 for success, 0 if a timeout occurs and the lock cannot be acquired, or NULL if an error occurs.

A client that has acquired an advisory lock can release it by calling RELEASE_LOCK():

 mysql> SELECT RELEASE_LOCK('my lock'); +-------------------------+ | RELEASE_LOCK('my lock') | +-------------------------+ |                       1 | +-------------------------+ 

RELEASE_LOCK() returns 1 if the lock was released successfully, 0 if the name was locked but not by the client requesting the release, and NULL if the name was not locked.

An advisory lock also is released if the client makes another call to GET_LOCK() or closes its connection to the server.

Two other functions are available for checking the status of advisory locks:

• IS_FREE_LOCK(lock_name) returns 1 if the name is not locked, 0 if it is locked, and NULL if an error occurs.

• IS_USED_LOCK(lock_name) returns the connection ID of the client that holds the lock on the name, or NULL if the name is not locked.