Lock functions-PolarDB(PolarDB)-阿里云帮助中心

PolarDB-X 2.4.0_5.4.19-20240426 and later support all user-level named lock functions from MySQL 5.7. For the MySQL 5.7 reference, see Locking Functions.

Supported functions

Function	Description
`GET_LOCK(str, timeout)`	Acquires a named lock. `str` is the lock name; `timeout` is the maximum wait time. Returns `1` if the lock is acquired, `0` if the attempt timed out, or `NULL` if an error occurred.
`IS_FREE_LOCK(str)`	Checks whether a named lock is available. Returns `1` if the lock is not held, `0` if it is held, or `NULL` if an error occurred.
`IS_USED_LOCK(str)`	Returns the connection identifier of the session holding the named lock, or `NULL` if the lock is not held.
`RELEASE_LOCK(str)`	Releases a named lock. Returns `1` if the lock is released, `0` if the lock was not acquired by the current thread, or `NULL` if the lock does not exist.
`RELEASE_ALL_LOCK()`	Releases all named locks held by the current session. Returns the number of released locks, or `0` if no locks were held.

Example: basic lock lifecycle

-- Acquire a lock (wait up to 10 seconds)
SELECT GET_LOCK('my_lock', 10);
-- Returns: 1 (acquired)

-- Check lock status from another session
SELECT IS_FREE_LOCK('my_lock'), IS_USED_LOCK('my_lock');
-- IS_FREE_LOCK: 0 (in use), IS_USED_LOCK: <connection_id>

-- Release the lock
SELECT RELEASE_LOCK('my_lock');
-- Returns: 1 (released)

IS_MY_LOCK (PolarDB-X extension)

IS_MY_LOCK(str) checks whether the current session holds the named lock. Returns 1 if the lock is held by the current session, or 0 if it is not.

Usage notes

In a distributed database, a lock held for an extended period can become invalid due to a partition split-brain event. To prevent your application from continuing to operate on an invalid lock, call IS_MY_LOCK every 60 seconds to verify the lock is still valid.

get_lock(lock_name);           // Acquire the lock.

while (is_my_lock(lock_name)){ // Verify the lock is still valid.
    do_work_with_timeout(60);  // Execute the business operation. Limit: 60 s.
    if (work_finished){        // Check whether the work is done.
        break;
    }
}

release_lock(lock_name);       // Release the lock.