MySQL 5.7, such as the source of spinach sold where the location of DDL blocked issues

In MySQL 5.7, for MDL, spinach source for sale (www.1159880099.com) QQ1159880099 introduced a new table performance_schema.metadata_locks, The table can show the relevant information about MDL, including its object, type and holding wait.

Opening the instrument of MDL

However, the relevant instrument is not turned on (MySQL 8.0 is on by default), it can be opened in the following two ways,

Temporary entry into force

The Performance_schema.setup_instrume NTS table is modified, but after the instance restarts, it reverts to the default value.

UPDATE performance_schema.setup_instruments SET ENABLED = ' yes ', TIMED = ' yes '
WHERE NAME = ' wait/lock/metadata/sql/mdl ';

Permanent effect
Setting in the configuration file
[Mysqld]
Performance-schema-instrument= ' Wait/lock/metadata/sql/mdl=on '

Test scenario
Here's a simple demo to see how to locate the blocking problem with DDL operations in MySQL 5.7.

Copy Code
Session1> begin;
Query OK, 0 rows Affected (0.00 sec)

Session1> Delete from slowtech.t1 where id=2;
Query OK, 1 row Affected (0.00 sec)

Session1> select * from Slowtech.t1;
+------+------+
| ID | name |
+------+------+
| 1 | A |
+------+------+
1 row in Set (0.00 sec)

session1> Update slowtech.t1 set name= ' C ' where id=1;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

Session2> ALTER TABLE SLOWTECH.T1 add C1 int; # #被阻塞

Session3> show Processlist;
+----+------+-----------+------+---------+------+---------------------------------+---------------------------- --------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+---------------------------- --------+
| 2 | Root | localhost | NULL | Sleep | 51 | | NULL |
| 3 | Root | localhost | NULL | Query | 0 | Starting | Show Processlist |
| 4 | Root | localhost | NULL | Query | 9 | Waiting for table Metadata Lock | ALTER TABLE SLOWTECH.T1 add C1 int |
+----+------+-----------+------+---------+------+---------------------------------+---------------------------- --------+
3 Rows in Set (0.00 sec)

Session3> Select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id From Performance_schema.metadata_locks;
+-------------+--------------------+----------------+---------------------+---------------+-------------+------ -----------+
| object_type | Object_schema | object_name | Lock_type | lock_duration | Lock_status | owner_thread_id |
+-------------+--------------------+----------------+---------------------+---------------+-------------+------ -----------+
| TABLE | Slowtech | T1 | Shared_write | TRANSACTION | Granted | 27 |
| GLOBAL | NULL | NULL | intention_exclusive | STATEMENT | Granted | 29 |
| SCHEMA | Slowtech | NULL | intention_exclusive | TRANSACTION | Granted | 29 |
| TABLE | Slowtech | T1 | shared_upgradable | TRANSACTION | Granted | 29 |
| TABLE | Slowtech | T1 | EXCLUSIVE | TRANSACTION | PENDING | 29 |
| TABLE | Performance_schema | Metadata_locks | Shared_read | TRANSACTION | Granted | 28 |
+-------------+--------------------+----------------+---------------------+---------------+-------------+------ -----------+
6 rows in Set (0.00 sec)
Copy Code
Here, the focus is on Lock_status, "PENDING" means that the thread is waiting for the MDL, while "granted" means the thread holds the MDL.

How to find the session that caused the blocking

Combined with owner_thread_id, can be reached, is the Line 29 line in the waiting for the Line 27 line of the MDL, at this time, can kill off Line 52 line.

However, it is important to note that owner_thread_id gives only the thread ID, not the ID in show processlist. If you want to find the Processlist ID for the thread, you need to query the Performance_schema.threads table.

