Mysql的锁以及sql的加锁类型

1 Mysql锁类型

1.1 锁类型划分

讨论一下mysql的 InnoDB的锁分类情况。mysql不同的存储引擎有不同的锁机制。

如上数据库有众多锁名称，主要是不同的分类导致的。实际上一个锁要么是共享锁（S），要么是排他锁（S）；一个锁要么是行锁，要么是表锁，要么是页锁。我们从具体的加锁方式讨论，研究一下锁模式中的各种锁类型。
行锁最终是加在索引上的。

1. 间隙锁和任何锁都兼容——间隙锁必然可以加上
2. 记录锁和带记录的锁冲突（r锁、next-key锁）——记录冲突了就加不上了
3. next-key和带记录的锁冲突（nextkey、r锁）——分间隙和记录两步枷锁分析
4. 插入意向锁和带间隙的锁冲突

共享锁或排它锁(Shared and Exclusive Locks)并不是具体的锁，它是锁的模式，用来“修饰”其他各种类型的锁。

1.2 记录锁（Record Locks）

记录锁会锁住索引的记录。表没有索引时，mysql默认会创建的隐藏聚集索引。用于阻止插入、更新、删除记录。
LOCK_MODE分别是：S,REC_NOT_GAP或X,REC_NOT_GAP。

1.3 间隙锁（Gap Locks）

间隙锁会锁住索引之间的间隙，也可以锁第一个索引之前、最后一个索引之后（开区间）。例如索引 id ，锁住id的范围是(4，7)、(-∞ ，1)、(100， +∞)。间隙锁用于阻止插入，锁住的范围不允许做插入，不允许获取插入意向锁。

有排他或共享两种模式，但两种模式没有任何区别，二者等价。
LOCK_MODE分别是：S,GAP或X,GAP。

1.4 临界锁（Next-Key Locks）

临界锁是间隙锁和记录锁的组合。临界锁锁住当前索引记录 + 索引记录前的间隙。默认情况，临界锁使用在可重复读 REPEATABLE READ事务隔离级别。
例如：索引记录包括10、13、20，临界锁有可能加锁的区间如下：

（-∞，10]，(10，13]，(13，-∞)

1.5 意向锁（Intention Locks）

innoDB支持不同粒度的锁定，允许行锁和表锁共存。为了实现不同粒度的锁定，使用意向锁实现。
意向锁是表级锁，用于表明事务稍后对表中的记录使用锁（共享、排他）。

意向锁有两种：

• 意向共享锁：表明事务将对表中记录使用共享锁。
• 意向排他锁：表明事务将对表中记录使用排他锁。

意图锁定协议如下：

• 在事务可以获取表中行的共享锁之前，它必须首先获取IS表上的锁或更强的锁。
• 在事务获得表中行的排他锁之前，它必须首先获得IX 表的锁。

意向锁要解决的问题
事务加表级锁时，快速判断表是否存在行级锁。
没有意向锁时，需要逐行判断是否加锁，效率低。意向锁类似一个加锁的标记，直接判断表是否加锁。

意向锁的兼容性

1. 意向锁之间完全兼容
2. 意向锁和行级锁完全兼容
3. 意向锁只和表级锁存在某些冲突。如下：
   X: 行级排他锁，S: 行级共享锁。IX: 表级意向排他锁，IS: 表级意向共享锁。

	意向共享锁（IS）	意向排他锁（IX）
共享锁（S）	兼容	冲突
排他锁（X）	冲突	冲突

1.6 插入意向锁（Insert Intention Locks）

插入意向锁是一种特殊的间隙锁，是行锁，在insert之前设置。插入意向锁之间不冲突，只要记录本身（主键、唯一索引）不冲突，多个事务向同一个间隙插入数据，不会等待。
有共享或排他两种模式，但，两种模式没有任何区别，二者等价。

1.7 自增锁（AUTO-INC Locks）

自增锁是一种特殊的表级锁，主要用于事务插入含有自增字段列（AUTO_INCREAMENT）。加自增锁时，其他事务无法插入记录。

mysql8.0文档 innoDB锁 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-locks

2 InnoDB加锁方式

讨论重复读（Repeatable Read）事务隔离级别下 InnoDB存储引擎的加锁方式。不同的事务隔离级别加锁方式不同。

2.1 结论

• 锁定读、更新、删除条件有索引一般在索引上设置记录锁。普通查找不加锁，使用快照读。
• 条件是索引范围，设置临界锁。
• 没有索引可以使用，会锁住所有行。但是仍然是行锁，一般使用多个临界锁实现。
• 插入会设置记录锁，不会使用间隙锁、临界锁。插入前会设置插入意向锁。

2.2 测试表结构

