1、熟悉InnoDB的3种行级锁

除了表级锁之外，InnoDB还支持行级锁，行级锁能够有效地减少锁冲突。

按照锁定范围的不同，

MySQL支持3种行级锁：记录锁、间隙锁、临建锁

https://blog.csdn.net/weixin_38597669/article/details/89405337?spm=1001.2101.3001.6650.4&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-4.no_search_link&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-4.no_search_link

2、记录锁

记录锁是通过主键或唯一索引对某行记录进行加锁，它锁住该行的索引，有时也称之为行锁。

3、间隙锁

间隙锁（Gap Locks）锁住的是一个范围区间，而不仅仅是这个表的记录

4、临键锁

临键锁（ Next-key Locks ）是记录锁和间隙锁的组合，除了锁定记录本身之外，还会锁定左右两边相邻的区间范围。

5、测试数据

CREATE TABLE sql22_lock ( 
    id INT UNSIGNED auto_increment NOT NULL, 
    num INT, 
    PRIMARY KEY ( id ), 
    KEY ( num ) 
    ) ENGINE = INNODB CHARACTER SET utf8;
    INSERT INTO sql22_lock(id,num) VALUES(1,1);
    INSERT INTO sql22_lock(id,num) VALUES(2,3);
    INSERT INTO sql22_lock(id,num) VALUES(3,5);
    INSERT INTO sql22_lock(id,num) VALUES(5,7);
    INSERT INTO sql22_lock(id,num) VALUES(7,7);
    INSERT INTO sql22_lock(id,num) VALUES(8,9);
    INSERT INTO sql22_lock(id,num) VALUES(10,11);
  INSERT INTO sql22_lock(id,num) VALUES(15,12);

6、演示

① 记录锁

# 事务A 
begin;

select * from sql22_lock where id = 5 for update;

commit;

# 事务B

begin;

select * from sql22_lock where id = 5 lock in share mode;

commit;

② 间隙锁

# 事务A 
select * from sql22_lock;

begin;

select * from sql22_lock where id > 5 and id < 10 lock in share mode;

commit;

# 事务B
select * from sql22_lock;

begin;

insert into sql22_lock(id,num) values(6,66);
insert into sql22_lock(id,num) values(9,99);

update sql22_lock set num = 77 where id = 7;
update sql22_lock set num = 88 where id = 8;

commit;

③ 临键锁

# 事务A 
select * from sql22_lock;

begin;

select * from sql22_lock where num = 9 for update;

commit;

# 事务B
select * from sql22_lock;

begin;
insert into sql22_lock(id,num) values(16,6);
insert into sql22_lock(id,num) values(16,7);
insert into sql22_lock(id,num) values(16,8);
insert into sql22_lock(id,num) values(16,9);
insert into sql22_lock(id,num) values(16,10);
insert into sql22_lock(id,num) values(16,11);
insert into sql22_lock(id,num) values(12,12);
commit;