乐趣区

关于mysql:mysql锁总结

jiezi

全局锁

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

退出移动版