29.3.1 The Locking Service

MySQL distributions 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 29.3, “MySQL Services for Plugins”. For general information about user-defined functions, see Section 29.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 ns1 and ns2, respectively, each application can use lock names lock1 and lock2 without 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 ER_LOCKING_SERVICE_WRONG_NAME error.
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_TIMEOUT error 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_DEADLOCK error. 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 ER_LOCKING_SERVICE_TIMEOUT or ER_LOCKING_SERVICE_DEADLOCK error.
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 GET_LOCK() and related SQL functions (see Section 12.14, “Locking Functions”). For example, GET_LOCK() does not implement namespaces and provides only exclusive locks, not distinct read and write locks.

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-locking-service.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut