mysql行级锁

2022-09-13 本文已影响0人 sunpy

概念

行级锁就是锁住表中指定行的记录。

行锁原理

InnoDB行锁是通过给索引项加锁来实现的，如果没有建立索引，那么采用表默认的隐式主键来锁定。

innodb引擎提供行锁种类

Record Lock：单个行记录上加锁。锁定的是索引项，如果没有设置索引，将使用隐式的主键锁定。
Gap Lock：间隙锁，不包含本身记录的锁定范围。
Next-Key Lock：Record Lock+Gap Lock。锁定范围且包含自身记录。
InnoDB默认存储引擎是REPEATABLE READ模式，Next-Key Lock算法是默认的行记录算法。

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

行锁的实现方式

共享锁、排他锁

Record Lock（单条记录锁）

锁定范围：当前单条记录。

session1	session2
set autocommit = 0;	set autocommit = 0;
select * from edu_user where id = "5045dfba5f5b4cb5b805c379fc538bcb" for update;
	成功：select * from edu_user;
	成功：select * from edu_user where id = "1234df4446cb4cb6bc2f639830b12345" for update;
	阻塞：select * from edu_user where id = "5045dfba5f5b4cb5b805c379fc538bcb" for update;
commit;
	执行阻塞内容
	commit;

说明：锁住了id为5045dfba5f5b4cb5b805c379fc538bcb的记录，其他行记录可以查询，也可以锁操作、读写。

Next-Key Lock（范围锁）

设计范围锁的目的：解决幻读问题（Phantom Problem）。
什么是幻读：就是在同一事务中，连续执行两次同样的sql语句出现不同结果，第二次sql语句可能会返回之前不存在的行。
正常锁定范围：[prev_val，where_val)、[where_val，post_val)。
范围锁防止幻读：当前会话A会锁定值的前面区域和后面区域，防止在操作过程中，会话B插入了值，造成会话C读取两次结果不同。

操作：

session1	session2
set autocommit = 0;	set autocommit = 0;
select * from edu_test where a_val = '11' for update;
	成功：insert into edu_test select 6, '9', '9';
	成功：insert into edu_test select 7, '14', '14';
	成功：insert into edu_test select 9, '13', '13';
	阻塞：insert into edu_test select 10, '12', '12';
	阻塞：insert into edu_test select 11, '11', '11';
	阻塞：insert into edu_test select 12, '10', '10';
commit
	commit;
	执行阻塞内容

说明：a_val非主键值设置了索引，a_val值为11上排他锁，那么锁定的范围应该为[10，11)、[11,13)。

Next-Key Lock 退化为 Record Lock

结论：当查询的索引含有唯一属性时，innodb会对Next-Key Lock优化，将其降级为Record Lock，即锁住索引本身，而不是范围。
锁定范围：当前单条唯一记录。

操作：

session1	session2
set autocommit = 0;	set autocommit = 0;
select * from edu_test where a_val = '11' for update;
	成功：insert into edu_test select 6, '12', '12';
	commit;
commit

说明：a_val非主键值设置为唯一索引，a_val值为11上了排他锁，那么锁定只为a_val=11的记录，其他记录不锁定，当前锁已经退化为了单条记录锁，如果硬插入a_val=11的记录，那么只会报值重复错误，锁粒度减小，提高了并发性。

Next-Key Lock 退化为 Gap Lock

结论：当查询的索引的值不存在时，那么innodb会对Next-Key Lock优化，将其退化为Gap Lock。
锁定范围：[prev_val，post_val)。

操作：

session1	session2
set autocommit = 0;	set autocommit = 0;
select * from edu_test where a_val = '12' for update;
	成功：insert into edu_test select 6, 9, '9';
	成功：insert into edu_test select 7, 10, '10';
	成功：insert into edu_test select 8, 14, '14';
	成功：insert into edu_test select 9, 13, '13';
	阻塞：insert into edu_test select 10, 11, '11';
	阻塞：insert into edu_test select 11, 12, '12';
commit
	commit;
	执行阻塞内容

说明：a_val非主键值设置了索引，a_val设置了不存在的排他锁，这样innodb会对Next-Key Lock优化，将其退化为Gap Lock。锁定范围是[11, 13)。

order by 排序情况

order by排序会先将当前需要查询的结果排序，然后根据查询索引值前后加锁。

锁定范围：(prev_val，post_val]。

操作：

session1	session2
set autocommit = 0;	set autocommit = 0;
select * from edu_test where a_val = 20 order by a_val desc for update;
	成功：insert into edu_test select 6, 12, '12';
	成功：insert into edu_test select 7, 32, '32';
	成功：insert into edu_test select 8, 31, '31';
	阻塞：insert into edu_test select 9, 13, '13';
	阻塞：insert into edu_test select 10, 25, '25';
commit
	commit;
	执行阻塞内容

分析：先倒序排序

31， 20， 13， 11， 10

说明：当前索引值a_val为20，那么锁定范围为(31，13]。

范围查询情况

锁定范围：(prev_val，val1]、(val1，val2]、(val2，post_val]。

操作：

session1	session2
set autocommit = 0;	set autocommit = 0;
select * from edu_test where a_val < 20 and a_val > 11 order by a_val desc for update;
	成功：insert into edu_test select 6, 31, '31';
	阻塞：insert into edu_test select 7, 10, '10';
commit
	commit;
	执行阻塞内容