全局锁

  • 全局锁就是对整个数据库实例加锁。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);
idcd
000
555
101010
151515
202020
252525

惟一索引等值查问间隙锁

sessionAsessionBsessionC
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无影响

非惟一索引等值查问间隙锁

sessionAsessionBsessionC
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会被锁住

主键范畴查问

sessionAsessionBsessionC
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会被锁住

非惟一索引范畴查问

sessionAsessionBsessionC
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有两行

idcd
101010
301030

进行如下操作

sessionAsessionBsessionC
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执行的语句

sessionAsessionBsessionC
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实质是分为间隙锁+行锁两个步骤来执行的

sessionAsessionB
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讲