Mysql 全局读锁，MDL锁阻塞查询

最新推荐文章于 2024-03-05 14:54:09 发布
最新推荐文章于 2024-03-05 14:54:09 发布
阅读量1.1k 收藏 1
点赞数
分类专栏： Mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shaochenshuo/article/details/105075345
版权

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 出现频率最高的会话)

 

渔夫数据库笔记
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MYSQL METADATA LOCK(MDL LOCK)MDL锁问题分析
12-16
一、前言 MYSQL中MDL锁一直是一个比较让人比较头疼的问题，我们谈起锁一般更加倾向于INNODB下层的gap lock、next key lock、row lock等，因为它很好理解，也很好观察，而对于MDL LOCK却了解得很少，因为它实在不好观察，只有出现问题查看show processlist勉强可以看到 简单的所谓的Waiting for table metadata lock之类的状态，其实MDL LOCK是MYSQL上层一个非常复杂的子系统，有自己的死锁检测机制 (无向图?)而大家一般口中的是不是锁表了其实就是指的它，可见的它的关键性和严重性，笔者也是根据自己的需求学习了一
mysql中MDL（元数据锁）的长事务读写阻塞如何解决
pige666的博客
12-05 150
那其存在的长事务读写阻塞问题，一般是怎么解决的呢，主要有两种解决方法。
MySQL 元数据锁及问题排查（Metadata Locks MDL）
最新发布
Vincent的博客
03-05 945
"元数据"是用来描述数据对象定义的，而元数据锁（Metadata Lock MDL）即是加在这些定义上。通常我们认为非锁定一致性读（简单select）是不加锁的，这个是基于表内数据层面，其依然会对表的元数据加锁，保证读取数据期间表结构不会变更。
MYSQL MDL锁之select阻塞ddl操作
jolly10的专栏
03-28 1562
今天做了一个简单的测试，当session1做一个小表的查询结束后，session2对些表做alter操作，发现session会由于拿不到这个 阻表的MDL锁而被阻塞，这种情况在oracle中是不会发生的，由于经常用oracle的思维去学mysql，觉得这样的结果有点不可思议。session 1:mysql> use l5m Database changed mysql> select ...
mysql5.7中如何查看表是否被锁问题
fhw925464207的博客
07-20 1244
在使用mysql的时候，如何查看表是否被锁呢？该语句可以查询到当前锁表的状态通过sql日志，分析相应SQL，给表加索引，常用字段加索引，表关联字段加索引等方式对sql进行优化。如果发生死锁，通过该语句可以查询到表被锁的进程，然后通过kill命令杀掉该进程。
MySQL（十二）MDL锁介绍
weixin_43189971的博客
08-20 4923
。
mysql mdl 锁_mysql原理 ~ 细说 MDL锁
weixin_39873741的博客
01-26 363
一 简介: MDL锁二 分类1 MDL读锁 MDL读锁是相互兼容的 SHARED_READ2 MDL 写锁 MDL写锁是互斥的 EXCLUSIVE三 针对目标1 目标 MDL锁是针对语句级别的,并非事务本身,是mysql_server层面的锁2 目的 当事务本身执行的时候理论上是不能容忍表结构在中途发生改变的四 MDL申请0 select 语句 MDL_SHARED_R...
mysql 读锁会不会阻塞写锁_MySQL 由于MDL读锁select被阻塞
weixin_35949256的博客
01-19 497
thread 1、begin；更新表；没有提交，也没有回滚操作thread2、create index 在这个表上这时候客户端超时中断再次连接会话查询此表被阻塞，无法查询thread3、查询 select * from test；root@localhost:yaochong17:08:27>selectid,user,host,db,command,time,state,info...
MySQL的MDL锁解惑
bisal的专栏
04-28 1071
前几天，某个使用MySQL的应用，出现了一些问题，表象上是应用请求无响应，通过检索数据库，发现很多会话执行的SQL提示"Waiting for table metadata lock"的等待，经过确认，这些SQL对应的就是这些请求，SQL无返回，自然从应用端不能得到响应。但为什么出现这种现象？"table metadata lock"又是什么锁？从"table metad...
MySQL全局锁和表锁的深入理解
12-15
MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。 当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据...
详细分析mysql MDL元数据锁
09-08
主要介绍了mysql MDL元数据锁的相关资料，文中讲解非常细致，代码帮助大家更好的理解和学习，感兴趣的朋友可以了解下
mysql MDL读写锁阻塞，以及online ddl造成的“插队”现象
欢迎访问 花落的速度 的博客
07-18 9135
看mysql45讲遇到一个问题 为什么C等待拿锁之后，D也会阻塞？其实这里并没有解释清楚。因为如果按并发理解的话，C，D应当是同等级，都有可能拿到锁的。但C读写锁互斥，D读读不互斥，这样的话就跟上图所述相悖了。就，查了一下。 （鸣谢 一梦如是YFL提供的文章） 首先是MDL（metaData Lock）的概念。元数据锁是server层的锁，表级锁，主要用于隔离DML（Data Ma...
mysql深入理解全局锁和表锁解决MDL锁和死锁问题
kouryoushine的博客
04-23 1230
说明 不太深入细节，mysql的数据库可以粗略划分为global、table、row三种锁 锁的种类 全局锁global lock 如何加锁 全局锁又称为库锁，即锁住整个db instance. 当我想让一个表read-only的时候，可以通过下面命令添加全局锁 Flush tables with read lock (FTWRL) 命令 set global readonly = true 全局参数 Update,Create,ALter,Commit,事务等操作都会被锁住。 什么时候使用全局锁 使用
MySQL学习笔记二：全局锁、表锁、MDL锁、行锁
Kim_
06-01 1557
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。 全局锁 全局锁就是对整个数据库实例加锁。可以通过命令 Flush tables with read lock (FTWRL) 对全局进行加锁，让整个库处...
Mysql - 争取一篇讲清楚Mysql中的锁（全局锁、表级锁、行级锁）及加锁规则、死锁问题
it_lihongmin的博客
03-17 1068
目录 全局锁（Flush tables with read lock （FTWRL）） 表级锁（表锁、MDL读锁写锁） 行级锁（行锁、临建锁、间隙锁） Mysql中的锁比较多并且加锁规则复杂，争取这一篇博客可以分析清楚，锁分为全局锁和表级锁是整个Mysql实例和所有的存储引擎都可以使用的。行级锁是InnoDB引擎特有的，而InnoDB最大的区别于其他引擎的特点就是事务，事务与锁、MVCC、Redo Log、Undo Log、Binlog都有关系，所以InnoDB为了实现事务代价还是比较大...
mysql锁系列之MDL元数据锁之一
一梦如是的博客
03-01 8580
基础材料： centos7.5 mysql 5.7.24 当mysql运行一条SQL语句时，在你预期的时间内，没有完成时，我们都会登陆到mysql数据库上想查看是不是出了什么问题，通常会使用的一个命令就是 show processlist，看看有哪些session，这些session在做什么事情。就从这个命令开始，显示如下： 图中看到了显示了几处信息： id：为session_id...
MySQL 实战总结：MySQL 元数据锁(MDL)
weixin_48943299的博客
04-02 6818
前言： 当我们在 MySQL 中执行 DDL 语句时，经常会发现语句没有在你预期的时间完成，这时候我们通常会在mysql输入终端输入show full processlist ，来看看发生了什么状况。当你看到 waiting for table metadata lock 时，那就碰到元数据锁了。那元数据锁是怎样产生的又应该怎样避免呢？让我们从这篇文章开始了解它。 一、什么是元数据锁(metadata lock) 在MySQL5.5.3之前，有一个著名的bug#989(bug链接: MySQL Bug
mysql数据库锁MDL锁的解释
huan的学习记录
12-05 3033
在我们系统中有一张表它的查询概率非常高。最近有个需求，需要对这个表增加一个字段，然而在增加字段的时候发现系统中有多个业务出现了超时操作，那么这个是什么原因导致的呢？经过查阅资料发现是数据库的MDL锁+事务导致的。
mysql(四): mysql的全局锁、表锁、行锁、死锁检测
weixin_42255265的博客
07-06 370
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理的控制资源的访问规则。 全局锁主要用在逻辑备份中，对于全部是InnoDB引擎的库，建议使用 -single-transaction参数备份更友好。 一、全局锁: 就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法。命令是: Flush table with read lock (FTWRL)。当...
simulink mdl
09-08
Simulink的.mdl文件是一种文本格式文件，用于存储Simulink模型的信息。早期的Simulink版本使用.mdl文件格式。而Simulink的.slx文件是一种二进制格式文件，从Matlab/Simulink R2012a版本开始引入，旨在取代.mdl格式。...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

渔夫数据库笔记

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值