作者:叨B叨B叨叨叨 | 来源:互联网 | 2023-08-23 11:19
根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。全局锁对整个数据库实例加锁,mysql加全局读锁的方法:flushtableswithreadlock(FT
根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。
全局锁
对整个数据库实例加锁,mysql加全局读锁的方法:flush tables with read lock (FTWRL)
,当需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。
全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都select出来存成文本。
注意,在备份过程中整个库完全处于只读状态,这会让业务系统停摆,所以是很危险的操作。
加全局锁有风险。但备份为什么要加锁呢?
假设你现在要维护“极客时间”的购买系统,关注的是用户账户余额表和用户课程表。现在发起一个逻辑备份。假设备份期间,有一个用户,他购买了一门课程,业务逻辑里就要扣掉他的余额,然后往已购课程里面加上一门课。如果时间顺序上是先备份账户余额表(u_account),然后用户购买,然后备份用户课程表(u_course),会怎么样呢?你可以看一下这个图:
![在这里插入图片描述](https://img.php1.cn/3cd4a/1eebe/cd5/b428d8f746fb8d47.webp?x-oss-process=image,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NTAyMjQ1,size_16,color_FFFFFF,t_70)
这个备份结果里,用户A的数据状态是“账户余额没扣,但是用户课程表里面已经多了一门课”。如果后面用这个备份来恢复数据的话,用户A就发现,自己赚了。作为用户可别觉得这样可真好啊,你可以试想一下:如果备份表的顺序反过来,先备份用户课程表,再备份账户余额表,发生的就是另一个不好的场景了。
官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction
的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。
有了这个功能,为什么还需要FTWRL呢?一致性读是好,但前提是引擎要支持这个隔离级别。比如,对于MyISAM
这种不支持事务的引擎,如果备份过程中有更新,总是只能取到最新的数据,那么就破坏了备份的一致性。
这时,我们就需要使用FTWRL命令了。所以,single-transaction
方法只适用于所有的表使用事务引擎的库。 如果有的表使用了不支持事务的引擎,那么备份就只能通过FTWRL方法。
既然要全库只读,为什么不使用 set global readOnly=true
的方式呢 ?
建议用FTWRL方式,主要有两个原因:
- 在有些系统中,
readonly
的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改global变量的方式影响面更大,不建议使用。 - 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开,那么MySQL会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为readonly之后,如果客户端发生异常,则数据库就会一直保持readonly状态,这样会导致整个库长时间处于不可写状态,风险较高。
表级锁
MySQL里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。
表锁的语法是表锁的语法是 lock tables ... read/write
。与FTWRL类似,可以用unlock tables
主动释放锁,也可以在客户端断开的时候自动释放。
需要注意,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write;
这个语句,则其他线程写t1、读写t2的语句都会被阻塞。同时,线程A在执行unlock tables
之前,也只能执行读t1、读写t2的操作。连写t1都不允许,自然也不能访问其他表。
在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎,一般不使用lock tables
命令来控制并发,毕竟锁住整个表的影响面还是太大。
另一类表级的锁是MDL(metadata lock)。MDL不需要显式使用,在访问一个表的时候会被自动加上。MDL的作用是,保证读写的正确性。
如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。
因此,在MySQL 5.5版本中引入了MDL,当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。
- 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
- 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。
虽然MDL锁是系统默认会加的,但却是不能忽略的一个机制。
比如下面这个例子,经常有人掉到这个坑里:给一个小表加个字段,导致整个库挂了。
你肯定知道,给一个表加字段,或者修改字段,或者加索引,需要扫描全表的数据。在对大表操作的时候,你肯定会特别小心,以免对线上服务造成影响。而实际上,即使是小表,操作不慎也会出问题。我们来看一下下面的操作序列,假设表t是一个小表。
备注:这里的实验环境是MySQL 5.6。
![](https://img.php1.cn/3cd4a/1eebe/cd5/8ad8f3bf8da691df.webp?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2NTAyMjQ1,size_16,color_FFFFFF,t_70)
- sessionA 先启动,会对表加一个MDL读锁,sessionB启动后也会加一个MDL读锁,读锁之间不互斥,因此可以正常执行;
- 之后sessionC会被blocked,因为sessionA的MDL读锁还没释放,而sessionC需要MDL写锁,读写锁之间是互斥的,因此只能被阻塞。
- 然后sessionD也需要一个MDL读锁,会被sessionC阻塞,也就是说这张表现在完全变成不可读写了。
如果某个表上的查语句比较频繁,而且客户端有重试机制,也就是说超时以后会再起一个session再请求,这个库的线程很快就会爆满。
事务中的MDL锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。
注: DDL语句如修改表结构,增加列等操作是隐式提交的
基于上面的分析,我们来讨论一个问题,如何安全地给小表加字段?
首先我们要解决长事务,事务不提交,就会一直占着MDL锁。在MySQL的information_schema库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行,要考虑先暂停DDL,或者kill掉这个长事务。
但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?
这时候kill可能未必管用,因为新的请求马上就来了。比较理想的机制是,在alter table语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到MDL写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。
MariaDB已经合并了AliSQL的这个功能,所以这两个开源分支目前都支持DDL NOWAIT/WAIT n这个语法。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
小结
本文介绍了MySQL的全局锁和表级锁。全局锁主要用在逻辑备份过程中。对于全部是InnoDB引擎的库,建议选择使用–single-transaction
参数,对应用会更友好。
表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有locktables这样的语句,你需要追查一下,比较可能的情况是:要么是你的系统现在还在用MyISAM这类不支持事务的引擎,那要安排升级换引擎;要么是你的引擎升级了,但是代码还没升级。我见过这样的情况,最后业务开发就是把locktables 和 unlock tables 改成 begin 和 commit,问题就解决了。MDL会直到事务提交才释放,在做表结构变更的时候,你一定要小心不要导致锁住线上查询和更新。