锐客网

  1. 首页 > 睿知 > it技术 > >

重重封锁，让你一条数据都拿不到《死磕MySQL系列 十三》

IT知识mysql系列

笛里谁知壮士心，沙头空照征人骨。这篇文章主要讲述重重封锁，让你一条数据都拿不到《死磕MySQL系列 十三》相关的知识，希望能为你提供帮助。
在开发中有遇到很简单的SQL却执行的非常慢，甚至只查询一行数据。
咔咔遇到的只有两种情况，一种是mysql服务器CPU占用率很高，所有的SQL都执行的很慢直到超时，程序也直接502，另一种情况是行锁造成的锁等待。
接下来咔咔带领大家看看各种为难SQL执行的场景，本期文章带大家再熟悉一下MySQL中的锁

最新文章??死磕MySQL系列总目录??
??什么？还在用delete删除数据《死磕MySQL系列 九》??
??MySQL统计总数就用count(*)，别花里胡哨的《死磕MySQL系列 十》??
??为什么MySQL字符串不加引号索引失效？《死磕MySQL系列 十一》??
??打开order by的大门，一探究竟《死磕MySQL系列 十二》??
一、MDL锁现在你应该知道要聊的是MDL，这个锁很少有开发人员去关注，在开发中并没有实际的语法来开启或关闭锁。
这个特性是在MySQL5.5引入的，目的是为了解决一张表同时在做查询和修改表结构，这种情况必定会造成查询结果跟表结构无法对应。
所以，当你访问一个表时会默认加上MDL锁，MDL锁的互斥关系跟共享锁、排它锁是一样的，读写互斥，写写互斥。
MDl锁是在事务提交后才会释放，执行期间一直持有。
同时你需要知道MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现MDL写锁等待，会阻塞后续该表的所有CURL操作。
也就说，一旦你在一个未提交事务之后执行了DDL操作，那么等到的结果就是MySQL挂掉，客户端会有重试机制，DDL后所有CURD会在超时后重新发起请求，这个库的线程会很快爆满。
当线程A通过DDL时手里握着表的MDL写锁，而线程B的查询需要获取MDL读锁，所以线程B就一直处于锁等待状态。
在生产环境是坚决不可以直接修改表结构的，如果你的表非常大的话会很容易造成业务所有的CURD处于堵塞。
解决方案
大表DDL可以使用??pt-online-schema-change??这个工具来处理，具体怎么用后续文章会跟大家分享出来。
若不小心在线上执行了修改表结构，可以通过show processlist命令来查找，不过这个命令在查找上很不方便，可以使用performance_schema和sys系统库来进行查询。前提是你的MySQL参数performance_schema=on，在MySQL8.0.26版本中，这个参数是默认开启的，若你所在的版本没有开启时可以打开。
然后就可以执行??select blocking_pid from sys.schema_table_lock_waits??，就可以看到当前持有MDL锁的线程ID，直接使用kill命令即可。
二、全局锁在MySQL强人“锁”难《死磕MySQL系列 三》的文章中给大家聊到了全局锁，使用语法??flush table t with read lock 或者 flush table with read lock??
指定表名时就锁定指定表，未指定时表示锁定所有表。
这两个语句执行是非常快的，一般不会造成SQL堵塞，但防火、防盗你也防不住有其它线程的语句把flush语句堵塞住。
线程A执行大事物，需要执行10s
线程B执行flush table t with read lock
线程C执行select * from evt_sms where id = 1
所以线程C哪怕是只查询一条数据在10s内也是返回不了结果的，线程B的flush 命令需要等线程A的事务执行完毕，而线程C此时却被未执行的线程B堵塞着。
解决方案
一般出现这种情况只需要执行show processlist就可以看到堵塞线程C的线程是那个，同样直接使用kill掉对应的线程即可。
三、行锁这个场景是非常好模拟的，接下来让我们一起看看
线程A正常修改大批量数据执行语句为update evt_sms set code = 123 where id > 11089
线程B执行select * from evt_sms where id = 120365 lock in share mode
在文章开头就跟大家简单的说了一句，MySQL中读锁与写锁、写锁与写锁互斥，所以线程B会一直等待线程A的事务提交之后才能返回结果。
解决方案
分析一下，线程B执行的语句添加的是读锁，能被堵住的只有是写锁，所以可以直接在sys.innodb_lock_waits表中查到占着这个写锁的是谁。
执行语句??select * from evt_sms sys.innodb_lock_waits where lock_table=kaka.evt_sms\\G??
这个试验就不演示了，复现过程也十分简答可以自己看一下哈！输出结果的最后一行就是解决方案，带着你的答案来到评论区
四、快照读引发的问题【重重封锁，让你一条数据都拿不到《死磕MySQL系列 十三》】了解过MVCC实现原理的大概率都会看到过当前读、快照读这两个词，如果你还不知道它们是什么就好好记一下。
当前读
执行select语句时加上共享锁、排它锁的操作就是当前读。
例：select * from evt_sms where id = 1 lock in share mode
这里的共享锁、排它锁也就是常说的读锁、写锁
在MySQL的Innodb存储引擎中进行DML操作时会默认添加排它锁
上边这个例子，select语句一旦加上了共享锁其它线程是不能修改当前记录的，因此当前读读取的数据库就是最新的数据
快照读
快照读的前提是隔离级别不是串行级别，串行级别的快照读会退化为当前读，快照读的出现是为了提高事务并发性，其实现也是基于MVCC的
MVCC在某种情况下可以认为是行锁的一个变种，但要知道的是在很多情况是不会有加锁行为的
这时你应该记住快照读获取的数据不是最新的，有可能是之前版本的数据
实现MVCC的三大因素隐式字段、undo log、read-view，read-view就是通过快照读产生的，它是由查询的那一时间所有未提交事务ID组成的数组，和已经创建的最大事务ID组成的。然后通过本线程的事务ID在read-view中进行对比
为什么说快照读会引发查询迟迟不返回结果
上文给大家提了一个东西undo log，都知道undo log是回滚日志，查询慢的原因也在这里
线程A先开启一个事务
线程B开启对id为1的数据行进行更新
由于id = 1的数据很多所以会产生很多的版本链，这里就认为是5万个
线程A执行了select * from evt_sms where id = 1就会迟迟返回不了结果
此时线程B并没有提交事务，所以线程A的查询需要根据版本链一直回退到5W个undo log之前，也就是这里导致查询非常慢
下图是一个咔咔之前做的undo log版本链图

