参考

https://xiaolincoding.com/mysql/lock/mysql_lock.html

 

 

全局锁（备份数据库）

加全局锁

flush tables with read lock

释放全局锁

unlock tables

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

  • 对数据的增删改操作，比如 insert、delete、update等语句；
  • 对表结构的更改操作，比如 alter table、drop table 等语句。

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

加全局锁又会带来什么缺点呢？

加上全局锁，意味着整个数据库都是只读状态。

那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

但是这样会影响业务，那有什么其他方式可以避免？

如果数据库的引擎支持的事务支持 可重复读的隔离级别，那么在 备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View（可重复读隔离级别的特性），而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

 

表级锁

1、表锁

　　加表锁

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

　　释放表锁

unlock tables

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

 

2、MDL锁（元数据锁--表结构变更）

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

    • 对一张表进行 CRUD 操作时，加的是 MDL 读锁（可以加其它 MDL 读锁，也就是可以进行其它 CRUD。但是不可以加 MDL 写锁，即在这期间不可变更表结构）；
    • 对一张表做 结构变更 操作的时候，加的是 MDL 写锁（不可以加 MDL 读锁，也就是在这期间不可以被 CRUD。当然也不可以加 MDL 写锁）；

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

    • 当有线程在执行 select、insert、delete、update 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select、insert、delete、update 语句（ 释放 MDL 读锁）。
    • 反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。这就会导致一个现象

如果一个进行 CRUD 的长事务A一直没有提交，这时候它长时间持有MDL读锁不释放。此时一个新线程 B 想要变更表结构，申请了 MDL 写锁，就会在队列进行等待。而它后面想要进行 CRUD 操作的所有线程就会一直阻塞住，这时数据库的线程很快就会爆满了。因为它们要申请 MDL 读锁，而在队列里前面有优先级更高的线程 B申请的 MDL 写锁在等待。

 

2、意向锁（快速判断表里是否有记录被加行级X独占锁）

  • 在使用 InnoDB 引擎的表里对某些记录加上 行级「共享锁」之前，需要先在表级别加上一个 表级「意向共享锁」IS；
  • 在使用 InnoDB 引擎的表里对某些纪录加上  行级「独占锁」之前，需要先在表级别加上一个 表级「意向独占锁」IX；

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。不过，select 也是可以对记录加共享锁和独占锁的，需要特殊指定。

也就是，当执行插入、更新、删除操作，需要先加上 表级「意向独占锁」，然后对该记录加 行级独占锁。

意向共享锁和意向独占锁是表级锁

    1. 不会和行级的共享锁和独占锁发生冲突
    2. 意向锁之间也不会发生冲突
    3. 只会和 共享表锁（lock tables ... read）和 独占表锁（lock tables ... write）发生冲突。

它的作用是什么呢？

假如Innodb需要对一个table上一个表锁（例如数据备份、alter table、drop table、 create index 之类的操作，系统会对整个表锁定，这说的表锁不是意向锁，而是独占表锁，请大家不要混淆），就需要先判断是否有某个行被上了行锁，如果有的话，说明某个或某些行正在执行读写操作，系统需要等待这个写操作完成了才能做备份之类的工作，才能加表级X独占锁。

 

3、AUTO-INC 锁（自增主键时 INSERT前加上）

表里的主键设置成自增是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

    • 在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段生成新的自增值
    • 等插入完成后，把 AUTO-INC 锁立即释放掉（不是在一个事务提交后才释放）

也就是说对同一个表的插入操作 生成自增值 和 插入 都是串行的。那么，一个事务在持有 AUTO-INC 锁的过程中（生成自增值和插入操作），要向该表的插入语句都会被阻塞，这样会影响插入性能。

因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，只是生成自增值之后就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

    • 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
    • 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
    • 当 innodb_autoinc_lock_mode = 1：
      • 普通 insert 语句，自增锁在申请之后就马上释放；
      • 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

auto-inc锁和轻量级锁带来的结果是，在「主从复制的场景」中会发生 从库和主库 数据不一致的问题（待研究）。

 

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

 不同的 SQL 语句当然会加不同的锁，总结起来主要分为五种情况：

  • SELECT … 语句正常情况下为快照读，不加锁；
  • SELECT … LOCK IN SHARE MODE 语句为当前读，加 S 锁；
  • SELECT … FOR UPDATE 语句为当前读，加 X 锁；
  • 常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；
  • 常见的 DDL 语句（如 ALTER、CREATE 等）加表级锁，且这些语句为隐式提交，不能回滚。

