RELEASE_LOCK() - Release Lock Instance

Q

How to release an instance of a given lock associated with the current connection session using the RELEASE_LOCK() function?

✍: FYIcenter.com

A

RELEASE_LOCK(lock) is a MySQL built-in function that releases an instance of a given lock associated with the current connection session. It returns 1 if released Successfully, 0 if the lock is associated with other connection session, or NULL if the does not exist. For example:

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

SELECT RELEASE_LOCK('MyLock'), IS_USED_LOCK('MyLock'); 
  -- +------------------------+------------------------+
  -- | RELEASE_LOCK('MyLock') | IS_USED_LOCK('MyLock') |
  -- +------------------------+------------------------+
  -- |                      1 |                   NULL |
  -- +------------------------+------------------------+

SELECT RELEASE_LOCK('MyLock'), IS_USED_LOCK('MyLock');
  -- +------------------------+------------------------+
  -- | RELEASE_LOCK('MyLock') | IS_USED_LOCK('MyLock') |
  -- +------------------------+------------------------+
  -- |                   NULL |                   NULL |
  -- +------------------------+------------------------+

If a lock has multiple instances, you need to call RELEASE_LOCK() multiple times to release them. For example:

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'), IS_USED_LOCK('Cloned');
  -- +------------------------+------------------------+
  -- | RELEASE_LOCK('Cloned') | IS_USED_LOCK('Cloned') |
  -- +------------------------+------------------------+
  -- |                      1 |                    307 |
  -- +------------------------+------------------------+

SELECT RELEASE_LOCK('Cloned'), IS_USED_LOCK('Cloned');
  -- +------------------------+------------------------+
  -- | RELEASE_LOCK('Cloned') | IS_USED_LOCK('Cloned') |
  -- +------------------------+------------------------+
  -- |                      1 |                   NULL |
  -- +------------------------+------------------------+

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

Reference information of the RELEASE_LOCK() function:

RELEASE_LOCK(lock): boolean
  releases an instance of the given lock associated with the current 
  connection session.

Arguments, return value and availability:
  lock: Required. The lock to be released.
  boolean: Return value. 1 if an instance is released, 0 if the lock 
    is associated with another connection, or NULL if lock does not exist.
  Available since MySQL 4.

Related MySQL functions:

 

SLEEP() - Holding Statement Execution

RELEASE_ALL_LOCKS() - Release All Locks

MySQL Functions for Miscellaneous Purposes

⇑⇑ MySQL Function References

2023-12-20, 241🔥, 0💬