How to request a user defined lock using the GET_LOCK() function?

GET_LOCK(lock, timeout) is a MySQL built-in function that tries to obtain a user defined lock within a timeout period. It returns 1 if successful, 0 if failed. For example:

SELECT GET_LOCK('MyLock', 60), IS_FREE_LOCK('MyLock'), IS_USED_LOCK('MyLock');
  -- +------------------------+------------------------+------------------------+
  -- | GET_LOCK('MyLock', 60) | IS_FREE_LOCK('MyLock') | IS_USED_LOCK('MyLock') |
  -- +------------------------+------------------------+------------------------+
  -- |                      1 |                      0 |                    307 |
  -- +------------------------+------------------------+------------------------+

GET_LOCK(lock, timeout) function behavior can be described as:

• If the requested lock is a new lock, it is created and associated to the requesting session. GET_LOCK() returns 1 immediately.
• If the requested lock is an existing lock associated to the requesting session, an extra instance of the lock is created and associated to the requesting session. GET_LOCK() returns 1 immediately.
• If the requested lock is an existing lock associated to another session, GET_LOCK() waits for it to be released by the other session. When the lock is released within the timeout period, GET_LOCK() returns 1. When the lock is not released within the timeout period, GET_LOCK() returns 0.

The following examples shows you the multiple-instance behavior.

SELECT GET_LOCK('Cloned', 5), IS_USED_LOCK('Cloned');
  -- +-----------------------+------------------------+
  -- | GET_LOCK('Cloned', 5) | IS_USED_LOCK('Cloned') |
  -- +-----------------------+------------------------+
  -- |                     1 |                    307 |
  -- +-----------------------+------------------------+

SELECT GET_LOCK('Cloned', 5), IS_USED_LOCK('Cloned');
  -- +-----------------------+------------------------+
  -- | GET_LOCK('Cloned', 5) | IS_USED_LOCK('Cloned') |
  -- +-----------------------+------------------------+
  -- |                     1 |                    307 |
  -- +-----------------------+------------------------+

SELECT RELEASE_LOCK('Cloned'); 
  -- +------------------------+
  -- | RELEASE_LOCK('Cloned') |
  -- +------------------------+
  -- |                      1 |
  -- +------------------------+

SELECT RELEASE_LOCK('Cloned');
  -- +------------------------+
  -- | RELEASE_LOCK('Cloned') |
  -- +------------------------+
  -- |                      1 |
  -- +------------------------+

SELECT RELEASE_LOCK('Cloned');
  -- +------------------------+
  -- | RELEASE_LOCK('Cloned') |
  -- +------------------------+
  -- |                   NULL |
  -- +------------------------+

The following examples shows you the waiting behavior.

-- in session 1: 
SELECT CONNECTION_ID();
  -- +-----------------+
  -- | CONNECTION_ID() |
  -- +-----------------+
  -- |             307 |
  -- +-----------------+

SELECT GET_LOCK('Shared', 5), IS_USED_LOCK('Shared');
  -- +-----------------------+------------------------+
  -- | GET_LOCK('Shared', 5) | IS_USED_LOCK('Shared') |
  -- +-----------------------+------------------------+
  -- |                     1 |                    307 |
  -- +-----------------------+------------------------+

-- in session 2:
SELECT CONNECTION_ID();
  -- +-----------------+
  -- | CONNECTION_ID() |
  -- +-----------------+
  -- |             340 |
  -- +-----------------+

SELECT IS_USED_LOCK('Shared');
  -- +------------------------+
  -- | IS_USED_LOCK('Shared') |
  -- +------------------------+
  -- |                    307 |
  -- +------------------------+

SELECT SYSDATE(), GET_LOCK('Shared', 5), SYSDATE();
  -- +---------------------+-----------------------+---------------------+
  -- | SYSDATE()           | GET_LOCK('Shared', 5) | SYSDATE()           |
  -- +---------------------+-----------------------+---------------------+
  -- | 2023-12-19 18:28:04 |                     0 | 2023-12-19 18:28:09 |
  -- +---------------------+-----------------------+---------------------+

Reference information of the GET_LOCK() function:

GET_LOCK(lock, timeout): boolean
  Tries to obtain a user defined lock within a timeout period.

Arguments, return value and availability:
  lock: Required. The lock name to be requested.
  timeout: Required. The timeout period to wait for the lock to be released.
  boolean: Return value. 1 if request is successful, 0 otherwise.
  Available since MySQL 4.

Related MySQL functions:

• GET_LOCK() - Requesting User Defined Lock
• RELEASE_ALL_LOCKS() - Release All Locks
• RELEASE_LOCK() - Release Lock Instance

⇒ GREATEST() - Finding the Greatest/Maximum Value

⇐ EXTRACTVALUE() - Extracting Text Content from XML

⇑ MySQL Functions for Miscellaneous Purposes

⇑⇑ MySQL Function References