RELEASE_ALL_LOCKS() - Release All Locks

Q

How to release all locks associated with the current connection session using the RELEASE_ALL_LOCKS() function?

✍: FYIcenter.com

A

RELEASE_ALL_LOCKS(lock) is a MySQL built-in function that releases all locks associated with the current connection session. It returns the number of locks released. For example:

SELECT GET_LOCK('MyLockX', 60), GET_LOCK('MyLockY', 60); 
  -- +-------------------------+-------------------------+
  -- | GET_LOCK('MyLockX', 60) | GET_LOCK('MyLockY', 60) |
  -- +-------------------------+-------------------------+
  -- |                       1 |                       1 |
  -- +-------------------------+-------------------------+

SELECT IS_USED_LOCK('MyLockX'), IS_USED_LOCK('MyLockY'); 
  -- +-------------------------+-------------------------+
  -- | IS_USED_LOCK('MyLockX') | IS_USED_LOCK('MyLockY') |
  -- +-------------------------+-------------------------+
  -- |                     307 |                     307 |
  -- +-------------------------+-------------------------+

SELECT RELEASE_ALL_LOCKS();
  -- +---------------------+
  -- | RELEASE_ALL_LOCKS() |
  -- +---------------------+
  -- |                   2 |
  -- +---------------------+

SELECT IS_USED_LOCK('MyLockX'), IS_USED_LOCK('MyLockY'); 
  -- +-------------------------+-------------------------+
  -- | IS_USED_LOCK('MyLockX') | IS_USED_LOCK('MyLockY') |
  -- +-------------------------+-------------------------+
  -- |                    NULL |                    NULL |
  -- +-------------------------+-------------------------+

Note that when a session connection is terminated, all locks associated with the connection will be released automatically.

Reference information of the RELEASE_ALL_LOCKS() function:

RELEASE_ALL_LOCKS(): count
  releases all locks associated with the current connection session,
  and returns the number of locks released.

Arguments, return value and availability:
  count: Return value. Number of locks released.
  Available since MySQL 4.

Related MySQL functions:

 

RELEASE_LOCK() - Release Lock Instance

NULLIF() - NULL on Equal Values

MySQL Functions for Miscellaneous Purposes

⇑⇑ MySQL Function References

2023-12-20, 290🔥, 0💬