CREATE TABLE `locks_test`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `index` int NOT NULL,
  `name` varchar(255),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx`(`index`)
)

表数据：

2.3 select 查询加锁

普通查不加锁，使用快照读，MVCC（多版本并发控制）实现。

2.4 select…for, update, update , delete 加锁情况

该语句必须在事务里执行。加锁为排他锁。

1. 有索引，且能找到该记录

select * from locks_test where id = 1 for update;

加锁：X,REC_NOT_GAP，记录锁。锁住索引 id = 1的记录

2. 有索引，没有找到该记录

select * from locks_test where id = 3 for update;

加锁：X,GAP，间隙锁。锁住的范围：（2，5），锁住上一个索引和下一个索引的中间范围。没有下一个索引记录，就锁到正无穷。

3. 无索引

select * from locks_test where name = 'aa' for update;

有记录和无记录 加锁：X，临界锁。全表加临界锁。临界锁：(-∞, 1], (1, 2], (2, 5], (5, 6] 。

4. 使用的索引不是主键
   上面使用的都是主键索引，使用普通索引时，找到记录会有特殊处理：对索引加锁，并且对主键索引加锁。没有找到记录不对主键索引处理。

select * from locks_test where `index` = 10 for update;

加锁情况：idx索引加了记录锁，并且对应的主键索引也加了记录锁。实际是相同的记录行。

5. 范围查询

select * from locks_test where id > 2 and id < 6 for update;

有索引加锁：锁住范围内的索引和索引的间隙。
无索引会对所有行对应的索引加临界锁。

锁状态：(2, 5] 的临界锁，(5, 6) 的间隙锁。锁住的范围刚好是 (2, 6)。

2.5 select…lock in share mode 加锁情况

该语句必须在事务里执行。加锁为共享锁。
有索引，有记录，加锁：S,REC_NOT_GAP，共享记录锁。
有索引，无记录，加锁：S,GAP，共享间隙锁。
无索引，加锁：S，共享临界锁。

2.6 insert 插入加锁

insert into locks_test(`index`, `name`) values(12, 'inaa');

一般插入是隐式加锁。执行并不会加锁，只有在有冲突的时候才会先加插入意向锁。其他事务会替插入创建记录锁，将隐式锁转成显示锁

加锁流程

• 先执行事务A

-- 事务A
begin;
select * from locks_test where `index` = 12 for update;

• 在执行事务B

-- 事务B
begin;
insert into locks_test(`index`, `name`) values(12, 'inaa');

事务A加临界锁。事务B发现有锁冲突，锁等待，请求加锁：X,GAP,INSERT_INTENTION，插入意向锁。

• 事务A提交

commit;

事务B执行插入语句，未提交。

• 开启事务C

-- 事务C
begin;
select * from locks_test where `index` = 12  for update;

此时加锁情况：
事务B插入：X, REC_NOT_GAP, 记录锁。 之前的 X,GAP,INSERT_INTENTION，插入意向锁没有释放
事务C：X, REC_NOT_GAP, 记录锁 锁等待，具体加锁类型，根据事务C执行的SQL确定。
事务C，尝试对记录加锁。先判断记录上的事务ID是否提交。事务B没有提交，事务C会将事务B的隐式锁转换成显示锁，为其创建 X, REC_NOT_GAP, 记录锁。事务C自身加锁X, REC_NOT_GAP, 记录锁，进入锁等待状态。

多个insert加锁情况

插入同一个间隙，插入间隙锁相互兼容，只要没有key重复冲突，可以插入成功。

有key重复冲突时

• 事务A 插入索引12的记录

begin;
insert into locks_test(`index`, `name`) values(12, 'inaa');

没有锁。

• 事务B 插入索引12的记录

begin;
insert into locks_test(`index`, `name`) values(12, 'inbb');

加锁情况：
事务B跟事务A有插入索引冲突，将事务A的隐式锁转成显示锁（X,REC_NOT_GAP 记录锁），同时事务B锁等待，加锁：S, 共享临界锁，(11，12]。

• 如果事务A 回滚

rollback;

事务B 加锁：S,GAP， 共享间隙锁，和隐式记录锁（X,REC_NOT_GAP） 索引12。区间：(11, 12), (12, 20)。如果有事务C，会将隐式锁转换成显示锁。

• 如果事务A 提交

commit;

事务B 加锁：S 索引的共享间隙锁，（11，12）；X 主键的排他间隙锁，（6，+∞）；唯一索引冲突，不能插入。

2.7 锁状态查看

SELECT * FROM performance_schema.data_locks;

LOCK_MODE	锁类型
IX	意向排他锁
X	排他 临界锁
X, REC_NOT_GAP	排他 记录锁
X, GAP	排他 间隙锁
X,GAP,INSERT_INTENTION	排他 插入意向锁
IS	意向共享锁
S	共享 临界锁
S, REC_NOT_GAP	共享 记录锁
S, GAP	共享 间隙锁
S,GAP,INSERT_INTENTION	共享 插入意向锁

LOCK_MODE	LOCK_DATA	锁的记录范围
记录锁	20	索引为20的行
间隙锁	20	索引为 ( 11, 20 ) 的行
临界锁	20	索引为 ( 11, 20 ] 的行