Copy Code
Session3> SELECT * from Performance_schema.threads where thread_id in (27,29) \g
1. Row
Thread_id:27
Name:thread/sql/one_connection
Type:foreground
Processlist_id:2
Processlist_user:root
Processlist_host:localhost
Processlist_db:null
Processlist_command:sleep
processlist_time:214
Processlist_state:null
Processlist_info:null
Parent_thread_id:1
Role:null
Instrumented:yes
History:yes
Connection_type:socket
thread_os_id:9800
2. Row
Thread_id:29
Name:thread/sql/one_connection
Type:foreground
Processlist_id:4
Processlist_user:root
Processlist_host:localhost
Processlist_db:null
Processlist_command:query
processlist_time:172
Processlist_state:waiting for Table metadata Lock
Processlist_info:alter table SLOWTECH.T1 Add C1 int
Parent_thread_id:1
Role:null
Instrumented:yes
History:yes
Connection_type:socket
thread_os_id:9907
2 rows in Set (0.00 sec)
Copy Code
By combining the two tables and drawing on the output of the Sys.innodb_lock _waits, we can actually visualize the MDL waiting relationship.

Copy Code
SELECT
A.object_schema as Locked_schema,
A.object_name as locked_table,
"Metadata Lock" as Locked _type,
c.processlist_id as waiting_processlist_id,
C.processlist_time as Waiting_age,
C.processlist_info As Waiting_query,
C.processlist_state as Waiting_state,
d.processlist_id as blocking_processlist_id,
D. Processlist_time as Blocking_age,
D.processlist_info as Blocking_query,
concat (' KILL ', d.processlist_id) as SQL _kill_blocking_connection
from
performance_schema.metadata_locks a
JOIN performance_schema.metadata_ Locks B on a.object_schema = B.object_schema
and a.object_name = B.object_name
and a.lock_status = ' PENDING '
and B.lock_status = ' granted '
and a.owner_thread_id <> b.owner_thread_id
and a.lock_type = ' EXCLUSIVE '
JOIN Performance_schema.threads C on a.owner_thread_id = c.thread_id
JOIN performance_schema.threads D on b.OWNER_THREAD_ ID = d.thread_id\g

1. Row
Locked_schema:slowtech
Locked_table:t1
Locked_type:metadata Lock
Waiting_processlist_id:4
waiting_age:259
Waiting_query:alter table SLOWTECH.T1 Add C1 int
Waiting_state:waiting for Table metadata Lock
Blocking_processlist_id:2
blocking_age:301
Blocking_query:null
Sql_kill_blocking_connection:kill 2
1 row in Set (0.00 sec)
Copy Code
Output at a glance, DDL operation if you want to get MDL, execute kill 2.

The official Sys.schematablelock_waits
In fact, MySQL 5.7 also integrates similar functionality in the SYS library, and the same scenario, the output is as follows,

Copy Code
Mysql> SELECT * from Sys.schema_table_lock_waits\g
1. Row
Object_schema:slowtech
Object_name:t1
Waiting_thread_id:29
Waiting_pid:4
Waiting_account: [Email protected]
Waiting_lock_type:exclusive
Waiting_lock_duration:transaction
Waiting_query:alter table SLOWTECH.T1 Add C1 int
waiting_query_secs:446
waiting_query_rows_affected:0
waiting_query_rows_examined:0
Blocking_thread_id:27
Blocking_pid:2
Blocking_account: [Email protected]
Blocking_lock_type:shared_read
Blocking_lock_duration:transaction
Sql_kill_blocking_query:kill Query 2
Sql_kill_blocking_connection:kill 2
2. Row
Object_schema:slowtech
Object_name:t1
Waiting_thread_id:29
Waiting_pid:4
Waiting_account: [Email protected]
Waiting_lock_type:exclusive
Waiting_lock_duration:transaction
Waiting_query:alter table SLOWTECH.T1 Add C1 int
waiting_query_secs:446
waiting_query_rows_affected:0
waiting_query_rows_examined:0
Blocking_thread_id:29
Blocking_pid:4
Blocking_account: [Email protected]
Blocking_lock_type:shared_upgradable
Blocking_lock_duration:transaction
Sql_kill_blocking_query:kill Query 4
Sql_kill_blocking_connection:kill 4
2 rows in Set (0.00 sec)
Copy Code
Specific analysis of the official output,

