Bug #104396 The MDL_LOCK display of the 'lock table read local' on metadata_locks.
Submitted: 23 Jul 13:06 Modified: 26 Jul 12:13
Reporter: deng yl Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[23 Jul 13:06] deng yl
Description:
The MDL_LOCK display of the 'flush table read local' on metadata_locks is different from the actual granted.

The 'flush table read local' execute on innodb engine table get MDL_LOCK process as follow:  

At first get the MDL_SHARED_READ, but the innodb is HA_NO_READ_LOCAL_LOCK
so upgrade to MDL_SHARED_READ_ONLY. 

But the MDL_context::upgrade_shared_lock only change the type of the ticket, 
not change the m_psi of the ticket.

How to repeat:
The test case is as follows´╝Ü

step 1 on mysql client:

session 1:
create table t1(c1 int, c2 int);

mysql> lock table t1 read local;

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              13 |
+-----------------+

session 2:
mysql> select LOCK_TYPE, LOCK_STATUS from performance_schema.metadata_locks where OBJECT_NAME = 't1'\G
*************************** 1. row ***************************
LOCK_TYPE: SHARED_READ
LOCK_STATUS: GRANTED

+-------------+-------------+
| LOCK_TYPE   | LOCK_STATUS |
+-------------+-------------+
| SHARED_READ | GRANTED     |
+-------------+-------------+

mysql> select THREAD_OS_ID from performance_schema.threads where PROCESSLIST_ID = 13\G
*************************** 1. row ***************************
THREAD_OS_ID: 8225

step 2 one shell and gdb:

gdb attach 8114 //gdb attach ${mysql_process_pid} 

(gdb) info threads

(gdb) thread 37 

//switch to the thread execute 'lock table t1 read local' 

(gdb) f 11 
// switch to the stack do_command.

(gdb) p thd->mdl_context->m_ticket_store.m_durations[2].m_ticket_list->m_first->m_type
$11 = MDL_SHARED_READ_ONLY

(gdb) p (enum enum_mdl_type)((PFS_metadata_lock *) thd->mdl_context->m_ticket_store.m_durations[2].m_ticket_list->m_first->m_psi)->m_mdl_type
$17 = MDL_SHARED_READ
[23 Jul 13:23] deng yl
the error statement is 'lock table read local'
[26 Jul 12:13] MySQL Verification Team
Hi Mr. yi,

Thank you for your bug report.

However, it is not a bug.

Metadata lock and InnoDB locks are at two totally different levels in our server. InnoDB SE has to wait on the metadata lock, but it has its own set of locks for this and other purposes.

In short, this is expected behaviour and is explained in our Reference Manual.

Not a bug.