mysql get_lock is a locking function using which we can get a named lock.

As part of our Server administration services, Bobcares provides queries.

Let’s delve into the specifics of the mysql get_lock function.

MySQL get_lock

The syntax of MySQL get_lock locking function is GET_LOCK(str,timeout). It attempts to obtain a lock with the string str as the name, with a timeout of timeout seconds. It returns 1 if the lock was successfully obtained, returns 0 if it timed out, and NULL if there was an error. For GET_LOCK() and related functions, str is case insensitive. If str is null or an empty string, GET_LOCK() returns NULL and does nothing.

We can release the lock obtained with GET_LOCK() explicitly by executing RELEASE_LOCK(). It will also be released implicitly when the session ends. When a transaction commits or rolls back, the locks obtained with GET_LOCK() are not released.

The metadata locking (MDL) subsystem was used to reimplement  GET_LOCK() in the most recent version of MySQL, and its capabilities were expanded. Multiple locks can be acquired at the same time, and GET_LOCK() does not release any existing locks. It is also possible for a single session to obtain multiple locks with the same name.

Result of MDL reimplementation

The Performance Schema metadata_locks table now contains uniquely named locks acquired with GET_LOCK() as a result of the MDL reimplementation. USER LEVEL LOCK is the OBJECT_TYPE column, and the OBJECT_NAME column is the lock name.

Only the first lock for a name registers a row in the metadata locks table if multiple locks are acquired for the same name. The counter in the lock is incremented by subsequent locks for the name, but no additional metadata locks are acquired. When the last lock instance on the name is released, the metadata_locks row for the lock is deleted.

Locks acquired with this function appear in the Information Schema METADATA_LOCK_INFO table if the metadata_lock_info plugin is installed. For statement-based replication, statements that use the GET_LOCK() function are not safe.

Only one simultaneous lock can be acquired before MySQL 5.7, and GET_LOCK() releases any existing lock. The following example demonstrates the difference in mysql get_lock acquisition behaviour in MySQL 5.7. Assume we run the following commands:


SELECT GET_LOCK('lock1',10);
SELECT GET_LOCK('lock2',10);
SELECT RELEASE_LOCK('lock2');
SELECT RELEASE_LOCK('lock1');


The second GET_LOCK() in MySQL 5.7 or later acquires a second lock, and both RELEASE LOCK() calls return 1. (success). Because there is no ‘lock1’ to release before MySQL 5.7, the second GET_LOCK() releases the first lock (‘lock1’), and the second RELEASE LOCK() returns NULL (failure).

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, we have looked into the details of the mysql get_lock functions.