其中，当前读的 SQL 语句的 where 从句的不同也会影响加锁，包括是否使用索引，索引是否是唯一索引等等。

 

在读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上。

在可重复读隔离级别下，行级锁的种类除了有记录锁，还有间隙锁（目的是为了避免幻读），所以行级锁的种类主要有三类：

行级锁的类型主要有三类：

    • Record Lock，记录锁，也就是仅仅把一条记录锁上；
    • Gap Lock，间隙锁，锁定【两条记录中间的】一个范围，但是不包含记录本身；
    • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定后面闭区间那条记录本身。

1、Record Lock

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 共享锁和 X 排他锁之分的：

 

2、Gap Lock（范围锁，前开后开区间）

Gap Lock 称为间隙锁（顾名思义，为了锁住 两条记录中间不存在的间隙的那段范围），只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁之间是兼容的，间隙锁的意义只在于阻止区间被插入或删除，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

 

3、Next-Key Lock（前开后闭区间，后闭的那条记录即记录锁）

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改和删除 id = 5 这条记录。

 

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

 

4、插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。（？？？？）

 

MySQL 是怎么加行级锁的？

行级锁加锁规则比较复杂，不同的场景，加锁的形式是不同的。

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。

那到底是什么场景呢？总结一句，在能使用记录锁或者间隙锁就能避免幻读、不可重复读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。

有什么命令可以分析加了什么锁？

我们可以通过 select * from performance_schema.data_locks\G; 这条语句，查看事务执行 SQL 过程中加了什么锁。

从上图可以看到，共加了两个锁，分别是：

    • 表锁：X 类型的意向锁；
    • 行锁：X 类型的记录锁；

这里我们重点关注行级锁，图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思。

通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

    • 如果 LOCK_MODE 为 X，说明是 next-key 锁；
    • 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
    • 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；
如果是二级索引项加行级锁，而且还会对查询到的记录的主键索引项上加「记录锁」。
（如果是二级唯一索引，在二级索引上加锁规则 和 主键索引的相同）

1、唯一索引等值查询

  • 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
  • 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

（1）记录存在

这个很好理解，因为查的只是这一条数据，所以 记录锁就够了

怎么用 next-key lock 退化的语言描述？

唯一索引 等值查询，当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。

 

（2）记录不存在

查询不存在于表里的 id = 2 的记录

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id = 2 for update;
Empty set (0.03 sec)

此时事务 A 在 id = 5 记录（第一条大于该查询记录的记录）的主键索引上加的是间隙锁，锁住的范围是 (1, 5)。

接下来，如果有其他事务插入 id 值为 2、3、4 这一些记录的话，这些插入语句都会发生阻塞。

注意，如果其他事务插入的 id = 1 或者 id = 5 的记录话，并不会发生阻塞，而是报主键冲突的错误，因为表中已经存在 id = 1 和 id = 5 的记录了。

唯一索引 等值查询，当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

 

2、唯一索引范围查询

（1）针对「大于或者大于等于」的范围查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id > 15 for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 20 | 香克斯    |  39 |
+----+-----------+-----+
1 row in set (0.01 sec)

查找大于 15 的记录

 

 

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id >= 15 for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 20 | 香克斯    |  39 |
+----+-----------+-----+
1 row in set (0.01 sec)

查找大于等于 15 的记录

 (10, 15] 的 next-key lock 退化为了 15 上的记录锁

唯一索引 范围查询，在满足一些条件的时候，索引的 next-key lock 退化为间隙锁（这里×）或者记录锁（这里√ (10, 15] 的 next-key lock 退化为了 15 上的记录锁）。

 

（2）针对「小于或者小于等于」的范围查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id < 6 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飞   |  19 |
|  5 | 索隆   |  21 |
+----+--------+-----+
3 rows in set (0.00 sec)

查找 id < 6 的记录（id = 6 这条记录不存在）

（5,10] 的 next key lock 退化为了 (5,10) 的间隙锁

