MySQL Plugin Services 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); SELECT service_get_write_locks(’ns’, ’lock1’, ’lock2’, 0) FROM t1 WHERE ... ; Caution Locking Service Monitoring 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>  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: First, enable the metadata lock instrument: 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. 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 still exist. If the intent is for there to be a correspondence between rows inserted and locks acquired, that intent is not 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. INSERT INTO ... SELECT service_get_write_locks(’ns’, t1.col_name, 0) FROM t1; 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: 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 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. If you examine the Performance Schema  metadata_locks  table at this point, you should find that the session holds six distinct locks with the same  (ns, lock1)  identifier. (For details, see Locking Service Monitoring.) 1139