Only an ALTER TABLE operation, but produced two records, and two records of the Kill object is not the same, the table structure is not familiar with and do not carefully read the contents of the record, it will inevitably kill the wrong object.

Furthermore, if n queries are blocked by DDL operations, then n 2 records are generated. In the case of more blocking operations, this N2 record is completely noisy.

In the previous SQL, no matter how many operations were blocked, an ALTER TABLE operation would only output a single record.

How to view actions that have been performed by a blocking session
But the above SQL also has regrets, its blocking_query is null, and in session 1, it clearly has executed three SQL.

This is related to performance_schema.threads (similar to show Processlist), which only outputs the currently running SQL, and for what has been done, there is actually no way to see it.

But on the line, kill is a prudent operation, after all, it's hard to know if Kill is a business-critical operation? Or is it a batch update operation? So, is there a way to catch the operation before the transaction?

Answer, yes.

This is the table in the performance schema that records the statement event (action event), including Events_statements_current,events_statements_history,events_ Statements_history_long,prepared_statements_instances.

The most common is the first three.

The table structure of the three is exactly the same, in which the Events_statements_history also contains the events_statements_current operation, so we will use events_statements_history here.

The ultimate SQL is as follows,

Copy Code
SELECT
Locked_schema,
Locked_table,
Locked_type,
WAITING_PROCESSLIST_ID,
Waiting_age,
Waiting_query,
Waiting_state,
BLOCKING_PROCESSLIST_ID,
Blocking_age,
Substring_index (Sql_text, "transaction_begin;", -1) as Blocking_query,
Sql_kill_blocking_connection
From
(
SELECT
b.owner_thread_id as granted_thread_id,
A.object_schema as Locked_schema,
A.object_name as Locked_table,
"Metadata Lock" as Locked_type,
c.processlist_id as waiting_processlist_id,
C.processlist_time as Waiting_age,
C.processlist_info as Waiting_query,
C.processlist_state as Waiting_state,
d.processlist_id as blocking_processlist_id,
D.processlist_time as Blocking_age,
D.processlist_info as Blocking_query,
Concat (' KILL ', d.processlist_id) as Sql_kill_blocking_connection
From
Performance_schema.metadata_locks A
JOIN performance_schema.metadata_locks b on a.object_schema = B.object_schema
and a.object_name = B.object_name
and a.lock_status = ' PENDING '
and b.lock_status = ' granted '
and a.owner_thread_id <> b.owner_thread_id
and A.lock_type = ' EXCLUSIVE '
JOIN performance_schema.threads C on a.owner_thread_id = c.thread_id
JOIN performance_schema.threads D on b.owner_thread_id = d.thread_id
) T1,
(
SELECT
THREAD_ID,
Group_concat (case is event_name = ' statement/sql/begin ' then "Transaction_begin" ELSE sql_text END ORDER by event_id SE Parator ";") As Sql_text
From
Performance_schema.events_statements_history
GROUP by thread_id
) T2
WHERE
t1.granted_thread_id = t2.thread_id \g

1. Row
Locked_schema:slowtech
Locked_table:t1
Locked_type:metadata Lock
Waiting_processlist_id:4
waiting_age:294
Waiting_query:alter table SLOWTECH.T1 Add C1 int
Waiting_state:waiting for Table metadata Lock
Blocking_processlist_id:2
blocking_age:336
Blocking_query:delete from Slowtech.t1 where Id=2;select * from Slowtech.t1;update slowtech.t1 set name= ' C ' where id=1
Sql_kill_blocking_connection:kill 2
1 row in Set, 1 Warning (0.00 sec)
Copy Code
As can be seen from the above output, Blocking_query contains all the operations of the current transaction in session 1, output in order of execution.

It is important to note that by default, Events_statements_history only retains the last 10 operations per thread, and if there are more operations in the transaction, there is actually no way to catch the whole.

MySQL 5.7, such as the source of spinach sold where the location of DDL blocked issues