28.3.1.2 The Locking Service UDF Interface

The locking service routines described in Section 28.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.6.2, “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, adjusting the .so suffix for your platform as necessary:

CREATE FUNCTION service_get_read_locks RETURNS INT
  SONAME 'locking_service.so';
CREATE FUNCTION service_get_write_locks RETURNS INT
  SONAME 'locking_service.so';
CREATE FUNCTION service_release_locks RETURNS INT
  SONAME 'locking_service.so';

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:

DROP FUNCTION service_get_read_locks;
DROP FUNCTION service_get_write_locks;
DROP FUNCTION service_release_locks;

Before using the locking service UDFs, install them according to the instructions provided at Section 28.3.1.2.1, “Installing or Uninstalling the UDF Locking Interface”.

To acquire one or more read locks, call this function:

mysql> SELECT service_get_read_locks('mynamespace', 'rlock1', 'rlock2', 10);
+---------------------------------------------------------------+
| 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:

mysql> SELECT service_get_write_locks('mynamespace', 'wlock1', 'wlock2', 10);
+----------------------------------------------------------------+
| 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:

mysql> SELECT service_release_locks('mynamespace');
+--------------------------------------+
| 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:

mysql> SELECT service_get_read_locks('mynamespace', '', 10);
ERROR 3131 (42000): Incorrect locking service lock name ''.

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:

SELECT service_get_write_locks('ns', 'lock1', 'lock1', 'lock1', 0);
SELECT service_get_read_locks('ns', 'lock1', 'lock1', 'lock1', 0);

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 28.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:

SELECT service_get_write_locks('ns', 'lock1', 'lock2', 0) FROM t1 WHERE ... ;

INSERT INTO ... SELECT service_get_write_locks('ns', t1.col_name, 0) FROM t1;

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:

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
    -> WHERE NAME = 'wait/lock/metadata/sql/mdl';

Then acquire some locks and check the contents of the metadata_locks table:

mysql> SELECT service_get_write_locks('mynamespace', 'lock1', 0);
+----------------------------------------------------+
| service_get_write_locks('mynamespace', 'lock1', 0) |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
mysql> SELECT service_get_read_locks('mynamespace', 'lock2', 0);
+---------------------------------------------------+
| service_get_read_locks('mynamespace', 'lock2', 0) |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS
    -> FROM performance_schema.metadata_locks
    -> WHERE OBJECT_TYPE = 'LOCKING SERVICE'\G
*************************** 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 28.3.1.2.2, “Using the UDF Locking Interface”.

The functions share these characteristics:

These locking service UDFs are available: