Advisory Locks Also called a cooperative lock. Advisory locks do not lock data and they do not prevent access to data by clients except to the extent that they cooperate with each other. Unlike implicit and explicit locks, advisory locks are not managed by the server. Clients manage advisory lcoks using a set of function calls to cooperate among themselves.

Acquire Lock Use the GET_LOCK() function

mysql> select get_lock('my_lock', 5);
+------------------------+
| get_lock('my_lock', 5) |
+------------------------+
| 1 |
+------------------------+

The first argument is the name ofbe locked, and the second the argument is the timeout value in seconds that indicates hwo long to wait for the lock if it cannot be acquired. Returns 1 for success, 0 if a timeout occured and the lock cannot be acquired, NULL if an error occured.

Releasing Locks The lock is realesed by calling the RELEASE_LOCK() function or by calling GET_LOCK() with the same name of when the client closes the connection to the server.

mysql> select release_lock('my_lock');
+-------------------------+
| release_lock('my_lock') |
+-------------------------+
| 1 |
+-------------------------+

Check if a Lock is Free to Use The function IS_FREE_LOCK(lock_name) returns 1 if te name is not locked, 0 if it is locked, NULL if an error occurs.

mysql> select IS_FREE_LOCK('my_lock');
+-------------------------+
| IS_FREE_LOCK('my_lock') |
+-------------------------+
| 1 |
+-------------------------+

Check if a Lock is in Use

Use the function IS_USED_LOCK(lock_name)

mysql> select IS_USED_LOCK('my_lock');
+-------------------------+
| IS_USED_LOCK('my_lock') |
+-------------------------+
| NULL |
+-------------------------+

Return the connection id of the client that holds the lock on the name, or NULL if the name is not locked.