MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
This section describes functions used to manipulate user-level locks.
Table 12.19 Locking Functions
Name | Description |
---|---|
GET_LOCK() |
Get a named lock |
IS_FREE_LOCK() |
Whether the named lock is free |
IS_USED_LOCK() |
Whether the named lock is in use; return connection identifier if true |
RELEASE_LOCK() |
Release the named lock |
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.
The behavior of GET_LOCK()
changes in MySQL 5.7. In consideration of future upgrades,
limit the str
value to 64
characters or less and do not rely on subsequent calls to
GET_LOCK()
releasing previous
locks.
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
.
mysql>SELECT GET_LOCK('lock1',10);
-> 1 mysql>SELECT IS_FREE_LOCK('lock2');
-> 1 mysql>SELECT GET_LOCK('lock2',10);
-> 1 mysql>SELECT RELEASE_LOCK('lock2');
-> 1 mysql>SELECT RELEASE_LOCK('lock1');
-> NULL
The second RELEASE_LOCK()
call
returns NULL
because the lock
'lock1'
was automatically released by the
second GET_LOCK()
call.
If multiple clients are waiting for a lock, the order in which they acquire it is undefined. Applications should not assume that clients acquire the lock in the same order that they issued the lock requests.
GET_LOCK()
is unsafe for
statement-based replication. A warning is logged if you use
this function when
binlog_format
is set to
STATEMENT
.
Checks whether the lock named str
is free to use (that is, not locked). Returns
1
if the lock is free (no one is using the
lock), 0
if the lock is in use, and
NULL
if an error occurs (such as an
incorrect argument).
This function is unsafe for statement-based replication. A
warning is logged if you use this function when
binlog_format
is set to
STATEMENT
.
Checks whether the lock named str
is in use (that is, locked). If so, it returns the connection
identifier of the client session that holds the lock.
Otherwise, it returns NULL
.
This function is unsafe for statement-based replication. A
warning is logged if you use this function when
binlog_format
is set to
STATEMENT
.
Releases the lock named by the string
str
that was obtained with
GET_LOCK()
. Returns
1
if the lock was released,
0
if the lock was not established by this
thread (in which case the lock is not released), and
NULL
if the named lock did not exist. The
lock does not exist if it was never obtained by a call to
GET_LOCK()
or if it has
previously been released.
The DO
statement is convenient
to use with RELEASE_LOCK()
. See
Section 13.2.3, “DO Statement”.
This function is unsafe for statement-based replication. A
warning is logged if you use this function when
binlog_format
is set to
STATEMENT
.