mysql锁-这条sql加了哪些锁

InnoDB 存储引擎中的行锁的加锁规则。

1、 InnoDB的三种行锁

Record Lock（记录锁）：锁住某一行记录
Gap Lock（间隙锁）：锁住一段左开右开的区间
Next-key Lock（临键锁）：锁住一段左开右闭的区间 （next-key lock 实际就是 间隙锁+记录锁）

2、常见的加锁语句

2.1、常见隐式加锁语句

1）、党见的DML语句（update、delete、insert），InnoDB 会自动给相应的记录行加写锁
2）、默认情况下对于普通 SELECT 语句，InnoDB 不会加任何锁，但是在 Serializable 隔离级别下会加行级读锁

2.1、常见显示加锁语句

1）、SELECT * FROM table_name WHERE … FOR UPDATE，加行级写锁

2）、SELECT * FROM table_name WHERE … LOCK IN SHARE MODE，加行级读锁

3、加锁的2条规则

1）查找过程中访问到的对象才会加锁

2）加锁的基本单位是 Next-key Lock

4、案例

一张order表，id为主键（唯一索引），order_id普通索引（非唯一索引），remark普通列（无索引）

id	order_id	remark
10	4	a
15	8	b
20	16	c
25	32	d
30	64	e

4.1、唯一索引等值查询

唯一索引等值查询时，查询的记录是否存在，加锁的规则也会不同：
1）查询记录存在时，Next-key Lock会退化成记录锁
2）查询记录不存在时，Next-key Lock会退化成间隙锁

• 查询记录存在

 SELECT * from t_order to2 where id = 25 for update;

结合加锁的两条核心：查找过程中访问到的对象才会加锁 + 加锁的基本单位是 Next-key Lock（左开右闭），我们可以分析出，这条语句的加锁范围是 (20, 25]

不过，由于这个唯一索引等值查询的记录 id = 25 是存在的，因此，Next-key Lock 会退化成记录锁，因此最终的加锁范围是 id = 25 这一行

可以在mysql客户端开启2个事物验证：

事物一：
start transaction; 
//锁住记录Id= 25的这条
select * from t_order to2 
where id = 25
for update
commit;
事物二：
start transaction; 
//可以插入成功
insert into t_order(id,order_id,remark )values('21','44','f')
rollback;

• 查询的记录不存在

再来看查询的记录不存在的案例：

SELECT * from t_order to2 where id = 21;

结合加锁的两条核心：查找过程中访问到的对象才会加锁 + 加锁的基本单位是 Next-key Lock（左开右闭），我们可以分析出，这条语句的加锁范围是 (20, 25]

为什么是 (20，25] 而不是 (20, 22]，因为 id = 22 的记录不存在，InnoDB 先找到 id = 20 的记录，发现不匹配，于是继续往下找，发现 id = 25，因此，id = 25 的这一行被扫描到了，所以整体的加锁范围是 (20, 25]

事物一：
start transaction; 
//锁住记录Id= 25的这条
select * from t_order to2 
where id = 25
for update
commit;
事物二：
start transaction; 
//阻塞，等待事物一提交，
insert into t_order(id,order_id,remark )values('21','44','f')
rollback;

4.2、唯一索引范围查询

唯一索引范围查询的规则和等值查询的规则一样，只有一个区别，就是唯一索引的范围查询需要一直向右遍历到第一个不满足条件的记录。

select * from t_order to2 
where id >= 20 and id < 22
for update;

先来看语句查询条件的前半部分 id >= 20，因此，这条语句最开始要找的第一行是 id = 20，结合加锁的两个核心，需要加上 Next-key Lock (15,20]。又由于 id 是唯一索引，且 id = 20 的这行记录是存在的，因此会退化成记录锁，也就是只会对 id = 20 这一行加锁。

再来看语句查询条件的后半部分 id < 22，由于是范围查找，就会继续往后找第一个不满足条件的记录，也就是会找到 id = 25 这一行停下来，然后加 Next-key Lock (20, 25]，重点来了，但由于 id = 25 不满足 id < 22，因此会退化成间隙锁，加锁范围变为 (20, 25)。
所以，上述语句在主键 id 上的最终的加锁范围是 Record Lock id = 20 以及 Gap Lock (20, 25)

4.3、非唯一索引等值查询

非唯一索引进行等值查询的时候，根据查询的记录是否存在，加锁的规则会有所不同：
1、当查询的记录是存在的，除了会加 Next-key Lock 外，还会额外加间隙锁（规则是向下遍历到第一个不符合条件的值才能停止），也就是会加两把锁

查找记录的左区间加 Next-key Lock，右区间加 Gap lock

2、当查询的记录是不存在的，Next-key Lock 会退化成间隙锁（这个规则和唯一索引的等值查询是一样的）

• 查询记录存在

select * from t_order to2 
where order_id =16
for update;

结合加锁的两条核心，这条语句首先会对普通索引 a 加上 Next-key Lock，范围是 (8,16]

又因为是非唯一索引等值查询，且查询的记录 order_id = 16 是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是 (16,32)

所以，上述语句在普通索引order_id 上的最终加锁范围是 Next-key Lock (8,16] 以及 Gap Lock (16,32)。

验证，开启2个事物：

事物一：
start transaction; 
select * from t_order to2 
where order_id =16
for update;
commit;事物二：
start transaction; 
//阻塞，等待事物一提交
INSERT into t_order(id,order_id ,remark )values ('35','9','ffff')
commit;

• 查询记录不存在

select * from t_order to2 
where order_id =18
for update;

结合加锁的两条核心，这条语句首先会对普通索引 order_id 加上 Next-key Lock，范围是 (16,32]

但是由于查询的记录order_id = 18 是不存在的，因此 Next-key Lock 会退化为间隙锁，即最终在普通索引 a 上的加锁范围是 (16,32)。

4.4、非唯一索引范围查询

范围查询需要一直向右遍历到第一个不满足条件的记录，和唯一索引范围查询不同的是，非唯一索引的范围查询并不会退化成 Record Lock 或者 Gap Lock。

start transaction; 
select * from t_order to2 
where order_id >=16 and order_id <18
for update;

先来看语句查询条件的前半部分 order_id >= 16，因此，这条语句最开始要找的第一行是 order_id = 16，结合加锁的两个核心，需要加上 Next-key Lock (8,16]。虽然非唯一索引 order_id = 16 的这行记录是存在的，但此时并不会像唯一索引那样退化成记录锁。

再来看语句查询条件的后半部分 order_id < 18，由于是范围查找，就会继续往后找第一个不满足条件的记录，也就是会找到 id = 32 这一行停下来，然后加 Next-key Lock (16, 32]。虽然 id = 32 不满足 id < 18，但此时并不会向唯一索引那样退化成间隙锁。

所以，上述语句在普通索引 a 上的最终的加锁范围是 Next-key Lock (8, 16] 和 (16, 32]，也就是 (8, 32]。