RELEASE_ALL_LOCKS

Releases all user-level advisory locks held by the current session and returns the count of locks that were released. This is a MatrixOne extension — MySQL releases locks on session close but does not provide an equivalent function to release all locks at once.

Syntax

RELEASE_ALL_LOCKS()

Arguments

This function takes no arguments.

Return Value

  • Returns the number of locks released (BIGINT).

  • Returns 0 if the session held no locks.

Usage Notes

  • RELEASE_ALL_LOCKS() releases all named advisory locks acquired by the current session via GET_LOCK(). Locks held by other sessions are not affected.

  • After calling RELEASE_ALL_LOCKS(), IS_USED_LOCK() returns NULL for previously held locks, and IS_FREE_LOCK() returns 1.

  • Locks are also automatically released when the session ends.

Examples

DROP DATABASE IF EXISTS release_all_locks_demo;
CREATE DATABASE release_all_locks_demo;
USE release_all_locks_demo;

-- Acquire multiple locks
SELECT GET_LOCK('lock_a', 0);
-- Returns 1

SELECT GET_LOCK('lock_b', 0);
-- Returns 1

-- Verify locks are held
SELECT IS_USED_LOCK('lock_a') IS NOT NULL;
-- Returns 1

SELECT IS_USED_LOCK('lock_b') IS NOT NULL;
-- Returns 1

-- Release all locks at once
SELECT RELEASE_ALL_LOCKS();
-- Returns 2 (two locks released)

-- Verify locks are free
SELECT IS_FREE_LOCK('lock_a');
-- Returns 1

SELECT IS_FREE_LOCK('lock_b');
-- Returns 1

-- Calling again when no locks are held
SELECT RELEASE_ALL_LOCKS();
-- Returns 0

-- Acquire a single lock and release all
SELECT GET_LOCK('lock_c', 0);
SELECT RELEASE_ALL_LOCKS();
-- Returns 1

DROP DATABASE release_all_locks_demo;

See Also