MySQL get_lock

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.