线程A的查询是快照读，执行查询时会产生read-view，read-view会把线程A、线程B的事务存放在一个数组中，然后用一定的规则进行判断线程A能看到的数据是什么。
比对规则是什么
trx_id为当前的事务ID，min_id、max_id为当前启动事务的最大事务ID和最小事务ID
如果落在trx_id< min_id，表示此版本是已经提交的事务生成的，由于事务已经提交所以数据是可见的
如果落在trx_id> max_id，表示此版本是由将来启动的事务生成的，是肯定不可见的
若在min_id< =trx_id< =max_id时
如果row的trx_id在数组中，表示此版本是由还没提交的事务生成的，不可见，但是当前自己的事务是可见的如果row的trx_id不在数组中，表明是提交的事务生成了该版本，可见 在这里还有一个特殊情况那就是对于已经删除的数据，在之前的undo log日志讲述时说了update和delete是同一种类型的undo log，同样也可以认为delete就是update的特殊情况。
当删除一条数据时会将版本链上最新的数据复制一份，然后将trx_id修改为删除时的trx_id，同时在该记录的头信息中存在一个delete flag标记，将这个标记写上true，用来表示当前记录已经删除。
在查询时按照版本链的规则查询到对应的记录，如果delete flag标记位为true，意味着数据已经被删除，则不返回数据。
五、总结本期文章通过MDL锁、全局锁、行锁、undo log说明查询一条数据页迟迟不返回的问题，可以看到大多数都是一些理论知识，有些东西看着看着也就理解其中的含义了。
这里需要注意的是不要把MDL和DML搞混淆了，这可是两个东西，MDL指的是锁、而DML指的是数据库的增删改查。

“


坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助，我是咔咔，下期见。


”



    推荐阅读


    上一篇：#私藏项目实操分享#Python模拟登录，selenium模块，Python识别图形验证码实现自动登录

    下一篇：#yyds干货盘点#Spring源码三千问Spring动态代理（什么时候使用的 cglib，什么时候使用的是 jdk proxy（））