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

2022年01月14日 阅读数:0
这篇文章主要向大家介绍重重封锁,让你一条数据都拿不到《死磕MySQL系列 十三》,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

在开发中有遇到很简单的SQL却执行的很是慢,甚至只查询一行数据。数据库

咔咔遇到的只有两种状况,一种是MySQL服务器CPU占用率很高,全部的SQL都执行的很慢直到超时,程序也直接502,另外一种状况是行锁形成的锁等待。数组

接下来咔咔带领你们看看各类为难SQL执行的场景,本期文章带你们再熟悉一下MySQL中的锁服务器

最新文章

死磕MySQL系列总目录并发

什么?还在用delete删除数据《死磕MySQL系列 九》app

MySQL统计总数就用count(*),别花里胡哨的《死磕MySQL系列 十》工具

为何MySQL字符串不加引号索引失效?《死磕MySQL系列 十一》学习

打开order by的大门,一探究竟《死磕MySQL系列 十二》url

1、MDL锁

如今你应该知道要聊的是MDL,这个锁不多有开发人员去关注,在开发中并无实际的语法来开启或关闭锁。spa

这个特性是在MySQL5.5引入的,目的是为了解决一张表同时在作查询和修改表结构,这种状况一定会形成查询结果跟表结构没法对应。.net

因此,当你访问一个表时会默认加上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命令便可。

2、全局锁

在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掉对应的线程便可。

3、行锁

这个场景是很是好模拟的,接下来让咱们一块儿看看

线程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

这个试验就不演示了,复现过程也十分简答能够本身看一下哈!输出结果的最后一行就是解决方案,带着你的答案来到评论区

4、快照读引起的问题

了解过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,意味着数据已经被删除,则不返回数据。

5、总结

本期文章经过MDL锁、全局锁、行锁、undo log说明查询一条数据页迟迟不返回的问题,能够看到大多数都是一些理论知识,有些东西看着看着也就理解其中的含义了。

这里须要注意的是不要把MDL和DML搞混淆了,这但是两个东西,MDL指的是锁、而DML指的是数据库的增删改查。

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