首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MetaData Lock 之三

MetaData Lock 之三

作者头像
用户1278550
发布2018-08-09 11:01:55
6080
发布2018-08-09 11:01:55
举报
文章被收录于专栏:idbaidba

一 简介 通过前面两篇文章的介绍,相信读到这里的各位对MDL 锁已经有了比较深入的了解了,本文将结合理论知识介绍几组MDL 锁的案例。 二 常见MDL 锁的场景 1 Waiting for global read lock 我们先构造一个Waiting for global read lock场景: session1: alter table t1 add c3 bigint; //大表执行需较长时间 session2: set global read only=on; //等待 查看

  1. mysql> show processlist;
  2. +----+------+-----------------+------+---------+------+------------------------------+------------------+
  3. | Id | User | Host | db | Command | Time | State | Info |
  4. +----+------+-----------------+------+---------+------+------------------------------+------------------+
  5. | 1 | root | localhost:5202 | test | Query | 12 | altering table | alter table t1 add c3 bigint |
  6. | 2 | root | localhost:14699 | test | Query | 3 | Waiting for global read lock | set global read_only=on |
  7. | 3 | root | localhost:17085 | NULL | Query | 0 | init | show processlist |
  8. +----+------+-----------------+------+---------+------+------------------------------+------------------+

分析: alter table t1 add c3 bigint;会加(GLOBAL,MDL_STATEMENT,MDL_INTENTION_EXCLUSIVE) 语句结束后才释放 set global read only=on; 会加(GLOBAL,MDL_EXPLICIT,MDL_SHARED) 由于session1执行时间比较长,一直持有MDL_INTENTION_EXCLUSIVE。从兼容性矩阵可以看出MDL_SHARED和MDL_INTENTION_EXCLUSIVE是不相容的,因此发生“Waiting for global read lock ”等待。直到session 1 alter操作完成释放MDL_INTENTION_EXCLUSIVE。set global read only=on;才可以继续执行。 2.2 Waiting for commit lock

session1: begin; insert into t1 vlaues(null, 'ab'); session2: flush table with read lock;//成功 session1: commit //发生等待

mysql> show processlist;

  1. +----+------+-----------------+------+------------+------+-------------------------+------------------+
  2. | Id | User | Host | db | Command | Time | State | Info |
  3. +----+------+-----------------+------+------------+------+-------------------------+------------------+
  4. | 1 | root | 127.0.0.1:5202 | test | Query | 7 | Waiting for commit lock | commit |
  5. | 2 | root | 127.0.0.1:14699 | test | Sleep | 13 | | NULL |
  6. | 3 | root | 127.0.0.1:17085 | NULL | Query | 0 | init | show processlist |
  7. +----+------+-----------------+------+---------+------+-------------------------+------------------+

分析: flush table with read lock;持有(COMMIT,MDL_EXPLICIT,MDL_SHARED) commit时上(COMMIT,MDL_EXPLICIT,MDL_INTENTION_EXCLUSIVE)锁 MDL_SHARED和MDL_INTENTION_EXCLUSIVE是不相容的,因此发生等待. 2.3 Waiting for table metadata lock 这个是比较常见的锁等待,总结下来有如下几种场景 1 长查询/mysqldump 阻塞DDL

session1

session2

session3

Select count(*) from t;

alter table t add column c3 int;

Show processlist;session2:copy to tmp table

阻塞

Show processlist;session2:Waiting for table metadata lock

A:执行完毕

Show processlist; session2:rename table

Select count(*) from t;

B:执行完毕

Show processlist;session1: Sending data

会话1先执行select ,会话2后执行alter,在会话1执行完毕前,会话2拿不到MDL锁,从表格上面来看,主要阻塞在rename阶段。会话1在执行完毕后,会话2拿到MDL锁,变为rename table状态,这个操作持续时间非常短,会话1再次执行查询,当会话2执行完后,此时会话1正常执行。这说明对于MDL锁而言,select会阻塞alter,而alter不会阻塞select。在rename的瞬间,alter是会阻塞select的. 当执行select语句时,只要select语句在获取MDL_SHARED_READ锁之前,alter没有执行到rename阶段,那么select获取MDL_SHARED_READ锁成功,后续有alter执行到rename阶段,请求MDL_EXCLUSIVE锁时,就会被阻塞。rename阶段会持有MDL_EXCLUSIVE锁,但由于这个过程时间非常短(大头都在copy数据阶段),并且是alter的最后一个阶段,所以基本感觉不到alter会阻塞select语句。由于MDL锁在事务提交后才释放,若线上存在大查询,或者存在未提交的事务,则会出现ddl卡住的现象。这里要注意的是,ddl卡住后,若再有select查询或DML进来,都会被堵住,就会出现threadrunning飙高的情况。

2 未提交的事务阻塞 DDL

session1

session2

session3

begin;update t set where id=3;

alter table t add column c3 int;//hang

Show processlist;session2:Waiting for table metadata lock

Select count(*) from t;//hang

Show processlist;session2:Waiting for table metadata locksession3:Waiting for table metadata lock

session1 对表t进行update操作,存在未提交的事务,故一直持有 MDL_SHARED_WRITE锁,由于没有执行COMMIT,会一直持有。session2 DDL 操作会请求TABLE-TRANSACTION-EXCLUSIVE锁,该锁与session1 的MDL_SHARED_WRITE 锁互斥,故 session2 的DDL 等待;session3的查询操作会请求TABLE- TRANSACTION- MDL_SHARED_READ锁,虽然MDL_SHARED_READ与活跃锁MDL_SHARED_WRITE不冲突,但是与session2的等待锁EXCLUSIVE冲突,因此也会等待。 解决该中场景的问题比较麻烦,但从show processlist 不能检查出哪个会话持有锁 。可以从两个方面进行调查 a 查询 information_schema.innodb_trx

b 检查 show engine innodb status ;

active N sec 说明事务持续了N秒,一般而言超过10秒的事务都是有问题的 。找到了活动的事务之后,要和开发沟通看看如何处理,能否直接kill 该回话? 根据和开发沟通的结果采取相应的措施。 3 第1种情况的特例,存在一个查询失败的语句,比如查询不存在的列,语句失败返回,但是事务没有提交,此时alter仍然会被堵住。 通过show processlist看不到TableA上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对TableA进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。 官方手册上对此的说明如下:

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志。

三 参考 [1] 《MySQL出现Waiting for table metadata lock的场景浅析》 [2] 《MySQL中 metadata lock问题分析》 [3] 《官方Metadata Lock 介绍》

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2017-12-04,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档