Rechercher dans le manuel MySQL
29.3.1.2 The Locking Service UDF Interface
This section describes how to use the locking service user-defined function (UDF) interface. To use the C language interface instead, see Section 29.3.1.1, “The Locking Service C Interface” For general characteristics of the locking service interface, see Section 29.3.1, “The Locking Service”. For general information about user-defined functions, see Section 29.4.2, “Adding a New User-Defined Function”.
29.3.1.2.1 Installing or Uninstalling the UDF Locking Interface
The locking service routines described in Section 29.3.1.1, “The Locking Service C Interface” need not be installed because they are built into the server. The same is not true of the user-defined functions (UDFs) that map onto calls to the service routines: The UDFs must be installed before use. This section describes how to do that. For general information about UDF installation, see Section 5.7.1, “Installing and Uninstalling User-Defined Functions”.
The locking service UDFs are implemented in a plugin library
file located in the directory named by the
plugin_dir
system variable.
The file base name is locking_service
.
The file name suffix differs per platform (for example,
.so
for Unix and Unix-like systems,
.dll
for Windows).
To install the locking service UDFs, use the
CREATE FUNCTION
statement
(adjust the .so
suffix for your
platform as necessary):
If the UDFs are used on a master replication server, install them on all slave servers as well to avoid replication problems.
Once installed, the UDFs remain installed until uninstalled.
To remove them, use the DROP
FUNCTION
statement:
Before using the locking service UDFs, install them according to the instructions provided at Section 29.3.1.2.1, “Installing or Uninstalling the UDF Locking Interface”.
To acquire one or more read locks, call this function:
- +---------------------------------------------------------------+
- | service_get_read_locks('mynamespace', 'rlock1', 'rlock2', 10) |
- +---------------------------------------------------------------+
- | 1 |
- +---------------------------------------------------------------+
The first argument is the lock namespace. The final argument is an integer timeout indicating how many seconds to wait to acquire the locks before giving up. The arguments in between are the lock names.
For the example just shown, the function acquires locks with
lock identifiers (mynamespace, rlock1)
and (mynamespace, rlock2)
.
To acquire write locks rather than read locks, call this function:
- +----------------------------------------------------------------+
- | service_get_write_locks('mynamespace', 'wlock1', 'wlock2', 10) |
- +----------------------------------------------------------------+
- | 1 |
- +----------------------------------------------------------------+
In this case, the lock identifiers are
(mynamespace, wlock1)
and
(mynamespace, wlock2)
.
To release all locks for a namespace, use this function:
- +--------------------------------------+
- | service_release_locks('mynamespace') |
- +--------------------------------------+
- | 1 |
- +--------------------------------------+
Each locking function returns nonzero for success. If the function fails, an error occurs. For example, the following error occurs because lock names cannot be empty:
A session can acquire multiple locks for the same lock identifier. As long as a different session does not have a write lock for an identifier, the session can acquire any number of read or write locks. Each lock request for the identifier acquires a new lock. The following statements acquire three write locks with the same identifier, then three read locks for the same identifier:
If you examine the Performance Schema
metadata_locks
table at this point, you
will find that the session holds six distinct locks with the
same (ns, lock1)
identifier. (For
details, see Section 29.3.1.2.3, “Locking Service Monitoring”.)
Because the session holds at least one write lock on
(ns, lock1)
, no other session can acquire
a lock for it, either read or write. If the session held
only read locks for the identifier, other sessions could
acquire read locks for it, but not write locks.
Locks for a single lock-acquisition call are acquired
atomically, but atomicity does not hold across calls. Thus,
for a statement such as the following, where
service_get_write_locks()
is called once
per row of the result set, atomicity holds for each
individual call, but not for the statement as a whole:
Because the locking service returns a separate lock for each successful request for a given lock identifier, it is possible for a single statement to acquire a large number of locks. For example:
These types of statements may have certain adverse effects. For example, if the statement fails part way through and rolls back, locks acquired up to the point of failure will still exist. If the intent is for there to be a correspondence between rows inserted and locks acquired, that intent will not be satisfied. Also, if it is important that locks are granted in a certain order, be aware that result set order may differ depending on which execution plan the optimizer chooses. For these reasons, it may be best to limit applications to a single lock-acquisition call per statement.
The locking service is implemented using the MySQL Server
metadata locks framework, so you monitor locking service
locks acquired or waited for by examining the Performance
Schema metadata_locks
table.
First, enable the metadata lock instrument:
Then acquire some locks and check the contents of the
metadata_locks
table:
- +----------------------------------------------------+
- | service_get_write_locks('mynamespace', 'lock1', 0) |
- +----------------------------------------------------+
- | 1 |
- +----------------------------------------------------+
- +---------------------------------------------------+
- | service_get_read_locks('mynamespace', 'lock2', 0) |
- +---------------------------------------------------+
- | 1 |
- +---------------------------------------------------+
- -> FROM performance_schema.metadata_locks
- *************************** 1. row ***************************
- OBJECT_TYPE: LOCKING SERVICE
- OBJECT_SCHEMA: mynamespace
- OBJECT_NAME: lock1
- LOCK_TYPE: EXCLUSIVE
- LOCK_STATUS: GRANTED
- *************************** 2. row ***************************
- OBJECT_TYPE: LOCKING SERVICE
- OBJECT_SCHEMA: mynamespace
- OBJECT_NAME: lock2
- LOCK_TYPE: SHARED
- LOCK_STATUS: GRANTED
Locking service locks have an OBJECT_TYPE
value of LOCKING SERVICE
. This is
distinct from, for example, locks acquired with the
GET_LOCK()
function, which
have an OBJECT_TYPE
of USER
LEVEL LOCK
.
The lock namespace, name, and mode appear in the
OBJECT_SCHEMA
,
OBJECT_NAME
, and
LOCK_TYPE
columns. Read and write locks
have LOCK_TYPE
values of
SHARED
and EXCLUSIVE
,
respectively.
The LOCK_STATUS
value is
GRANTED
for an acquired lock,
PENDING
for a lock that is being waited
for. You will see PENDING
if one session
holds a write lock and another session is attempting to
acquire a lock having the same identifier.
The SQL interface to the locking service implements the user-defined functions described in this section. For usage examples, see Section 29.3.1.2.2, “Using the UDF Locking Interface”.
The functions share these characteristics:
The return value is nonzero for success. Otherwise, an error occurs.
Namespace and lock names must be non-
NULL
, nonempty, and have a maximum length of 64 characters.Timeout values must be integers indicating how many seconds to wait to acquire locks before giving up with an error. If the timeout is 0, there is no waiting and the function produces an error if locks cannot be acquired immediately.
These locking service UDFs are available:
service_get_read_locks(
namespace
,lock_name
[,lock_name
] ...,timeout
)Acquires one or more read (shared) locks in the given namespace using the given lock names, timing out with an error if the locks are not acquired within the given timeout value.
service_get_write_locks(
namespace
,lock_name
[,lock_name
] ...,timeout
)Acquires one or more write (exclusive) locks in the given namespace using the given lock names, timing out with an error if the locks are not acquired within the given timeout value.
service_release_locks(
namespace
)For the given namespace, releases all locks that were acquired within the current session using
service_get_read_locks()
andservice_get_write_locks()
.It is not an error for there to be no locks in the namespace.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-locking-service-udf-interface.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.