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

strCopy Code

as the name, with a timeout of

timeoutCopy Code

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()Copy Code

and related functions,

strCopy Code

is case insensitive. If

strCopy Code

is null or an empty string,

GET_LOCK()Copy Code

returns NULL and does nothing.

We can release the lock obtained with

GET_LOCK()Copy Code

explicitly by executing

RELEASE_LOCK()Copy Code

. It will also be released implicitly when the session ends. When a transaction commits or rolls back, the locks obtained with

GET_LOCK()Copy Code

are not released.

The metadata locking (MDL) subsystem was used to reimplement  
GET_LOCK()Copy Code
 in the most recent version of MySQL, and its capabilities were expanded. Multiple locks can be acquired at the same time, and 
GET_LOCK()Copy Code
 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()Copy Code

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()Copy Code

function are not safe.

Only one simultaneous lock can be acquired before MySQL 5.7, and

GET_LOCK()Copy Code

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');
Copy Code

The second

GET_LOCK()Copy Code

in MySQL 5.7 or later acquires a second lock, and both

RELEASE LOCK()Copy Code

calls return 1. (success). Because there is no ‘lock1’ to release before MySQL 5.7, the second

GET_LOCK()Copy Code

releases the first lock (‘lock1’), and the second

RELEASE LOCK()Copy Code

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.