GET_LOCK(str,timeout)

尝试获取名称由字符串 str 给定的锁，使用 timeout 秒的超时。负值 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.

如果锁定成功，则返回 1；如果尝试超时（例如，因为另一个客户端之前已锁定该名称），则返回 0；如果发生错误（例如内存不足或线程被 mysqladmin kill 杀死），则返回 NULL。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).

通过GET_LOCK()获得的锁可以通过执行RELEASE_LOCK()显式释放，或者在会话终止时（无论是正常还是异常）隐式释放。通过GET_LOCK()获得的锁在事务提交或回滚时不会释放。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()是使用元数据锁定（MDL）子系统实现的。可以同时获取多个锁，并且GET_LOCK()不会释放任何现有的锁。例如，假设您执行以下语句：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');

第二个 GET_LOCK() 获取了第二个锁，并且两个 RELEASE_LOCK() 调用都返回 1（成功）。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.

通过 GET_LOCK() 获取的唯一命名锁会出现在性能模式 metadata_locks 表中。OBJECT_TYPE 列显示 USER LEVEL LOCK，OBJECT_NAME 列指示锁的名称。如果为 相同 名称获取了多个锁，则只有该名称的第一个锁会在 metadata_locks 表中注册一行。之后对该名称的锁会增加锁中的计数器，但不会获取额外的元数据锁。当该名称上的最后一个锁实例释放时，该锁在 metadata_locks 表中的行将被删除。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.

获取多个锁的能力意味着客户端之间可能会发生死锁。当发生这种情况时，服务器会选择一个调用者并终止其锁获取请求，并返回ER_USER_LOCK_DEADLOCK错误。此错误不会导致事务回滚。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 error. This error does not cause transactions to roll back.

MySQL 对锁名称的最大长度强制限制为 64 个字符。MySQL enforces a maximum length on lock names of 64 characters.

GET_LOCK() 可以用于实现应用程序锁或模拟记录锁。名称是在整个服务器范围内锁定的。如果一个名称在某个会话中已被锁定，GET_LOCK() 会阻止另一个会话对相同名称的锁发出请求。这使得同意使用给定锁名称的客户端可以使用该名称进行协作型建议锁。但请注意，这也允许一个不在协作客户端集合中的客户端锁定一个名称，无论是无意还是故意，从而阻止任何协作客户端锁定该名称。减少这种可能性的一种方法是使用数据库特定或应用程序特定的锁名称。例如，可以使用如下形式的锁名称：db_name.str 或 app_name.str。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.

如果多个客户端正在等待锁，它们获取锁的顺序是未定义的。应用程序不应假设客户端获取锁的顺序与它们发出锁请求的顺序相同。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() 对基于语句的复制不安全。当 binlog_format 设置为 STATEMENT 时使用此函数会记录警告。GET_LOCK() is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

由于 GET_LOCK() 仅在单个 mysqld 上建立锁，因此不适用于 NDB Cluster，因为 NDB Cluster 无法在多个 MySQL 服务器之间强制实施 SQL 锁。更多信息请参见 第 25.2.7.10 节，“多个 NDB Cluster 节点相关的限制”。Since GET_LOCK() establishes a lock only on a single mysqld, it is not suitable for use with NDB Cluster, which has no way of enforcing an SQL lock across multiple MySQL servers. See Section 25.2.7.10, “Limitations Relating to Multiple NDB Cluster Nodes”, for more information.

INSERT INTO ... SELECT GET_LOCK(t1.col_name) FROM t1;

一种不同的锁定接口可作为插件服务或一组可加载函数使用。此接口提供锁命名空间以及不同的读锁和写锁，这与GET_LOCK()及相关函数提供的接口不同。详情参见第 7.6.9.1 节，“锁定服务”。A different locking interface is available as either a plugin service or a set of loadable functions. This interface provides lock namespaces and distinct read and write locks, unlike the interface provided by GET_LOCK() and related functions. For details, see Section 7.6.9.1, “The Locking Service”.

IS_FREE_LOCK(str)

检查名为 str 的锁是否可用（即未被锁定）。如果锁可用（无人使用锁），返回 1；如果锁正在使用，返回 0；如果发生错误（例如参数不正确），返回 NULL。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).

该函数在基于语句的复制中不安全。如果在 binlog_format 设置为 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.

IS_USED_LOCK(str)

检查名为 str 的锁是否正在使用（即已锁定）。如果是，则返回持有该锁的客户端会话的连接标识符。否则，返回 NULL。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.

该函数在基于语句的复制中不安全。如果在 binlog_format 设置为 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.

RELEASE_ALL_LOCKS()

释放当前会话持有的所有命名锁，并返回释放的锁数量（如果没有则为 0）Releases all named locks held by the current session and returns the number of locks released (0 if there were none)

该函数在基于语句的复制中不安全。如果在 binlog_format 设置为 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.

RELEASE_LOCK(str)

释放由字符串 str 命名的锁，该锁是通过 GET_LOCK() 获得的。如果锁被释放，则返回 1；如果该锁不是由此线程建立的（在这种情况下锁不会被释放），则返回 0；如果指定名称的锁不存在，则返回 NULL。如果该锁从未通过调用 GET_LOCK() 获得，或者之前已经被释放，则该锁不存在。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.

DO 语句在与 RELEASE_LOCK() 一起使用时很方便。参见 第 15.2.3 节，“DO 语句”。The DO statement is convenient to use with RELEASE_LOCK(). See Section 15.2.3, “DO Statement”.

该函数在基于语句的复制中不安全。如果在 binlog_format 设置为 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.