mysql锁总结

全局锁

  • 全局锁就是对整个数据库实例加锁。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讲

你可能感兴趣的