MySQL Bugs: #93043: MySQL Manual describes get_lock() behavior in a wrong/unclear way

Bug #93043	MySQL Manual describes get_lock() behavior in a wrong/unclear way
Submitted:	1 Nov 2018 14:45	Modified:	16 Nov 2018 14:26
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GET_LOCK, metadata_locks, missing manual, performance schema

Description:
Fine MySQL manual (https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_get-lock) says:

"A lock obtained with GET_LOCK() is released explicitly by executing RELEASE_LOCK() or implicitly when your session terminates (either normally or abnormally). Lock release may also occur with another call to GET_LOCK():

GET_LOCK() is implemented using the metadata locking (MDL) subsystem. Multiple simultaneous locks can be acquired and GET_LOCK() does not release any existing locks. It is even possible for a given session to acquire multiple locks for the same name. Other sessions cannot acquire a lock with that name until the acquiring session releases all its locks for the name.

Locks acquired with GET_LOCK() appear in the Performance Schema metadata_locks table. The OBJECT_TYPE column says USER LEVEL LOCK and the OBJECT_NAME column indicates the lock name."

I see two problems with the above:

1. It states that "Lock release may also occur with another call to GET_LOCK()" and later "... and GET_LOCK() does not release any existing locks". I see clear contradiction here.

2. It says "It is even possible for a given session to acquire multiple locks for the same name". I do not see this happening in metadata_locks, see my test below.

manual should be clarified/fixed and/or implementation should be changed so that multiple locks with the same name for the same session become real.

How to repeat:
Do the following simple test with MySQL 8.0.x or any other version with MDLs used to implement get_locks():

openxs@ao756:~/dbs/8.0$ bin/mysql -uroot --socket=/tmp/mysql8.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.12 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select get_lock('lock1', 100);
+------------------------+
| get_lock('lock1', 100) |
+------------------------+
| 1 |
+------------------------+
1 row in set (0,01 sec)

One may expect 2 locks with the same name based on the manual, but we can see in the same or another session:

mysql> select * from performance_schema.metadata_locks where object_name='lock1'\G
*************************** 1. row ***************************
OBJECT_TYPE: USER LEVEL LOCK
OBJECT_SCHEMA: NULL
OBJECT_NAME: lock1
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 88217136
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: item_func.cc:4516
OWNER_THREAD_ID: 45
OWNER_EVENT_ID: 3
1 row in set (0,00 sec)

Now if we create another one with different name, we see:

mysql> select * from performance_schema.metadata_locks where object_name like 'lock%'\G
*************************** 1. row ***************************
OBJECT_TYPE: USER LEVEL LOCK
OBJECT_SCHEMA: NULL
OBJECT_NAME: lock1
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 88217136
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: item_func.cc:4516
OWNER_THREAD_ID: 45
OWNER_EVENT_ID: 3
*************************** 2. row ***************************
OBJECT_TYPE: USER LEVEL LOCK
OBJECT_SCHEMA: NULL
OBJECT_NAME: lock2
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 83599792
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: item_func.cc:4516
OWNER_THREAD_ID: 45
OWNER_EVENT_ID: 5
2 rows in set (0,01 sec)

mysql> select get_lock('lock2', 100); +------------------------+
| get_lock('lock2', 100) |
+------------------------+
| 1 |
+------------------------+
1 row in set (0,00 sec)

mysql> select * from performance_schema.metadata_locks where object_name like 'lock%'\G
*************************** 1. row ***************************
OBJECT_TYPE: USER LEVEL LOCK
OBJECT_SCHEMA: NULL
OBJECT_NAME: lock1
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 88217136
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: item_func.cc:4516
OWNER_THREAD_ID: 45
OWNER_EVENT_ID: 3
*************************** 2. row ***************************
OBJECT_TYPE: USER LEVEL LOCK
OBJECT_SCHEMA: NULL
OBJECT_NAME: lock2
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 83599792
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: item_func.cc:4516
OWNER_THREAD_ID: 45
OWNER_EVENT_ID: 5
2 rows in set (0,00 sec)

So, I see no locks released and re-acquired again, and I do not see multiple locks with the same name for the same session, as one may expect from the manual.

What I am doing wrong?

Suggested fix:
Fix the manual to describe current implementation of GET_LOCK() properly, or fix implementation if manual describes the intended behavior by design.

Hi,

Thank you for your bug report.

I truly agree with your analysis. Such text would puzzle me as well. It does require clarification and explanation.

Verified as reported.

Posted by developer:
 
Comments from Dmitry Lenev about the observations in the bug report:

Yes, this is the way it is implemented. Subsequent calls to GET_LOCK()
function simply increment counter in auxiliary struct in user-level
locks implementation, they don't even reach MDL subsystem.

So this is expected that multiple acquisitions of the same user-level
lock by connection are visible as a single MDL lock acquistion in P_S.

What you observe is expected behavior that corresponds to the way the
feature was designed/implemented.

Unfortunately, I don't think there is a good way for user to find out
how many recursive user-level lock acquisitions of the same lock by the
same connection has happened.

If you think that this is significant/serious problem the please report
a separate feature request about this. We can change user-level lock
implementation or invent some extension to P_S to solve this issue.

Posted by developer:
 
I've removed the contradictory statement that GET_LOCK() releases locks. Thanks for spotting that.

Regarding the single entry in the metadata_locks table for multiple locks on the same name, see previous comment. I've revised the GET_LOCK() description to take that into account:

"
Uniquely named locks acquired with GET_LOCK() appear in the
Performance Schema metadata_locks table. The OBJECT_TYPE column says
USER LEVEL LOCK and the OBJECT_NAME column indicates the lock name.
In the case that multiple locks are acquired for the same name, only
the first lock for the name registers a row in the metadata_locks
table. Subsequent locks for the name increment a counter in the lock
but do not acquire additional metadata locks. The metadata_locks row
for the lock is deleted when the last lock instance on the name is
released.
"

If inability to see all instances of a lock on a name is an significant
issue, please file a separate feature request referencing Bug#93043.
Thanks.