MySQL Lock--LOCK IN SHARE MODE和FOR UPDATE加锁对比

测试环境

MySQL版本： 5.7.30
事务级别： READ-COMMITTED

测试数据

mysql> show create table tb1001 G
*************************** 1. row ***************************
       Table: tb1001
Create Table: CREATE TABLE `tb1001` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  `c2` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=524273 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from tb1001 limit 10;
+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 |  1 |  1 |
|  2 |  2 |  2 |
|  3 |  3 |  3 |
| 10 | 10 | 10 |
| 11 | 11 | 11 |
| 12 | 12 | 12 |
| 13 | 13 | 13 |
| 14 | 14 | 14 |
| 15 | 15 | 15 |
| 16 | 16 | 16 |
+----+----+----+
10 rows in set (0.00 sec)

操作对比01

对于不需要回表查询的等值查询，如

select c1 from tb1001 where c1=3 lock in share mode;

产生1条行锁,无间隙锁
在idx_c1上对c1=3的1条记录加锁。

当不需要回表查找时，lock in share mode方式在二级索引上对满足条件的索引记录加锁，不会对满足条件的索引记录的下一条记录加行锁和间隙锁。

select c1 from tb1001 where c1=3 for update;

产生2条行锁,无间隙锁
在idx_c1上对c1=3的1条记录加锁。
在主键上对id=3的1条记录加锁。

当不需要回表查找时，for update方式在二级索引+主键索引上对满足条件的索引记录加锁，不会对满足条件的索引记录的下一条记录加行锁和间隙锁。

操作对比02

对于需要回表查询的等值查询，如：

select * from tb1001 where c1=3 lock in share mode;

产生2条行锁,无间隙锁
在idx_c1上对c1=3的1条记录加锁。
在主键上对id=3的1条记录加锁。

select * from tb1001 where c1=3 for update;

产生2条行锁,无间隙锁
在idx_c1上对c1=3的1条记录加锁。
在主键上对id=3的1条记录加锁。

lock in share mode方式和for update方式都会对二级索引+主键索引上对满足条件的索引记录加锁，不会对满足条件的索引记录的下一条记录加行锁和间隙锁。

操作对比03

对于不需要回表查询的范围查询，如

select c1 from tb1001 where c1<10 lock in share mode;

产生4条行锁,无间隙锁
在idx_c1上对c1=[1,2,3,10]的3条记录加锁。

当不需要回表查找时，lock in share mode方式在二级索引上对满足条件的索引记录加锁，会对满足条件的索引记录的下一条记录加行锁但不会加间隙锁。

select c1 from tb1001 where c1<10 for update;

产生6条行锁,无间隙锁
在idx_c1上对c1=[1,2,3]的3条记录加锁。
在主键上对id=[1,2,3]的3条记录加锁。

当不需要回表查找时，for update方式在二级索引+主键索引上对满足条件的索引记录加锁，不会对满足条件的索引记录的下一条记录加行锁和间隙锁。

操作对比04

对于不需要回表查询的范围查询，如

select * from tb1001 where c1<10 lock in share mode;

产生7条行锁,无间隙锁
在idx_c1上对c1=[1,2,3,10]的3条记录加锁。
在主键上对id=[1,2,3]的3条记录加锁

lock in share mode方式会对二级索引+主键索引上对满足条件的索引记录加锁，并对满足条件的索引记录的下一条记录在二级索引上加行锁，但不会对"满足条件的索引记录的下一条记录"在主键索引上加行锁，也不会加任何间隙锁。

select * from tb1001 where c1<10 for update;

产生6条行锁,无间隙锁
在idx_c1上对c1=[1,2,3]的3条记录加锁。
在主键上对id=[1,2,3]的3条记录加锁

for update方式都会对二级索引+主键索引上对满足条件的索引记录加锁，不会对满足条件的索引记录的下一条记录加行锁和间隙锁。