Топ-100
 

GET_LOCK

RDBMS Type: MySQL
Topic

Syntax:
GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the string str, using a
timeout of timeout seconds. A negative timeout value means infinite
timeout. The lock is exclusive. While held by one session, other
sessions cannot obtain a lock of the same name.

Returns 1 if the lock was obtained successfully, 0 if the attempt timed
out (for example, because another client has previously locked the
name), or NULL if an error occurred (such as running out of memory or
the thread was killed with mysqladmin kill).

A lock obtained with GET_LOCK() is released explicitly by executing
RELEASE_LOCK() or implicitly when your session terminates (either
normally or abnormally). Locks obtained with GET_LOCK() are not
released when transactions commit or roll back.

GET_LOCK() is implemented using the metadata locking (MDL) subsystem.
Multiple simultaneous locks can be acquired and GET_LOCK() does not
release any existing locks. For example, suppose that you execute these
statements:

SELECT GET_LOCK('lock1',10);
SELECT GET_LOCK('lock2',10);
SELECT RELEASE_LOCK('lock2');
SELECT RELEASE_LOCK('lock1');

The second GET_LOCK() acquires a second lock and both RELEASE_LOCK()
calls return 1 (success).

It is even possible for a given session to acquire multiple locks for
the same name. Other sessions cannot acquire a lock with that name
until the acquiring session releases all its locks for the name.

Uniquely named locks acquired with GET_LOCK() appear in the Performance
Schema metadata_locks table. The OBJECT_TYPE column says USER LEVEL
LOCK and the OBJECT_NAME column indicates the lock name. In the case
that multiple locks are acquired for the same name, only the first lock
for the name registers a row in the metadata_locks table. Subsequent
locks for the name increment a counter in the lock but do not acquire
additional metadata locks. The metadata_locks row for the lock is
deleted when the last lock instance on the name is released.

The capability of acquiring multiple locks means there is the
possibility of deadlock among clients. When this happens, the server
chooses a caller and terminates its lock-acquisition request with an
ER_USER_LOCK_DEADLOCK
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.h
tml#error_er_user_lock_deadlock) error. This error does not cause
transactions to roll back.

MySQL enforces a maximum length on lock names of 64 characters.

GET_LOCK() can be used to implement application locks or to simulate
record locks. Names are locked on a server-wide basis. If a name has
been locked within one session, GET_LOCK() blocks any request by
another session for a lock with the same name. This enables clients
that agree on a given lock name to use the name to perform cooperative
advisory locking. But be aware that it also enables a client that is
not among the set of cooperating clients to lock a name, either
inadvertently or deliberately, and thus prevent any of the cooperating
clients from locking that name. One way to reduce the likelihood of
this is to use lock names that are database-specific or
application-specific. For example, use lock names of the form
db_name.str or app_name.str.

URL: https://dev.mysql.com/doc/refman/8.0/en/locking-functions.html

Example