1. 查看全局读锁,表锁,MDL锁
1)说明
以下说明的方式适用于Mysql 5.7 及以上版本。对于5.7之前版本,没有具体的方法查看全局读锁,表锁,MDL锁信息。可以通过 information_schema.processlist,performance_schema.events_statements_history, performance_schema.events_statements_current 等表大概推测阻塞情况。
5.7及以上版本
2)使用如下方法的前提条件
##在5.7版本之前没有专门记录全局读锁信息的表,在5.7中新增performance_schema.metadata_locks视图可以查询全局读锁
update performance_schema.setup_instruments set ENABLED='YES',TIMED='YES' where name like 'statement%';
##该生产者默认已启用
update performance_schema.setup_instruments set ENABLED='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';
##该生产者默认未启用
update performance_schema.setup_consumers set ENABLED='YES' where name='events_statements_current' or name='events_statements_history';
##该消费者默认启用
update performance_schema.setup_consumers set ENABLED='YES' where name='global_instrumentation';
##该消费者默认已启用
3)查询全局读锁,表锁,MDL锁信息阻塞信息
NOTE1:各种备份工具为了得到一致性备份通常使用 flush table with read lock;来获得全局读锁。
查询方式1:(会列出所有的mdl,全局读锁,表锁等信息,但是无法直观判断出阻塞者与被阻塞者)
set group_concat_max_len=20480;
select
t.PROCESSLIST_ID,
t.PROCESSLIST_USER,
t.PROCESSLIST_HOST,
t.PROCESSLIST_STATE,
t.PROCESSLIST_INFO,
i.trx_started,
i.trx_state,
i.trx_query,
(select group_concat(tem_b.sql_text order by EVENT_ID SEPARATOR ';\n') from (select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_history his union select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_current cur) tem_b where tem_b.thread_id=t.thread_id) trx_statements,
m.OBJECT_TYPE,
m.OBJECT_SCHEMA,
m.OBJECT_NAME,
m.OBJECT_INSTANCE_BEGIN,
m.LOCK_TYPE,
m.LOCK_DURATION,
m.LOCK_STATUS,
m.SOURCE,
m.OWNER_THREAD_ID,
m.OWNER_EVENT_ID
from
performance_schema.metadata_locks m
join `performance_schema`.`threads` t on t.THREAD_ID=m.OWNER_THREAD_ID and m.OWNER_THREAD_ID!=sys.ps_thread_id(connection_id())
left join `information_schema`.`innodb_trx` i on t.PROCESSLIST_ID=i.trx_mysql_thread_id
order by m.OBJECT_INSTANCE_BEGIN,m.OWNER_THREAD_ID\G;
##如上语句会显示所有的MDL锁信息
查询方式2:(可以直观的列出阻塞者和被阻塞者及是否存在事务,但是无法列出阻塞者及被阻塞者目前持有的所有 mdl锁信息)
SELECT
`g`.`OBJECT_SCHEMA` AS `object_schema`,
`g`.`OBJECT_NAME` AS `object_name`,
`pt`.`THREAD_ID` AS `waiting_thread_id`,
`pt`.`PROCESSLIST_ID` AS `waiting_pid`,
`sys`.`ps_thread_account` (`p`.`OWNER_THREAD_ID`) AS `waiting_account`,
`pi`.`trx_started` AS `waiting_trx_started`,
`pi`.`trx_state` AS `waiting_trx_state`,
`p`.`LOCK_TYPE` AS `waiting_lock_type`,
`p`.`LOCK_DURATION` AS `waiting_lock_duration`,
`pt`.`PROCESSLIST_STATE` AS `waiting_state`,
`pt`.`PROCESSLIST_TIME` AS `waiting_query_secs`,
`ps`.`ROWS_AFFECTED` AS `waiting_query_rows_affected`,
`ps`.`ROWS_EXAMINED` AS `waiting_query_rows_examined`,
`sys`.`format_statement` (`pt`.`PROCESSLIST_INFO`) AS `waiting_query`,
(select group_concat(tem_b.sql_text order by EVENT_ID SEPARATOR ';\n') from (select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_history his union select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_current cur) tem_b where tem_b.thread_id=ps.THREAD_ID) waiting_trx_statements,
`gt`.`THREAD_ID` AS `blocking_thread_id`,
`gt`.`PROCESSLIST_ID` AS `blocking_pid`,
`sys`.`ps_thread_account` (`g`.`OWNER_THREAD_ID`) AS `blocking_account`,
`gi`.`trx_started` AS `blocking_trx_started`,
`gi`.`trx_state` AS `blocking_trx_state`,
`g`.`LOCK_TYPE` AS `blocking_lock_type`,
`gt`.`PROCESSLIST_STATE` AS `blocking_state`,
`g`.`LOCK_DURATION` AS `blocking_lock_duration`,
(select group_concat(tem_b.sql_text order by EVENT_ID SEPARATOR ';\n') from (select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_history his union select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_current cur) tem_b where tem_b.thread_id=gs.THREAD_ID) blocking_trx_statements,
concat('KILL QUERY ',`gt`.`PROCESSLIST_ID`) AS `sql_kill_blocking_query`,
concat('KILL ',`gt`.`PROCESSLIST_ID`) AS `sql_kill_blocking_connection`
FROM
`performance_schema`.`metadata_locks` `g`
JOIN `performance_schema`.`metadata_locks` `p` ON `g`.`OBJECT_TYPE` = `p`.`OBJECT_TYPE`
AND `g`.`OBJECT_SCHEMA` = `p`.`OBJECT_SCHEMA`
AND `g`.`OBJECT_NAME` = `p`.`OBJECT_NAME`
AND `g`.`OWNER_THREAD_ID` != `p`.`OWNER_THREAD_ID`
AND `g`.`LOCK_STATUS` = 'GRANTED'
AND `p`.`LOCK_STATUS` = 'PENDING'
JOIN `performance_schema`.`threads` `gt` ON `g`.`OWNER_THREAD_ID` = `gt`.`THREAD_ID`
JOIN `performance_schema`.`threads` `pt` ON `p`.`OWNER_THREAD_ID` = `pt`.`THREAD_ID`
LEFT JOIN `performance_schema`.`events_statements_current` `gs` ON `g`.`OWNER_THREAD_ID` = `gs`.`THREAD_ID`
LEFT JOIN `performance_schema`.`events_statements_current` `ps` ON `p`.`OWNER_THREAD_ID` = `ps`.`THREAD_ID`
LEFT JOIN `information_schema`.`innodb_trx` `gi` ON `gi`.`trx_mysql_thread_id` = `gt`.`PROCESSLIST_ID`
LEFT JOIN `information_schema`.`innodb_trx` `pi` ON `pi`.`trx_mysql_thread_id` = `pt`.`PROCESSLIST_ID`
WHERE
`g`.`OBJECT_TYPE` = 'TABLE'\G;
NOTE1:sys.schema_table_lock_waits 视图可以用来查mdl,但是有一些不足 1)没有`g`.`OWNER_THREAD_ID` != `p`.`OWNER_THREAD_ID`会查出自己阻塞自己的情况;2)没有关联`information_schema`.`innodb_trx`事务表,不知道事务情况;3)没有输出线程最近执行的10条语句
4)全局读锁阻塞示例
结果如下(会话1执行flush table with read lock,会话2执行update test_lock set name='name_8_before' where id=8被阻塞 ):
*************************** 1. row ***************************
PROCESSLIST_ID: 1012898 --会话的 processlist_id(information_schema.processlist.id)
PROCESSLIST_USER: root --会话用户
PROCESSLIST_HOST: 127.0.0.1 --会话ip
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
trx_started: NULL
trx_state: NULL
trx_query: NULL
trx_statements: show processlist; --当前线程最近执行的10条sql(最后一条是获取全局读锁语句)
show processlist;
show processlist;
show processlist;
show processlist;
show processlist;
show processlist;
show processlist;
select connection_id();
flush table with read lock
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140243634271056
LOCK_TYPE: SHARED
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: lock.cc:1110
OWNER_THREAD_ID: 1012931 --线程id(performance_schema.threads.THREAD_ID)
OWNER_EVENT_ID: 24
*************************** 2. row ***************************
PROCESSLIST_ID: 1012898
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
trx_started: NULL
trx_state: NULL
trx_query: NULL
trx_statements: show processlist;
show processlist;
show processlist;
show processlist;
show processlist;
show processlist;
show processlist;
show processlist;
select connection_id();
flush table with read lock
OBJECT_TYPE: COMMIT
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140243634291712
LOCK_TYPE: SHARED
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: lock.cc:1194
OWNER_THREAD_ID: 1012931
OWNER_EVENT_ID: 24
*************************** 3. row ***************************
PROCESSLIST_ID: 1012887 --会话的 processlist_id(information_schema.processlist.id)
PROCESSLIST_USER: root --会话用户
PROCESSLIST_HOST: 127.0.0.1 --会话用户
PROCESSLIST_STATE: Waiting for global read lock --会话当前所处状态(等待全局读锁)
PROCESSLIST_INFO: update test_lock set name='name_8_before' where id=8 --会话当前执行的sql
trx_started: NULL
trx_state: NULL
trx_query: NULL
trx_statements: truncate table performance_schema.events_statements_history;
update test_lock set name='name_8_before' where id=8
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140243701507712
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: PENDING
SOURCE: sql_base.cc:3190
OWNER_THREAD_ID: 1012920
OWNER_EVENT_ID: 66
3 rows in set (0.04 sec)
ERROR:
No query specified
5)MDL 锁阻塞示例
会话1对大表t进行全表更新(一直在进行中),会话2对大表进行ddl操作
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test_shao
OBJECT_NAME: t
OBJECT_INSTANCE_BEGIN: 119085328
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 1012919
OWNER_EVENT_ID: 34
trx_started: 2020-03-24 11:20:56
trx_state: RUNNING
trx_query: update t set name_3='xxxx_3'
PROCESSLIST_ID: 1012886
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: updating
PROCESSLIST_INFO: update t set name_3='xxxx_3'
trx_statements: update t set name_3='xxxx_3'
*************************** 2. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 119851536
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:3190
OWNER_THREAD_ID: 1012919
OWNER_EVENT_ID: 34
trx_started: 2020-03-24 11:20:56
trx_state: RUNNING
trx_query: update t set name_3='xxxx_3'
PROCESSLIST_ID: 1012886
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: updating
PROCESSLIST_INFO: update t set name_3='xxxx_3'
trx_statements: update t set name_3='xxxx_3'
*************************** 3. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140243701380176
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5533
OWNER_THREAD_ID: 1012920
OWNER_EVENT_ID: 59
trx_started: NULL
trx_state: NULL
trx_query: NULL
PROCESSLIST_ID: 1012887
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: alter table t add column name_4 varchar(20)
trx_statements: truncate table performance_schema.events_statements_history;
alter table t add column name_4 varchar(20)
*************************** 4. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test_shao
OBJECT_NAME: t
OBJECT_INSTANCE_BEGIN: 140243701506688
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: mdl.cc:3919
OWNER_THREAD_ID: 1012920
OWNER_EVENT_ID: 59
trx_started: NULL
trx_state: NULL
trx_query: NULL
PROCESSLIST_ID: 1012887
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: alter table t add column name_4 varchar(20)
trx_statements: truncate table performance_schema.events_statements_history;
alter table t add column name_4 varchar(20)
*************************** 5. row ***************************
OBJECT_TYPE: SCHEMA
OBJECT_SCHEMA: test_shao
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140243701507712
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5518
OWNER_THREAD_ID: 1012920
OWNER_EVENT_ID: 59
trx_started: NULL
trx_state: NULL
trx_query: NULL
PROCESSLIST_ID: 1012887
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: alter table t add column name_4 varchar(20)
trx_statements: truncate table performance_schema.events_statements_history;
alter table t add column name_4 varchar(20)
*************************** 6. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test_shao
OBJECT_NAME: t
OBJECT_INSTANCE_BEGIN: 140243701509408
LOCK_TYPE: SHARED_UPGRADABLE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 1012920
OWNER_EVENT_ID: 59
trx_started: NULL
trx_state: NULL
trx_query: NULL
PROCESSLIST_ID: 1012887
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: alter table t add column name_4 varchar(20)
trx_statements: truncate table performance_schema.events_statements_history;
alter table t add column name_4 varchar(20)
6 rows in set (0.01 sec)
ERROR:
No query specified
6) 表锁阻塞示例
##会话1执行lock table test_lock read,会话2执行update test_lock set name='xxx_name' where id=1 被阻塞
*************************** 1. row ***************************
PROCESSLIST_ID: 1013225
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
trx_started: NULL
trx_state: NULL
trx_query: NULL
trx_statements: truncate table performance_schema.events_statements_history;
lock table test_lock read
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test_shao
OBJECT_NAME: test_lock
OBJECT_INSTANCE_BEGIN: 140243500062176
LOCK_TYPE: SHARED_READ_ONLY
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 1013258
OWNER_EVENT_ID: 10
*************************** 2. row ***************************
PROCESSLIST_ID: 1013224
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: update test_lock set name='xxx_name' where id=1
trx_started: NULL
trx_state: NULL
trx_query: NULL
trx_statements: select connection_id();
update test_lock set name='xxx_name' where id=1
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test_shao
OBJECT_NAME: test_lock
OBJECT_INSTANCE_BEGIN: 140243768501248
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 1013257
OWNER_EVENT_ID: 5
*************************** 3. row ***************************
PROCESSLIST_ID: 1013224
PROCESSLIST_USER: root
PROCESSLIST_HOST: 127.0.0.1
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: update test_lock set name='xxx_name' where id=1
trx_started: NULL
trx_state: NULL
trx_query: NULL
trx_statements: select connection_id();
update test_lock set name='xxx_name' where id=1
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140243768882480
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:3190
OWNER_THREAD_ID: 1013257
OWNER_EVENT_ID: 5
3 rows in set (0.01 sec)
ERROR:
No query specified
7)级联阻塞查询示例(使用查询方式2)
#session 1 执行 count(distinct id),session 2 添加字段 name_5,session 3 添加字段 name_6,session 4 count(*)查询
*************************** 1. row ***************************
object_schema: test_shao
object_name: t
waiting_thread_id: 1066526
waiting_pid: 1066493
waiting_account: root@127.0.0.1
waiting_lock_type: SHARED_UPGRADABLE
waiting_lock_duration: TRANSACTION
waiting_query_secs: 3
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
waiting_query: alter table t add column name_6 varchar(20)
waiting_trx_statements: select USER();
SELECT DATABASE();
show tables;
desc t;
alter table t add column name_5 varchar(20);
select connection_id();
alter table t add column name_5 varchar(20);
alter table t add column name_5 varchar(20);
alter table t add column name_5 varchar(20);
alter table t add column name_6 varchar(20)
blocking_thread_id: 1066522
blocking_pid: 1066489
blocking_account: root@127.0.0.1
blocking_lock_type: SHARED_READ
blocking_lock_duration: TRANSACTION
blocking_trx_statements: selelct connection_id();
select connection_id();
select count(*) from t;
select connection_id();
select sys.ps_thread_id(connection_id());
select sys.ps_thread_id(connection_id());
select count(*) from t;
select count(*) from t;
select count(distinct id) from t
sql_kill_blocking_query: KILL QUERY 1066489
sql_kill_blocking_connection: KILL 1066489
*************************** 2. row ***************************
object_schema: test_shao
object_name: t
waiting_thread_id: 1066526
waiting_pid: 1066493
waiting_account: root@127.0.0.1
waiting_lock_type: SHARED_UPGRADABLE
waiting_lock_duration: TRANSACTION
waiting_query_secs: 3
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
waiting_query: alter table t add column name_6 varchar(20)
waiting_trx_statements: select USER();
SELECT DATABASE();
show tables;
desc t;
alter table t add column name_5 varchar(20);
select connection_id();
alter table t add column name_5 varchar(20);
alter table t add column name_5 varchar(20);
alter table t add column name_5 varchar(20);
alter table t add column name_6 varchar(20)
blocking_thread_id: 1066851
blocking_pid: 1066818
blocking_account: root@127.0.0.1
blocking_lock_type: SHARED_UPGRADABLE
blocking_lock_duration: TRANSACTION
blocking_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
alter table t add column name_6 varchar(20);
alter table t add column name_5 varchar(20)
sql_kill_blocking_query: KILL QUERY 1066818
sql_kill_blocking_connection: KILL 1066818
*************************** 3. row ***************************
object_schema: test_shao
object_name: t
waiting_thread_id: 1066851
waiting_pid: 1066818
waiting_account: root@127.0.0.1
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query_secs: 3
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
waiting_query: alter table t add column name_5 varchar(20)
waiting_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
alter table t add column name_6 varchar(20);
alter table t add column name_5 varchar(20)
blocking_thread_id: 1066522
blocking_pid: 1066489
blocking_account: root@127.0.0.1
blocking_lock_type: SHARED_READ
blocking_lock_duration: TRANSACTION
blocking_trx_statements: selelct connection_id();
select connection_id();
select count(*) from t;
select connection_id();
select sys.ps_thread_id(connection_id());
select sys.ps_thread_id(connection_id());
select count(*) from t;
select count(*) from t;
select count(distinct id) from t
sql_kill_blocking_query: KILL QUERY 1066489
sql_kill_blocking_connection: KILL 1066489
*************************** 4. row ***************************
object_schema: test_shao
object_name: t
waiting_thread_id: 1066852
waiting_pid: 1066819
waiting_account: root@127.0.0.1
waiting_lock_type: SHARED_READ
waiting_lock_duration: TRANSACTION
waiting_query_secs: 2
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
waiting_query: select count(*) from t
waiting_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
select count(*) from t;
desc t;
select count(*) from t
blocking_thread_id: 1066522
blocking_pid: 1066489
blocking_account: root@127.0.0.1
blocking_lock_type: SHARED_READ
blocking_lock_duration: TRANSACTION
blocking_trx_statements: selelct connection_id();
select connection_id();
select count(*) from t;
select connection_id();
select sys.ps_thread_id(connection_id());
select sys.ps_thread_id(connection_id());
select count(*) from t;
select count(*) from t;
select count(distinct id) from t
sql_kill_blocking_query: KILL QUERY 1066489
sql_kill_blocking_connection: KILL 1066489
*************************** 5. row ***************************
object_schema: test_shao
object_name: t
waiting_thread_id: 1066852
waiting_pid: 1066819
waiting_account: root@127.0.0.1
waiting_lock_type: SHARED_READ
waiting_lock_duration: TRANSACTION
waiting_query_secs: 2
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
waiting_query: select count(*) from t
waiting_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
select count(*) from t;
desc t;
select count(*) from t
blocking_thread_id: 1066851
blocking_pid: 1066818
blocking_account: root@127.0.0.1
blocking_lock_type: SHARED_UPGRADABLE
blocking_lock_duration: TRANSACTION
blocking_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
alter table t add column name_6 varchar(20);
alter table t add column name_5 varchar(20)
sql_kill_blocking_query: KILL QUERY 1066818
sql_kill_blocking_connection: KILL 1066818
5 rows in set (0.00 sec)
NOTE:在出现级联阻塞的时候,我们要注意找到阻塞的源头(blocking_pid 出现频率最高的会话)