| Bug #109402 | metadata_locks is inaccurate after upgrade_shared_lock | ||
|---|---|---|---|
| Submitted: | 16 Dec 2022 8:23 | Modified: | 24 Feb 2023 10:14 |
| Reporter: | Zhejun Cai | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S4 (Feature request) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[16 Dec 2022 14:02]
MySQL Verification Team
Hi Mr. CAi, Thank you for your bug report. However, we were not able to repeat what you report with our latest patch-fix release, 8.0.31. This is the output that we get: +-------------------+--------------------+----------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------------+--------------------+----------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ | TABLE | performance_schema | metadata_locks | NULL | 105553119118912 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6085 | 49 | 7 | | SCHEMA | performance_schema | NULL | NULL | 105553119119792 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dd_schema.cc:108 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | column_name | 105553118732176 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | lock_duration | 105553118732096 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | lock_status | 105553118731936 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | lock_type | 105553118732016 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_instance_begin | 105553118731776 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_name | 105553118731856 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_schema | 105553118731616 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_type | 105553118731696 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | owner_event_id | 105553118731456 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | owner_thread_id | 105553118731536 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | | COLUMN STATISTICS | performance_schema | metadata_locks | source | 105553118731296 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 49 | 7 | +-------------------+--------------------+----------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ Can't repeat.
[15 Feb 2023 9:36]
Zhejun Cai
Please confirm the steps to reproduce, because the content of the above reply is only about performance_schema, there is no OBJECT_NAME=t_bit2, which is generated by the statement : create table test.tt_bit2( a int, b int) start transaction; How to repeat: session 1 create database if not exists test; create table test.tt_bit2( a int, b int) start transaction; session 2 select * from performance_schema.metadata_locks;
[15 Feb 2023 12:56]
MySQL Verification Team
Hi Mr. Cai, We have run the test once again and this is what we get: +-------------------+--------------------+----------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------------+--------------------+----------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ | TABLE | performance_schema | metadata_locks | NULL | 105553161552592 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6085 | 50 | 3 | | SCHEMA | performance_schema | NULL | NULL | 105553161552432 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dd_schema.cc:108 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | column_name | 105553162004560 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | lock_duration | 105553162006640 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | lock_status | 105553162004480 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | lock_type | 105553162009280 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_instance_begin | 105553162009920 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_name | 105553162006960 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_schema | 105553162004960 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_type | 105553162004800 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | owner_event_id | 105553162005840 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | owner_thread_id | 105553162005280 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | source | 105553162004880 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | +-------------------+--------------------+----------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ Everything is at should be. There is no reason to have exclusive lock on that table, since there are no conditions that would require that. Not a bug.
[16 Feb 2023 3:33]
Zhejun Cai
First, my result is different with yours, here is my result on mysql 8.0.31(build from official source, without any modifications) session 1: mysql> create database if not exists test; Query OK, 1 row affected, 1 warning (0.01 sec) mysql> create table test.tt_bit2( a int, b int) start transaction; Query OK, 0 rows affected (0.01 sec) session 2: mysql> select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | GLOBAL | NULL | NULL | NULL | 46971843143120 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5475 | 73 | 9 | | BACKUP LOCK | NULL | NULL | NULL | 46971842834320 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5482 | 73 | 9 | | SCHEMA | test | NULL | NULL | 46971843141120 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5462 | 73 | 9 | | TABLE | test | tt_bit2 | NULL | 46971843225136 | SHARED | TRANSACTION | GRANTED | sql_parse.cc:6084 | 73 | 9 | | GLOBAL | NULL | NULL | NULL | 46971842769216 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:474 | 73 | 9 | | TABLESPACE | NULL | test/tt_bit2 | NULL | 46971842767232 | EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:470 | 73 | 9 | | TABLE | performance_schema | metadata_locks | NULL | 46971977168736 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6084 | 74 | 10 | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ 7 rows in set (0.00 sec) Secondly, after setting break point at MDL_context::upgrade_shared_lock by gdb, then execute the query "create table test.tt_bit2( a int, b int) start transaction;", i got the backtrace, as below: Thread 63 "connection" hit Breakpoint 1, MDL_context::upgrade_shared_lock () at /data3/caizj/source/mysql-8.0.31/sql/mdl.cc:3739 3739 MDL_request mdl_new_lock_request; (gdb) where #0 MDL_context::upgrade_shared_lock () at /data3/caizj/source/mysql-8.0.31/sql/mdl.cc:3739 #1 0x0000000000e75807 in open_table () at /data3/caizj/source/mysql-8.0.31/sql/sql_base.cc:3120 #2 0x0000000000e771cf in open_and_process_table () at /data3/caizj/source/mysql-8.0.31/sql/sql_base.cc:5051 #3 open_tables () at /data3/caizj/source/mysql-8.0.31/sql/sql_base.cc:5854 #4 0x0000000000fb3c4e in open_tables () at /data3/caizj/source/mysql-8.0.31/sql/sql_base.h:455 #5 mysql_create_table () at /data3/caizj/source/mysql-8.0.31/sql/sql_table.cc:10044 #6 0x00000000013e1ec2 in Sql_cmd_create_table::execute () at /data3/caizj/source/mysql-8.0.31/sql/sql_cmd_ddl_table.cc:433 #7 0x0000000000f09366 in mysql_execute_command () at /data3/caizj/source/mysql-8.0.31/sql/sql_parse.cc:4677 #8 0x0000000000f0e0a4 in dispatch_sql_command () at /data3/caizj/source/mysql-8.0.31/sql/sql_parse.cc:5312 #9 0x0000000000f1032c in dispatch_command () at /data3/caizj/source/mysql-8.0.31/sql/sql_parse.cc:2032 #10 0x0000000000f115e7 in do_command () at /data3/caizj/source/mysql-8.0.31/sql/sql_parse.cc:1435 #11 0x0000000001062818 in handle_connection () at /data3/caizj/source/mysql-8.0.31/sql/conn_handler/connection_handler_per_thread.cc:302 #12 0x00000000027b167d in pfs_spawn_thread () at /data3/caizj/source/mysql-8.0.31/storage/perfschema/pfs.cc:2986 #13 0x00002ab7f3390ea5 in start_thread () from /lib64/libpthread.so.0 #14 0x00002ab7f4f15b0d in clone () from /lib64/libc.so.6 and the code sippets at sql_base.cc:3120 3120 bool wait_result = thd->mdl_context.upgrade_shared_lock( 3121 table_list->mdl_request.ticket, MDL_EXCLUSIVE, 3122 thd->variables.lock_wait_timeout); So i am sure that it holds MDL_EXCLUSIVE.
[16 Feb 2023 13:21]
MySQL Verification Team
Hi, I get the following output: +-------------------+--------------------+----------------+-----------------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------------+--------------------+----------------+-----------------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | GLOBAL | NULL | NULL | NULL | 105553136240032 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5476 | 49 | 4 | | BACKUP LOCK | NULL | NULL | NULL | 105553136243312 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5483 | 49 | 4 | | SCHEMA | test | NULL | NULL | 105553136243392 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5463 | 49 | 4 | | TABLE | test | tt_bit2 | NULL | 105553136243152 | SHARED | TRANSACTION | GRANTED | sql_parse.cc:6085 | 49 | 4 | | GLOBAL | NULL | NULL | NULL | 105553136888528 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:475 | 49 | 4 | | TABLESPACE | NULL | test/tt_bit2 | NULL | 105553136882048 | EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:471 | 49 | 4 | | TABLE | performance_schema | metadata_locks | NULL | 105553136887408 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6085 | 50 | 3 | | SCHEMA | performance_schema | NULL | NULL | 105553136881728 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dd_schema.cc:108 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | column_name | 105553136900272 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | lock_duration | 105553136900592 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | lock_status | 105553136905632 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | lock_type | 105553136906192 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_instance_begin | 105553136906112 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_name | 105553136907792 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_schema | 105553136899952 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | object_type | 105553136900112 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | owner_event_id | 105553136904672 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | owner_thread_id | 105553136904592 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | | COLUMN STATISTICS | performance_schema | metadata_locks | source | 105553136904832 | SHARED_READ | STATEMENT | GRANTED | sql_base.cc:596 | 50 | 3 | +-------------------+--------------------+----------------+-----------------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ That means that there is exclusive lock, which is OK, since you used unusual method of starting transaction.
[17 Feb 2023 9:21]
Zhejun Cai
It holds SHARED, not EXCLUSIVE | TABLE | test | tt_bit2 | NULL | 105553136243152 | SHARED | TRANSACTION | GRANTED | sql_parse.cc:6085 | 49 | 4 | Let me give two more cases to elaborate the issue. CASE 1 Prerequisites: create database if not exists test; drop table if exists test.t1; create table test.t1(a int, b int) engine=INNODB; insert into test.t1 values(1,1); session 1: begin; select * from test.t1; session 2: begin; insert into test.t1 values(2,2); session 3: select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+ | TABLE | test | t1 | NULL | 46999022313728 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6084 | 69 | 15 | | TABLE | test | t1 | NULL | 46999895816544 | SHARED_WRITE | TRANSACTION | GRANTED | sql_parse.cc:6084 | 70 | 9 | | TABLE | performance_schema | metadata_locks | NULL | 46999961395008 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6084 | 71 | 4 | +-------------+--------------------+----------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+ 3 rows in set (0.00 sec) There is no conflict on MDL lock, session1(OWNER_THREAD_ID=69) holds the SHARED_READ, session2(OWNER_THREAD_ID=70) holds the SHARED_WRITE CASE 2 Prerequisites: create database if not exists test; drop table if exists test.t1; create table test.t1(a int, b int) engine=INNODB; insert into test.t1 values(1,1); session 1: LOCK TABLE test.t1 READ LOCAL session 2: insert into test.t1 values(2,2); session 3: select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ | TABLE | test | t1 | NULL | 46999022417136 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6084 | 69 | 20 | | GLOBAL | NULL | NULL | NULL | 46999894348704 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:3057 | 70 | 11 | | TABLE | test | t1 | NULL | 46999894349568 | SHARED_WRITE | TRANSACTION | PENDING | sql_parse.cc:6084 | 70 | 11 | | TABLE | performance_schema | metadata_locks | NULL | 46999961452352 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6084 | 71 | 5 | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+ 4 rows in set (0.01 sec) session2(OWNER_THREAD_ID=70) is PENDING , so which lock makes session2 pending? as the CASE1 tell me the MDL lock_type SHARED_READ and SHARED_WRITE are compatible Actually the session1 holds the MDL_SHARED_READ_ONLY instead of SHARED_READ, the code snippets is as follows 2785 bool result = thd->mdl_context.upgrade_shared_lock( 2786 table->table->mdl_ticket, MDL_SHARED_READ_ONLY, 2787 thd->variables.lock_wait_timeout); The MDL lock_type MDL_SHARED_READ_ONLY and MDL_SHARED_WRITE are not compatible, so session2 is PENDING. But performance_schema.metadata_locks doesn't output SHARED_READ_ONLY, as upgrade_shared_lock doesn't update the pfs_lock.
[17 Feb 2023 13:16]
MySQL Verification Team
Hi, We must admit that you have got us confused a bit .... First you are complaining that there is an exclusive lock, while it should not be ....... Now, you are complaining that it is a shared lock. It is possible that share lock is upgraded, but it is done in so many cases, by intention. In order to satisfy ACID and MVCC standards. Just let us know what is it exactly that you are complaining about. Both SHARED and EXCLUSIVE with the lock upgrade are intended behaviour.
[20 Feb 2023 2:29]
Zhejun Cai
Hi, SHARED upgrade to EXCLUSIVE or SHARED_READ upgrade to SHARED_READ_ONLY is no problem, but the monitor table metadata_locks does not output the correct result after upgrade. I always focus on the upgrade_shared_lock makes the monitor table performance_schema.metadata_locks inaccurate, just give two different scenes. In CASE2, i need to analyze which lock makes session2 wait by querying performance_schema.metadata_locks, As far as i know SHARED_WRITE and SHARED_READ are compatible, it makes me confuse. It makes sense if the metadata_locks output the upgrade result SHARED_READ_ONLY(session1, OWNER_THREAD_ID=69), as SHARED_READ_ONLY and SHARED_WRITE are not compatible.
[20 Feb 2023 13:12]
MySQL Verification Team
Hi Mr. Cai, We have repeated some of the behaviour that you have reported, but not all. Hence, it would be preferable that you make a test case, for 8.0.32, that would show the full behaviour, with all the stages. Next, it is essential to see whether there is a difference between the FULL InnoDB status and the monitor. This is essential. That means that after each stop holding locks should be presented, both by monitor and a FULL innodb status. That is mandatory in order to spot if there is a bug in the monitor in 8.0.32.
[21 Feb 2023 7:12]
Zhejun Cai
test case for performance_schema.metadata_locks when trigger upgrading mdl lock
Attachment: mdl_upgrade_table.test (application/octet-stream, text), 1.66 KiB.
[21 Feb 2023 7:23]
Zhejun Cai
Hi, Thank you for your advice, i make a test case for performance_schema.metadata_locks when trigger upgrading mdl lock. It's nothing about InnoDB, it's only about mdl lock, performance_schema.threads says the pending connection is Waiting for table metadata lock. My point is that the monitoring table performance_schema.metadata_locks does not update the lock type after the mdl lock upgrade from SHARED_READ to SHARED_READ_ONLY.
[21 Feb 2023 13:30]
MySQL Verification Team
Hi Mr. Cai, I have read the output of the test case and it is quite clear. In both situations, SHARED_READ and SHARED_WRITE lock are waiting on INTENTION_EXCLUSIVE lock that is granted to the same transaction that is waiting on SHARED_READ and SHARED_WRITE lock types. It is quite clear ...... That is intended behaviour.
[21 Feb 2023 13:36]
MySQL Verification Team
Mr. Cai,
We also have a present for you ...... Since you went into so much trouble, here is a compatibility matrix for MDL locks:
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
S | + + + + + + + + + - |
SH | + + + + + + + + + - |
SR | + + + + + + + + - - |
SW | + + + + + + - - - - |
SWLP | + + + + + + - - - - |
SU | + + + + + - + - - - |
SRO | + + + - - + + + - - |
SNW | + + + - - - + - - - |
SNRW | + + - - - - - - - - |
X | - - - - - - - - - - |
Here: "+" -- means that request can be satisfied
"-" -- means that request can't be satisfied and should wait
[22 Feb 2023 4:49]
Zhejun Cai
Hi,
Thank you for the compatibility matrix for MDL locks, This helps me state my point.
In your reply "SHARED_READ and SHARED_WRITE lock are waiting on INTENTION_EXCLUSIVE lock",
Did you think MDL SHARED_READ and SHARED_WRITE will wait on the MDL INTENTION_EXCLUSIVE ?
There is no INTENTION_EXCLUSIVE(IX) in the compatibility matrix that you given.
As far as i know, IX is the scoped lock, scoped lock matrix is about IS, IX, X, S.
I think this case is nothing about INTENTION_EXCLUSIVE.
In the case, metadata_locks output:
connection1 SHARED_READ granted,
connection2 INTENTION_EXCLUSIVE granted, SHARED_WRITE pending
Here is the contradiction,
according to the compatibility matrix, [SR, SW]="+" means that request can be satisfied,
Why connection2 is pending?
After connection1 UNLOCK TABLES, connection2 granted the SHARED_WRITE.
In fact, connection1 granted SHARED_READ_ONLY after upgrade mdl lock from SHARED_READ to SHARED_READ_ONLY,
but metadata_locks output SHARED_READ.
[SRO,SW]="-", That's clear , it's a bug on performance_schema.metadata_locks.
I can prove my point another way, i read mysql source code and debug with gdb ,
i can print the lock_type on that THD, it is MDL_SHARED_READ_ONLY.
(gdb) p *(thd->mdl_context->m_ticket_store->m_durations[2]->m_ticket_list.m_first)
$15 = {<MDL_wait_for_subgraph> = {_vptr.MDL_wait_for_subgraph = 0x80518e8 <vtable for MDL_ticket+16>, static DEADLOCK_WEIGHT_CO = 0, static DEADLOCK_WEIGHT_DML = 25, static DEADLOCK_WEIGHT_ULL = 50,
static DEADLOCK_WEIGHT_DDL = 100}, next_in_context = 0x0, prev_in_context = 0x2b170405cb50, next_in_lock = 0x0, prev_in_lock = 0x2b1704195960, m_type = MDL_SHARED_READ_ONLY, m_duration = MDL_EXPLICIT,
m_ctx = 0x2b170405cac0, m_lock = 0x2b1704195750, m_is_fast_path = false, m_hton_notified = false, m_psi = 0x2b160eae1e80}
[23 Feb 2023 4:50]
Zhejun Cai
Hi, Did you think IX lock can be regarded as X, and then use the compatibility matrix, [X, SR]='-' , but this does not make sense, IX in the test case(mdl_upgrade_table.test) i uploaded, it is granted, instead of pending, the pending lock is SW, they are in the same transaction.
[23 Feb 2023 15:11]
MySQL Verification Team
Hi, Just one more information for you. We agreed with Development that one more column is needed in that table so that it shows on which transaction / operation is the event waiting on. Because, after lot's of discussions, we agreed that SHR on t1 is waiting on SHW also on t1. That is not visible from that P_S table. Therefore, that P_S table needs to be changed so that everything is clearly visible. We had a long discussion with Development and agreed that metadata_locks needs two more columns. First column would be "Number" and would simply go from 1 to N and a vital column "Waiting on" .... which would imply that operation number X is waiting on operation number Y. So, from now on, this is a verified feature request in the Performance Schema. Afterr this feature being added, there will be no need for any deep analysis and wrong conclusions. Verified as a feature request for the Performance Schema table "metadata_locks".
[24 Feb 2023 10:14]
Zhejun Cai
Hi, This feature sounds good. I am looking forward to it, thank you. This conclusions you made: "Because, after lot's of discussions, we agreed that SHR on t1 is waiting on SHW also on t1." I think it is wrong, because it violates the rule of the compatibility matrix which you posted, ["SW,"SR"]="+". -- means that request can be satisfied I still insist that metadata_locks should output SHARED_READ_ONLY, instead of SHARED_READ. My conclusion is that SW on t1 is waiting on SRO also on t1, because there is a bug on metadata_lock after upgrading mdl lock. I have tried my best to prove that from multiple perspectives: 1. use proof by contradiction, the case(mdl_upgrade_table.test) and had posted my analysis. 2. gdb print the real lock_type on THD, it is SHARED_READ_ONLY (gdb) p thd->mdl_context->m_ticket_store->m_durations[2]->m_ticket_list.m_first->m_type $9 = MDL_SHARED_READ_ONLY 3. run the same case on MariaDB and MySQL, it is SHARED_READ_ONLY on MariadDB, and SHARED_READ on MySQL. CASE: use test; create table t1(a int, b int) engine=innodb; lock table t1 read local; MariaDB output: MariaDB [(none)]> install plugin METADATA_LOCK_INFO soname ‘metadata_lock_info.so’; MariaDB [(none)]> select * from information_schema.metadata_lock_info; +-----------+----------------------+---------------+---------------------+--------------+------------+ | THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME | +-----------+----------------------+---------------+---------------------+--------------+------------+ | 35 | MDL_SHARED_READ_ONLY | NULL | Table metadata lock | test | t1 | +-----------+----------------------+---------------+---------------------+--------------+------------+ 1 row in set (0.001 sec) MySQL output: mysql> select * from performance_schema .metadata_locks; +-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+ | TABLE | test | t1 | NULL | 47377852827232 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6093 | 70 | 15 | | TABLE | performance_schema | metadata_locks | NULL | 47377918519440 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6093 | 71 | 11 | +-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+ That's all.
[24 Feb 2023 13:29]
MySQL Verification Team
A diagnosis that I have sent you has been made by developers who created both MDL and that P_S.tables. A small difference in timing can change the picture entirely ..... If you think you know better then the entire development team, that is your right.
Description: OWNER_THREAD_ID=75 is pending, there is a TABLE test/tt_bit2 SHARED record in the metadata_locks where OWNER_THREAD_ID=72, but actually it's LOCK_TYPE should be EXCLUSIVE instead of SHARED mysql> select * from performance_schema .metadata_locks; +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+ | GLOBAL | NULL | NULL | NULL | 106034154324224 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5458 | 72 | 2 | | BACKUP LOCK | NULL | NULL | NULL | 106034154324112 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5465 | 72 | 2 | | SCHEMA | test | NULL | NULL | 106034154324000 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5445 | 72 | 2 | | TABLE | test | t_bit2 | NULL | 106034154323888 | SHARED | TRANSACTION | GRANTED | sql_parse.cc:5943 | 72 | 2 | | GLOBAL | NULL | NULL | NULL | 106034155138352 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:474 | 72 | 2 | | TABLESPACE | NULL | test/t_bit2 | NULL | 106034155138576 | EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:470 | 72 | 2 | | TABLE | test | t_bit2 | NULL | 106034155100944 | SHARED_READ | TRANSACTION | PENDING | table.h:2944 | 75 | 1 | | TABLE | performance_schema | metadata_locks | NULL | 106034154975168 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5943 | 74 | 14 | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+---------- here is gdb output, the m_type=MDL_EXCLUSIVE in ticket object, but m_mdl_type=1(MDL_SHARED) in PFS_metadata_lock #7 0x0000000003726cd1 in MDL_context::upgrade_shared_lock (this=this@entry=0x62600022b178, mdl_ticket=0x6070001a3fb0, new_type=new_type@entry=MDL_EXCLUSIVE, lock_wait_timeout=<optimized out>) at /data/caizj/source/aidb_v5_compile/sql/mdl.cc:3835 3835 MDL_ticket::destroy(mdl_new_lock_request.ticket); (gdb) p mdl_ticket->m_psi $18 = (PSI_metadata_lock *) 0x2b3ec22f8000 (gdb) p (PFS_metadata_lock*)$18 $19 = (PFS_metadata_lock *) 0x2b3ec22f8000 Thread 103 "connection" hit Breakpoint 5, pfs_lock::is_populated (this=this@entry=0x2b3ec22e1800) at /data/caizj/source/mysql/storage/perfschema/pfs_lock.h:187 187 uint32 copy = m_version_state.load(); (gdb) p $19->m_lock $23 = {m_version_state = {<std::__atomic_base<unsigned int>> = {static _S_alignment = 4, _M_i = 22}, static is_always_lock_free = true}} (gdb) p *$19 $24 = {<PFS_instr> = {m_lock = {m_version_state = {<std::__atomic_base<unsigned int>> = {static _S_alignment = 4, _M_i = 22}, static is_always_lock_free = true}}, m_enabled = true, m_timed = true, m_page = 0x6080000520a0}, m_identity = 0x6070001a3fb0, m_mdl_key = {m_length = 13, m_db_name_length = 4, m_object_name_length = 6, m_ptr = "\004test\000t_bit2\000partitions\000e\000ents", '\000' <repeats 356 times>, static m_namespace_to_wait_state_name = {{m_key = 102, m_name = 0x77bef00 "Waiting for global read lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 103, m_name = 0x77bef40 "Waiting for backup lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 104, m_name = 0x77bef80 "Waiting for tablespace metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 105, m_name = 0x77befe0 "Waiting for schema metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 106, m_name = 0x77bf040 "Waiting for table metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 107, m_name = 0x77bf080 "Waiting for stored function metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 108, m_name = 0x77bf0e0 "Waiting for stored procedure metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 109, m_name = 0x77bf140 "Waiting for trigger metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 110, m_name = 0x77bf1a0 "Waiting for event metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 111, m_name = 0x77bf1e0 "Waiting for commit lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 112, m_name = 0x77bf220 "User lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 113, m_name = 0x77bf260 "Waiting for locking service lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 114, m_name = 0x77bf2c0 "Waiting for spatial reference system lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 115, m_name = 0x77bf320 "Waiting for acl cache lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 116, m_name = 0x77bf360 "Waiting for column statistics lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 117, m_name = 0x77bf3c0 "Waiting for resource groups metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 118, m_name = 0x77bf420 "Waiting for foreign key metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}, {m_key = 119, m_name = 0x77bf480 "Waiting for check constraint metadata lock", m_flags = 0, m_documentation = 0x77beec0 ""}}}, m_mdl_type = 1, m_mdl_duration = 1, m_mdl_status = 1, m_src_file = 0x76b7b00 "/data/caizj/source/mysql/sql/sql_parse.cc", m_src_line = 5943, m_owner_thread_id = 72, m_owner_event_id = 2} (gdb) p $19 $25 = (PFS_metadata_lock *) 0x2b3ec22f8000 (gdb) p *(MDL_ticket*)($19->m_identity) $28 = {<MDL_wait_for_subgraph> = {_vptr.MDL_wait_for_subgraph = 0x91a6938 <vtable for MDL_ticket+16>, static DEADLOCK_WEIGHT_CO = 0, static DEADLOCK_WEIGHT_DML = 25, static DEADLOCK_WEIGHT_ULL = 50, static DEADLOCK_WEIGHT_DDL = 100}, next_in_context = 0x6070001a4020, prev_in_context = 0x60700026ad38, next_in_lock = 0x0, prev_in_lock = 0x617000440e50, m_type = MDL_EXCLUSIVE, m_ctx = 0x62600022b178, m_lock = 0x617000440c40, m_is_fast_path = false, m_hton_notified = true, m_psi = 0x2b3ec22f8000} How to repeat: create table test.tt_bit2( a int, b int) start transaction; select * from performance_schema.metadata_locks; Suggested fix: When upgrade shared lock(mdl_ticket->m_type = new_type), it should update PSI metadata_locks information in the meantime. Maybe add another PSI metadata_lock interface to update metadata_lock or simulate updating by create_metadata_lock and destory_metadata_lock?