MySQL强人“锁”难《死磕MySQL系列 三》

2021年11月26日 阅读数:3
这篇文章主要向大家介绍MySQL强人“锁”难《死磕MySQL系列 三》,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

系列文章

1、原来一条select语句在MySQL是这样执行的《死磕MySQL系列 一》mysql

2、一辈子挚友redo log、binlog《死磕MySQL系列 二》sql

前言

最近数据库总是出现下面死锁状况,借着这俩种状况出发详细的理解一下MySQL中的锁。数据库

Lock wait timeout exceeded; try restarting transaction安全

Deadlock found when trying to get lock; try restarting transaction数据结构

1、MySQL中有那些锁

全局锁架构

根据全局两个字,就能够确定的是给一个总体加上锁。全局锁就是对整个数据库实例加锁。并发

对于flush tables with read lock,执行完成后整库就处于只读状态,全部语句将被堵塞,包括增删改查、建立表、修改表结构等语句。工具

表锁学习

表锁你们都很是熟悉了,执行命令lock tables kaka read ,kaka2 write直到unlock tables以前,其它线程是没法对kaka写kaka2读的。url

执行命令的这个线程也只能够对kaka读,kaka2写。

行锁

行锁是在引擎层由各个引擎本身实现的。在MySQL中Innodb存储引擎支持行锁,若不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任什么时候刻只能有一个更新在执行,这就会影响到业务并发度。(因为篇幅的缘由,下期细谈)

2、全局锁

演示执行flush tables with read lock命令后数据库处于什么状态。

终端1执行全局锁命令

端口2执行删除操做,它不会直接执行成功,而是在端口1解锁后返回。

这个SQL须要3分钟的执行时间,这3分钟就是咔咔打开终端2并链接数据库的时间。

如今见证了开篇所说的全局锁直接让整个库处于只读状态,这里只演示了删除操做其它的几个操做本身尝试一下。

在蒋老师的文章中看到全局锁最典型的场景是用于逻辑备份,便是将整个库的每个表都select存储成文本。

如今,你想一想这种场景是在什么须要下出现的。

假如只有一个主库,执行了全局锁整库处于只读状态,那么业务基本停摆,产品没法使用。

此时你会有疑问我在从库上备份啊!备份期间,不能执行主库同步过来的binlog的,数据量若是很是大,将引起主从延迟过大,必须进行全量备份。

以上是全局锁引起的负面状况,但再看备份不加全局锁会出现什么问题。

相信大多数小伙伴都开发过支付类项目,接下来就用支付案例让你们很清晰的理解备份不加全局锁引起的问题。

发起一个逻辑备份。若是一个用户在备份期间购买了你公司的服务,在业务逻辑先扣除用户余额,而后给用户添加你公司对应的产品。

显然,这个逻辑没有问题的,但在特殊案例下执行备份操做就会引起问题。

若在时间顺序上先备份用户余额,而后用户发起购买,接着备份用户购买的产品表。

一个很是清晰的问题出现了,用户余额没减成功但用户却得到了对应的产品。

从用户的角度出发那是赚大发了,但这种执行顺序若是反过来的话就会产生不同的结果。

先备份用户产品表,而后备份用户余额表,就会出现用户钱花了东西没得着,这还得了,用户都是衣食父母这不是再割父母的韭菜。

也就是说,在备份不加锁的话,不一样表之间的执行备份的顺序不一样,若是某个表在备份的过程当中进行了更新而且成功备份而关联的表已经备份完成没法再进行跟新,此时就会出现数据不一致。

在MVCC那篇文章中提到了一个很是重要的概念一致性视图(read view),一致性视图是根据快照读那一刻全部未提交事务的集合,前提是隔离级别为可重复。

这时你应该知道要说什么了,没错就是官方大大给提供的逻辑备份工具mysqldump。

mysqldump的备份原理是经过协议链接到 MySQL 数据库,将须要备份的数据查询出来,将查询出的数据转换成对应的insert 语句,当咱们须要还原这些数据时,只要执行这些 insert 语句,便可将对应的数据还原。

例如备份test库的命令为mysqldump -uroot -p test > /backup/mysqldump/test.db

当mysqldump使用参数--single-transaction时,备份数据以前会启动一个事物,拿到一致性视图(read view),因此在整个备份的过程当中是支持更新的。

