Distributions of MySQL 5.7.8 or higher provide a locking interface that is available at two levels:
As a C language interface, callable as a plugin service from server plugins or user-defined functions
At the SQL level, as a set of user-defined functions that map onto calls to the service routines
For general information about plugin services, see Section 27.3, “MySQL Services for Plugins”. For general information about user-defined functions, see Section 27.4.2, “Adding a New User-Defined Function”.
The locking interface has these characteristics:
Locks have three attributes: Lock namespace, lock name, and lock mode:
Locks are identified by the combination of namespace and lock name. The namespace enables different applications to use the same lock names without colliding by creating locks in separate namespaces. For example, if applications A and B use namespaces of
ns2, respectively, each application can use lock names
lock2without interfering with the other application.
A lock mode is either read or write. Read locks are shared: If a session has a read lock on a given lock identifier, other sessions can acquire a read lock on the same identifier. Write locks are exclusive: If a session has a write lock on a given lock identifier, other sessions cannot acquire a read or write lock on the same identifier.
Namespace and lock names must be non-
NULL, nonempty, and have a maximum length of 64 characters. A namespace or lock name specified as
NULL, the empty string, or a string longer than 64 characters results in an
The locking interface treats namespace and lock names as binary strings, so comparisons are case sensitive.
The locking interface provides functions to acquire locks and release locks. No special privilege is required to call these functions. Privilege checking is the responsibility of the calling application.
Locks can be waited for if not immediately available. Lock acquisition calls take an integer timeout value that indicates how many seconds to wait to acquire locks before giving up. If the timeout is reached without successful lock acquisition, an
ER_LOCKING_SERVICE_TIMEOUTerror occurs. If the timeout is 0, there is no waiting and the call produces an error if locks cannot be acquired immediately.
The locking interface detects deadlock between lock-acquisition calls in different sessions. In this case, the locking service chooses a caller and terminates its lock-acquisition request with an
ER_LOCKING_SERVICE_DEADLOCKerror. This error does not cause transactions to roll back. To choose a session in case of deadlock, the locking service prefers sessions that hold read locks over sessions that hold write locks.
A session can acquire multiple locks with a single lock-acquisition call. For a given call, lock acquisition is atomic: The call succeeeds if all locks are acquired. If acquisition of any lock fails, the call acquires no locks and fails, typically with an
A session can acquire multiple locks for the same lock identifier (namespace and lock name combination). These lock instances can be read locks, write locks, or a mix of both.
Locks acquired within a session are released explicitly by calling a release-locks function, or implicitly when the session terminates (either normally or abnormally). Locks are not released when transactions commit or roll back.
Within a session, all locks for a given namespace when released are released together.
The interface provided by the locking service is distinct from
that provided by
related SQL functions (see
Section 13.20, “Miscellaneous Functions”). For example,
GET_LOCK() does not implement
namespaces and provides only exclusive locks, not distinct read
and write locks.