唯一索引范围查询，在满足一些条件的时候，索引的 next-key lock 退化为间隙锁（这里√ （5,10] 的 next key lock 退化为了 (5,10) 的间隙锁）或者记录锁（这里×）。

　　

 

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id <= 5 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飞   |  19 |
|  5 | 索隆   |  21 |
+----+--------+-----+
2 rows in set (0.00 sec)

查找 id <= 5 的记录（id = 5 这条记录存在）

这里没有发生退化

唯一索引 范围查询，在满足一些条件的时候，索引的 next-key lock 退化为间隙锁（这里×）或者记录锁（这里×）。

 

3、非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有 满足查询条件的记录 才会对它们的 主键索引加锁。

（1）记录不存在

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age = 25 for update;
Empty set (0.00 sec)

查找 age = 25 的记录（age = 25 的记录不存在）

 

和之前唯一索引类似，给 age = 25 所在的间隙加上间隙锁

非唯一索引 等值查询，当查询的记录「不存在」时，扫描到第一条大于条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

此时，如果有其他事务插入了 age 值为 23、24、25、26、....、38 这些新记录，那么这些插入语句都会发生阻塞。

不过对于 插入 age = 22 或 age = 39 记录 的语句，在一些情况是可以成功插入的，而一些情况则无法成功插入，这是为什么呢？

 　　二级索引树是按照二级索引值（age列）按顺序存放的，在相同的二级索引值情况下， 再按主键 id 的顺序存放。

间隙锁两端的记录是   (id=10, age=22)  和  （id=20,age=39），要插到这两条记录中间的都不能成功。也就是说：

    • 对于那些 age=22 的，如果 id<10 可以成功，如果 id>10 不可以成功
    • 对于那些 age=39 的，如果 id>20 可以成功，如果 id<20 不可以成功

分析结果中，LOCK_DATA 有两个数：

    • 第一个数 39 代表在二级非唯一索引 age 上的间隙锁 (22, 39)
    • 第二个数 20 代表在二级非唯一索引 age 上的 (10,20) 的 id 也不可以插入

 

（2）记录存在

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age = 22 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 10 | 山治   |  22 |
+----+--------+-----+
1 row in set (0.00 sec)

查找 age = 22 的记录（age = 22 的记录存在）

非唯一索引 等值查询，当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录。从扫描到的第一个等于条件的开始，到扫描到第一个不等于条件的停止扫描，然后在扫描的过程中，对扫描到的等于条件的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。

如上图，第一条不满足条件（age = 22 ）的记录 （age = 39, id = 20）上的 next-key lock 退化为了间隙锁

由于是非唯一索引，两个间隙锁的 LOCK_DATA 第二条是怎样的？

    • next-key 锁 (21,22] 的 LOCK_DATA 是 22，10
      • age = 21, id<5 的可以插入, age>5 的不可以插入
      • age = 22 的都不可以插入
    • 间隙锁 (22,39) 的 LOCK_DATA 是 39，20
      • age = 39, id>20 的可以插入，age<20 的不可以插入

 

 

4、非唯一索引范围查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age >= 22  for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 10 | 山治      |  22 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
2 rows in set (0.01 sec)

查找 age >= 22 的记录（age = 22 的记录存在）

非唯一索引 范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。（因为要防止在范围的边缘插入多条和边缘一样的记录）

 

5、没有加索引的查询和更新

如果 锁定读时 查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

因此，在 线上在执行 update、delete、select ... for update 等具有加锁性质的语句（单纯的 select 现在都不是锁定读了，而是无锁化的 MVCC 快照读），一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

 

 

 

 

总结

唯一索引等值查询：

  • 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
  • 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

非唯一索引等值查询（条件里的一个等值可能对应多条记录）：

  • 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录。从扫描到的第一个等于条件的开始，到扫描到第一个不等于条件的停止扫描，然后在扫描的过程中，对扫描到的等于条件的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
  • 当查询的记录「不存在」时，扫描到第一条大于该查询记录的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

  • 唯一索引在满足一些条件的时候，索引的 next-key lock 会退化为间隙锁或者记录锁。
  • 非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。
    • 如下图，查 age>=22
    • 如果 age 是 唯一索引，age = 22 上的 next-key lock 会退化为记录所
    • 如果 age 是非唯一索引，age = 22 上的 next-key lock 就不会退化