既然有了官方大大提供的mysqldump工具为什么还要使用flush tables with read lock来将整表锁住呢?

别忘记了刚提到的能够在备份过程当中进行更新,能够更新的前提是能够获得一致性视图,获取一致性视图的前提是开启事务。这里你应该清楚,不是全部存储引擎都支持事物。

若是有的表使用了别的存储引擎不支持事物,那么就只能使用flush tables with read lock方法,说到这里但愿你们尽可能在建立表时都选择Innodb存储引擎。

看着好一会了,还能记得我们要干什么吗?需求是全库处于只读状态。

若是你搭建过MySQL的主从架构,就会知道主库用来写数据,从库用来读数据而且从库不支持写入操做,能够实现这样的效果都是来自于参数readonly。

一样执行set global readonly=true也能够达到整库只读状态,那么为何从一开始没有给你们说这个方案,那是有缘由的。

一是,刚刚提到的搭建主从架构须要使用readonly来判断主库于从库。

二是,在异常处理的方式不一样。若是使用flush talbes with read lock命令客户端异常后MySQL会自动释放全局锁,让整个库回到正常状态。而整库设置为readonly后,一旦发生异常就会一直处于只读状态,致使整库长时间处于不可写状态。

因此说数据库一旦加上全局锁后数据的增删改、修改表结构、修改字段等操做都会被锁住。

3、表锁

表锁跟全局锁释放的命令一致unlock tables,一样客户端断开的时候也会自动释放。

在老一辈的革命前辈处理并发都是用的表锁,应该都知道锁表的影响虽不及锁库影响大,但在今天锁的粒度已经支持到行锁了(前提是使用Innodb存储引擎,就不必再使用行锁来处理并发了。

再来看表锁中的另外一位哥们“元数据锁”(metalock)简称“MDL”,这个锁估计不多人知道,由于在实际开发过程当中是不会有实际的语法来开启或关闭。

这个特性是在MySQL5.5版本后引入的,就是为了解决A线程正在查询一个表的数据,在这期间B线程修改了表的数据结构,那么就会形成查询的结果跟表结构对不上,这确定是不行的。

当你访问一个表时会默认加上MDL写锁,无论在任什么时候候记住读锁于读锁之间不互斥,读锁与写锁,写锁与写锁之间互斥,知道行锁的共享锁、排它锁也是这么个理。

那么MDL 不须要显示调用,那它是在何时释放的?

回答是:“MDL 是在事务提交后才会释放,这意味着事务执行期间,MDL 是一直持有的。”

那么看一个场景。

首先,线程A开启事务并执行查询语句时,对表加上了MDL锁。

而后,线程B执行的是查询,并不会堵塞住,由于读与读并不冲突。

接着,线程C修改表结构,此时的线程A还未提交事务,MDL还未释放,这时的线程因没法获取到MDl写锁,就会被阻塞。

最后线程D执行查询会发生什么呢?

答案是堵塞。

到这里按照正常的逻辑,线程C没有获取到MDL的写锁,线程D是能够申请到MDL读锁的,那为何还会堵塞呢!

这是由于申请MDL锁的操做会造成一个队列,队列中写锁获取优先级高于读锁,一旦出现MDL写锁等待,会阻塞后续该表的全部CURL操做

到这里你有没有后背发凉,一旦你在一个未提交事务以后执行了DDL操做,那么等到的结果就是MySQL挂掉,客户端会有重试机制,DDL后全部CURD会在超时后从新发起请求,这个库的线程会很快爆满。

既然这样如何给表安全的执行DDL操做呢?

首先,必须解决到长事务,事务不提交MDL锁就没法释放。

而后,在MySQL系统表里找到infomation_schema库中的innodb_trx,能够查看当前正在执行中的事务ID,这个表在事务那期文章中也没少提。

接着,你是否是想kill掉这些长事务而后执行DDL不就得了。

试想一下,当你kill掉的下一刻一个新的事务又进来了,同时你又执行了DDL操做,后果是什么应该清楚了哈!这种操做确定是不行的。

官方大大怎么会容许这种状况发生呢!

因而当你执行DDL操做时alter table kaka wait 30 add name能够加一个等待时间,若是在这个等待时间拿到MDL写锁最好,拿不到也不能堵塞后边的业务逻辑,先放弃。再重试执行这个命令。

4、总结

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