【MySQL】MySQL的锁机制

常见问题：

1. MySQL行锁升级会表锁的条件 [属性值重复率高时，MySQL忽略该索引，引发表锁]

一、理论知识

锁总结

1. 共享锁与独占锁（S、X）
2. 意向锁
3. 记录锁（Record Locks）
4. 间隙锁（Gap Locks）
5. Next-Key Locks
6. 插入意向锁
7. 自增锁
8. 空间索引断言锁

来源：MySQL 8.0 参考手册

1.1  为什么要加锁

数据库是一个多用户使用的共享资源，当多个用户并发的存取数据时，在数据库中加锁是为了保证数据库数据的一致性。

数据库有ACID原则(一致性、原子性、隔离性、持久性)，

• 脏读：读取未提交的数据
• 不可重复读：读取已修改的数据
• 幻读：读提交了插入/删除的数据

和标准SQL规范相比，MySQL中可重复读解决了幻读，实现了串行化隔离级别的功能，同时没有严重影响并发。是通过加锁、阻止插入新数据，来解决幻读的。

相关文章

1. 数据库事务的隔离级别

1.2 锁分类

           

(1) 共享锁（S锁）/排他锁（X锁）

• 共享锁（S锁）：也是读锁，事务拿到某一行记录的共享S锁,才可以读取这一行，并阻止别的事务对其添加X写锁，共享锁的目的是提高读读并发
• 排他锁（X锁）：也是写锁，事务拿到某一行记录的排他X锁，才可以修改或者删除这一行，排他锁的目的是为了保证数据的一致性

语义解释： 

• 共享锁之间不互斥，简记为：读读可以并行  select .........lock in share mode

select * from users where id =1 lock in share mode

• 排他锁与任何锁互斥，简记为：写读，写写不可以并行 select ,,, for update

select * from users where id =1 for update

(2) 意向锁( Intention Locks )

InnoDB为了支持多粒度锁机制(multiple granularity locking)，即允许行级锁与表级锁共存，而引入了意向锁(intention locks)。意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

1. 意向锁是一个表级别的锁(table-level locking)；
2. 意向锁又分为：
   • 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁；
   • 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁；

　　加锁的语法为：

select ... lock in share mode;　　要设置IS锁；
select ... for update;　　　　　　 要设置IX锁；

(3) 插入意向锁(Insert Intention Locks)

　对已有数据行的修改与删除，必须加强互斥锁(X锁)，那么对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

 　插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。插入意向锁互相是兼容的，允许相同间隙、不同数据的并发插入

　举个例子: 

• 事务A先执行，在10与20两条记录中插入了一行，还未提交：insert into t values(11, xxx);
• 事务B后执行，也在10与20两条记录中插入了一行：insert into t values(12, ooo);

因为是插入操作，虽然是插入同一个区间，但是插入的记录并不冲突，所以使用的是插入意向锁，此处A事务并不会阻塞B事务。

(4)  记录锁(Record Locks)

• 单行记录上的锁，行锁是加在索引上的。

　  记录锁，它封锁索引记录，例如(其中id为pk)：

create table lock_example(id smallint(10),name varchar(20),primary key id)engine=innodb;

数据库隔离级别为RR，表中有如下数据：

10, zhangsan
20, lisi
30, wangwu

select * from t where id=1 for update;

其实这里是先获取该表的意向排他锁(IX)，再获取这行记录的排他锁(我的理解是因为这里直接命中索引了)，以阻止其他事务插入，更新，删除id=1的这一行。

(5) 间隙锁(Gap Locks)

• 间隙锁，锁定记录之间的范围，但不包含记录本身，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。依然是上面的

间隙锁的主要目的 ：就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

间隙锁（Gap Lock）是Innodb在 可重复读 提交下为了解决幻读问题时引入的锁机制，（下面的所有案例没有特意强调都使用可重复读隔离级别）幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁，间隙锁就是解决这类问题的。在可重复读隔离级别下，数据库是通过行锁和间隙锁共同组成的（next-key lock），来实现的

加锁规则有以下特性，我们会在后面的案例中逐一解释：

1. 加锁的基本单位是（next-key lock）,他是前开后闭原则
2. 插叙过程中访问的对象会增加锁
3. 索引上的等值查询--给唯一索引加锁的时候，next-key lock升级为行锁
4. 索引上的等值查询--向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止

科普知识：主键索引和唯一索引的区别

• 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键

案例数据

id(主键)	c（普通索引）	d（无索引）
5	5	5
10	10	10
15	15	15
20	20	20
25	25	25

以上数据为了解决幻读问题，更新的时候不只是对上述的五条数据增加行锁，还对于中间的取值范围增加了间隙锁，（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+supernum] （其中supernum是数据库维护的最大的值。为了保证间隙锁都是左开右闭原则。）

案例：经典案例

5.1  间隙锁简单案例

步骤	事务A	事务B
1	begin; select * from t where id = 11 for update;	-
2	-	insert into user value(12,12,12) blocked
3	commit;	-

当有如下事务A和事务B时，事务A会对数据库表增加（10，15]这个区间锁，这时insert id = 12 的数据的时候就会因为区间锁（10，15]而被锁住无法执行。

5.2 间隙锁死锁问题

