GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the stringstr, using a timeout oftimeoutseconds. A negativetimeoutvalue means infinite timeout. The lock is exclusive. While held by one session, other sessions cannot obtain a lock of the same name.

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

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

In MySQL 5.7,GET_LOCK()was reimplemented using the metadata locking (MDL) subsystem and its capabilities were extended. Multiple simultaneous locks can be acquired andGET_LOCK()does not release any existing locks.

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.

As a result of the MDL reimplementation, uniquely named locks acquired withGET_LOCK()appear in the Performance Schemametadata_locks表格TheOBJECT_TYPEcolumn saysUSER LEVEL LOCKand theOBJECT_NAMEcolumn indicates the lock name. In the case that multiple locks are acquired for thesamename, only the first lock for the name registers a row in themetadata_locks表格Subsequent locks for the name increment a counter in the lock but do not acquire additional metadata locks. Themetadata_locksrow 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 anER_USER_LOCK_DEADLOCKerror. This error does not cause transactions to roll back.

Before MySQL 5.7, only a single simultaneous lock can be acquired andGET_LOCK()releases any existing lock. The difference in lock acquisition behavior as of MySQL 5.7 can be seen by the following example. Suppose that you execute these statements:

选择GET_LOCK (lock1, 10);选择GET_LOCK(“锁2',10); SELECT RELEASE_LOCK('lock2'); SELECT RELEASE_LOCK('lock1');

在MySQL 5.7或更高版本,第二个GET_LOCK()acquires a second lock and bothRELEASE_LOCK()calls return 1 (success). Before MySQL 5.7, the secondGET_LOCK()releases the first lock ('lock1')and the secondRELEASE_LOCK()returnsNULL(failure) because there is no'lock1'to release.

MySQL 5.7 and later enforces a maximum length on lock names of 64 characters. Previously, no limit was enforced.

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 formdb_name.strorapp_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()is unsafe for statement-based replication. A warning is logged if you use this function whenbinlog_formatis set toSTATEMENT.

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

A different locking interface is available as either a plugin service or a set of user-defined functions. This interface provides lock namespaces and distinct read and write locks, unlike the interface provided byGET_LOCK()and related functions. For details, seeSection 5.5.6.1, “The Locking Service”.

IS_FREE_LOCK(str)

Checks whether the lock namedstris free to use (that is, not locked). Returns1if the lock is free (no one is using the lock),0if the lock is in use, andNULLif 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 whenbinlog_formatis set toSTATEMENT.

IS_USED_LOCK(str)

Checks whether the lock namedstris in use (that is, locked). If so, it returns the connection identifier of the client session that holds the lock. Otherwise, it returnsNULL.

This function is unsafe for statement-based replication. A warning is logged if you use this function whenbinlog_formatis set toSTATEMENT.

RELEASE_ALL_LOCKS()

Releases all named locks held by the current session and returns the number of locks released (0 if there were none)

This function is unsafe for statement-based replication. A warning is logged if you use this function whenbinlog_formatis set toSTATEMENT.

RELEASE_LOCK(str)

Releases the lock named by the stringstrthat was obtained withGET_LOCK(). Returns1if the lock was released,0if the lock was not established by this thread (in which case the lock is not released), andNULLif the named lock did not exist. The lock does not exist if it was never obtained by a call toGET_LOCK()or if it has previously been released.

TheDOstatement is convenient to use withRELEASE_LOCK(). SeeSection 13.2.3, “DO Statement”.

This function is unsafe for statement-based replication. A warning is logged if you use this function whenbinlog_formatis set toSTATEMENT.