全局锁
- 全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是
Flush tables with read lock
(FTWRL)。 当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:
- 数据更新语句(数据的增删改)
- 数据定义语句(包括建表、修改表结构等)
- 更新类事务的提交语句
使用场景
全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本
弊端
- 如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆
如果你在从库上备份,那么备份期间从库不能执行主库同步过来的 binlog,会导致主从延迟
改进方式
使用可重复读隔离级别备份时也可以得到一致性视图
一致性读是好,但前提是引擎要支持这个隔离级别。比如,对于 MyISAM 这种不支持事务的引擎,如果备份过程中有更新,总是只能取到最新的数据,那么就破坏了备份的一致性。这时,我们就需要使用 FTWRL 命令了
全局锁和set global readonly=true的区别
- 有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改 global 变量的方式影响面更大
在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开,那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这样会导致整个库长时间处于不可写状态,风险较高
表级锁
表锁
- 表锁的语法是
lock tables … read/write
。与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作,不能访问其他表
元数据锁
- MDL 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性
如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。因此,在 MySQL 5.5 版本中引入了 MDL
当对一个表做增删改查操作的时候,加 MDL 读锁;
- 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查
当要对表做结构变更操作的时候,加 MDL 写锁
- 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行
元数据锁是server层的锁,与存储引擎无关
如何安全地给小表加字段
- 首先我们要解决长事务,事务不提交,就会一直占着 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 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如 MyISAM 引擎就不支持行锁
- 不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB 是支持行锁的,这也是 MyISAM 被 InnoDB 替代的重要原因之一
在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议
如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
可重复读模式下,行锁加锁规则
- 原则 1:加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
- 原则 2:查找过程中访问到的对象才会加锁。
- 优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
- 优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
- 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
举例分析
假设有表
CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`)) ENGINE=InnoDB;insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
id | c | d |
---|---|---|
0 | 0 | 0 |
5 | 5 | 5 |
10 | 10 | 10 |
15 | 15 | 15 |
20 | 20 | 20 |
25 | 25 | 25 |
唯一索引等值查询间隙锁
sessionA | sessionB | sessionC |
---|---|---|
begin;update t set d = d + 1 where id = 7 | ||
insert into t values(8,8,8) | ||
update t set d = d + 1 where id = 10 |
分析:sessionA的查询是在主键索引上查询,id = 7;
原则1:锁都是以next-key lock为单位,因此锁住了(5,10]
- 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁,本例中id = 7不是该next-key lock左开右闭范围id = 10边界情况,因此无法使用
- 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁;本例中,id = 7在(5,10]这个范围中,id = 7向右遍历,即遍历到(5,10]范围的最后一个值id = 10时,不满足等值查询条件id = 7,因此next-key lock退化为间隙锁,即(5,10)
- 所以最终锁住的区域是(5,10),sessionB被阻塞,sessionC无影响
非唯一索引等值查询间隙锁
sessionA | sessionB | sessionC |
---|---|---|
begin;select id from t where c = 5 lock in share mode; | ||
update t set d = d + 1 where id = 5; | ||
insert into t values(7,7,7) |
sessionA在非唯一索引c上进行查询
- 原则1,锁是以next-key lock为基本单位,本例中next-key lock为(0,5]
- c不是唯一索引,目前在(0,5]范围中,需要再往后遍历,直到遇到第一个不满足条件的值,即(5,10]
- 索引上的等值查询,c=5,可以将c=10的行排除,next-key lock退化为间隙锁,所以最后的区间为(0,5) (5,10)
sessionB的查询条件是主键id,sessionA的锁都是在c列上加的锁,所以sessionB不会被锁住。这里主要是因为使用的是共享锁,即lock in share mode,并且走的是覆盖索引不需要主键索引,mysql的共享锁不会去额外锁主键,只锁本次需要的索引;若使用的是for update排他锁,系统会认为你接下来会去更新数据,因此还会去锁主键
sessionC在之前分析的间隙锁的区间范围内,因此sessionC会被锁住
主键范围查询
sessionA | sessionB | sessionC |
---|---|---|
begin;select * from t where id >= 10 and id < 11 for update; | ||
insert into t values(8,8,8);insert into t values(13,13,13) | ||
update t set d = d + 1 where id = 15 |
- sessionA的id是主键索引,id >= 10,可以分解为id = 10,id > 10。id=10的时候,next-key lock为(5,10],对于唯一索引的等值查询,退化为行锁,所以只锁id=10的行;对于id > 10,间隙锁为(10,15]
- 范围id < 11,next-key lock也为(10,15]
- 综上,最后id=10有行锁、(10,15]有next-key lock
- 所以,sessionB的(8,8,8)可以插入,(13,13,13)会被锁住;sessionC会被锁住
非唯一索引范围查询
sessionA | sessionB | sessionC |
---|---|---|
begin;select * from t where c >= 10 and c < 11 for update; | ||
insert into t values(8,8,8); | ||
update t set d = d + 1 where c = 15 |
- c >= 10,分解为c = 10和c > 10;
- c = 10,此时next-key lock为(5,10];c > 10,此时next-key lock为(10,15]
- c < 11,此时next-key lock为(10,15]
因此最终的锁定为next-key lock (5,10],(10,15]
sessionB和sessionC都会被锁定
非唯一索引的等值情况
insert into t values(30,10,30);
插入该行数据后,目前c=10有两行
id | c | d |
---|---|---|
10 | 10 | 10 |
30 | 10 | 30 |
进行如下操作
sessionA | sessionB | sessionC |
---|---|---|
begin;delete from t where c = 10; | ||
insert into t values(12,12,12); | ||
update t set d = d + 1 where c = 15 |
首先c=10,next-key lock为(5,10],并且这里的10是(c=10,id=10)、(c=10,id=30)
由于c是非唯一索引,继续向右找,next-key lock为(10,15],由于此时是等值查询c=10,因此排除掉右边的15,最后退化为间隙锁(10,15)
此时的锁定范围为索引c的next-key lock (5,10],间隙锁(10,15)
从索引c的视角来看,锁定的范围是((c=5,id=5),(c=10,id=10),(c=10,id=30)、(c=15,id=15))
sessionB要插入(12,12,12),在当前的锁定范围内,因此会被阻塞
sessionC要插入id=15,不在锁定范围内,正常执行
limit语句加锁
同上例,但是改变sessionA执行的语句
sessionA | sessionB | sessionC |
---|---|---|
begin;delete from t where c = 10 limit 2; | ||
insert into t values(12,12,12); |
表里c = 10的记录有两条,加不加limit都不影响最终效果,但是会影响加锁的情况
首先还是分析c=10的next-key lock,此时锁定的范围是(5,10],本来c还会继续去锁定下一个区间,但是由于加了limit,只会去检查c=10的前两个结果,正好是(c=10,id=10),(c=10,id=30)两个结果,因此就不再去锁定下一个(10,15]区间了,所以最终的锁定范围是((c=5,id=5),(c=10,id=10),(c=10,id=30)],因此sessionB的插入可以成功
死锁的例子
注:next-key lock本质是分为间隙锁+行锁两个步骤来执行的
sessionA | sessionB |
---|---|
begin;select id from t where c= 10 lock in share mode; | |
update t set d = d + 1 where c = 10; | |
insert into t values(8,8,8) | |
ERROR deadlock found |
首先sessionA会加next-key lock (5,10]、间隙锁(10,15)
sessionB执行更新操作时,也要加next-key lock,步骤是先加间隙锁再加行锁;第一个next-key lock为(5,10],此时先加(5,10)间隙锁再加10行锁,由于间隙锁之间不互斥,所以(5,10)加锁成功,但是加10行锁的时候,因为sessionA已经锁住了10,所以此时被阻塞,导致sessionB后续的(10,15]next-key lock也暂时无法加上
sessionA此时要插入(8,8,8),被sessionB的间隙锁锁住,此时出现死锁
参考文章
极客时间:mysql实战45讲
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。