步骤	事务A	事务B
1	begin; select * from t where id = 9 for update;	-
2	-	begin; select * from t where id = 6 for update;
3	-	insert into user value(7,7,7) blocked
4	insert into user value(7,7,7) blocked	-

不同于写锁相互之间是互斥的原则，间隙锁之间不是互斥的，如果一个事务A获取到了（5,10]之间的间隙锁，另一个事务B也可以获取到（5,10]之间的间隙锁。这时就可能会发生死锁问题，如下案例。
事务A获取到（5,10]之间的间隙锁不允许其他的DDL操作，在事务提交，间隙锁释放之前，事务B也获取到了间隙锁（5,10]，这时两个事务就处于死锁状态

5.3 等值查询—唯一索引

步骤	事务A	事务B	事务C
1	begin; update u set d= d+ 1 where id = 7;	-	-
2	-	insert into u (8,8,8); blocked	-
4	-	-	update set d = d+ 1 where id = 10

1.加锁的范围是（5,10]的范围锁
2.由于数据是等值查询，并且表中最后数据id = 10 不满足id= 7的查询要求，故id=10 的行级锁退化为间隙锁，（5,10）
3.所以事务B中id=8会被锁住，而id=10的时候不会被锁住

5.4 等值查询—普通索引

步骤	事务A	事务B	事务C
1	begin; select id form t where c = 5 lock in share mode;	-	-
2	-	update t set d = d + 1 where id = 5	-
4	-	-	insert into values (7,7,7) blocked

1.加锁的范围是（0,5]，（5,10]的范围锁
2.由于c是普通索引，根据原则4，搜索到5后继续向后遍历直到搜索到10才放弃，故加锁范围为（5,10]
3.由于查询是等值查询，并且最后一个值不满足查询要求，故间隙锁退化为（5,10）
4.因为加锁是对普通索引c加锁，而且因为索引覆盖，没有对主键进行加锁，所以事务B执行正常
5.因为加锁范围（5,10）故事务C执行阻塞
6.需要注意的是，lock in share mode 因为覆盖索引故没有锁主键索引，如果使用for update 程序会觉得之后会执行更新操作故会将主键索引一同锁住

原则：

1.  间隙锁可以共存。一个事务进行的间隙锁定不会阻止另一事务对相同的间隙进行间隙锁定。共享和专用间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。
2. 间隙锁定可以显式禁用。如果将事务隔离级别更改为，则会发生这种情况 READ COMMITTED。在这种情况下，将禁用间隙锁定进行搜索和索引扫描，并且仅将其用于外键约束检查和重复键检查

相关文章

1. 间隙锁详解

6. 临键锁(Next-key Locks)

• 记录锁+ 间隙锁，锁定一个范围，包含记录本身。
• 临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

默认情况下，innodb使用next-key locks来锁定记录。但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

举个例子：正如上面的例子，但是id降级为普通索引(key)，也就是说即使这里声明了要加锁(for update)，而且命中的是索引，但是因为索引在这里没有UK约束，所以innodb会使用next-key locks，数据库隔离级别RR：

事务A执行如下语句，未提交：
select * from lock_example where id = 20 for update;

事务B开始，执行如下语句，会阻塞：
insert into lock_example values('zhang',15);

　如上的例子，事务A执行查询语句之后，默认给id=20这条记录加上了next-key lock，所以事务B插入10(包括)到30(不包括)之间的记录都会阻塞。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

1.3 锁粒度比较

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
  • 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 最大程度的支持并发，同时也带来了最大的锁开销。
  • 在 InnoDB 中，除单个 SQL 组成的事务外，
    锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
  • 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

相关文章

1. mysql的常见锁

2. mysql锁机制详解

3. MySQL锁总结

二、小总结

2.1 锁理解

• 记录锁：单行记录上的锁，行锁是加在索引上的。
• 间隙锁：锁定记录之间的范围，但不包含记录本身。
• Next Key Lock （临健锁）: 记录锁+ 间隙锁，锁定一个范围，包含记录本身。

以上总结的7种锁，个人理解可以按两种方式来区分：

　　1. 按锁的互斥程度来划分，可以分为共享、排他锁；

• 共享锁(S锁、IS锁)，可以提高读读并发；
• 为了保证数据强一致，InnoDB使用强互斥锁(X锁、IX锁)，保证同一行记录修改与删除的串行性；

　　2. 按锁的粒度来划分，可以分为：

• 表锁：意向锁(IS锁、IX锁)、自增锁；
• 行锁：记录锁、间隙锁、临键锁、插入意向锁；

　　其中

1. InnoDB的细粒度锁(即行锁)，是实现在索引记录上的(我的理解是如果未命中索引则会失效)；　　
2. 记录锁锁定索引记录；间隙锁锁定间隔，防止间隔中被其他事务插入；临键锁锁定索引记录+间隔，防止幻读；
3. InnoDB使用插入意向锁，可以提高插入并发；
4. 间隙锁(gap lock)与临键锁(next-key lock)只在RR以上的级别生效，RC下会失效；

注意：在《MySQL王者晋级之路》还提到了 MDL锁 ，也叫元数据锁，用于保证表中元数据的信息，在会话A中，表开启了查询事务后，会自动获得一个MDL锁，会话B就不可以执行任何DDL语句的操作，不能执行为表中添加字段的操作，会用MDL锁来保证数据之间的一致性。详情可以参考