How the GET_LOCK() function works in Mariadb?

The GET_LOCK() function is a locking function that acquires a named lock with a specified timeout.

Posted on

The GET_LOCK() function is a locking function that acquires a named lock with a specified timeout. It can be used for synchronizing access to shared resources or preventing concurrent execution of certain code sections.

Syntax

The syntax of the GET_LOCK() function is as follows:

GET_LOCK(lock_name, timeout)

The lock_name parameter is a string that identifies the lock to be acquired. The lock name is case insensitive and can be up to 64 characters long. The lock name can be any valid expression that evaluates to a string.

The timeout parameter is a decimal value that specifies the number of seconds to wait for the lock before giving up. The timeout can be any valid expression that evaluates to a decimal value. If the timeout is negative, the function waits indefinitely. If the timeout is zero, the function does not wait at all.

The function returns 1 if the lock was obtained successfully, 0 if the lock could not be obtained within the timeout, or NULL if an error occurred.

Examples

The following example shows how to use the GET_LOCK() function to acquire a lock named ‘my_lock’ with a timeout of 10 seconds.

SELECT GET_LOCK('my_lock', 10) AS result;

The output is:

+--------+
| result |
+--------+
|      1 |
+--------+

The function returns 1, indicating that the lock was obtained successfully. The lock will be released when the current session ends or when the RELEASE_LOCK() function is called.

Some of the functions that are related to the GET_LOCK() function are:

  • RELEASE_LOCK(): This function releases a named lock that was obtained by the current session. It returns 1 if the lock was released successfully, 0 if the lock was not held by the current session, or NULL if the lock did not exist.
  • IS_FREE_LOCK(): This function checks whether a named lock is free to be acquired. It returns 1 if the lock is free, 0 if the lock is in use, or NULL if the lock does not exist or an error occurred.
  • IS_USED_LOCK(): This function returns the connection identifier of the session that holds a named lock, or NULL if the lock is free, does not exist, or an error occurred.

Conclusion

The GET_LOCK() function is a useful function for acquiring a named lock with a specified timeout. It can be used for synchronizing access to shared resources or preventing concurrent execution of certain code sections. It can also be combined with other locking functions to achieve various locking operations.