Mysql行锁forUpdate实验以及记录锁、间隙锁，临键锁

背景

在编写一个领取码的时候，业务复杂度不高，所以直接做了个查询的排它锁 for update，同时研究了行锁相关的内容，因为经人提点说，，MySQL行锁只在主键和唯一索引起效，根据个人经验，相信MySQL不会仅仅如此，故进行了这次实验。这里仅研究悲观锁（排它锁）。

准备工具

1.MySQL版本：8.0.16
2.命令行
3.一张表
4.一组范围查询sql,一组主键id查询sql
5.插入1000条数据

使用到的分析命令：

SELECT version(); # 查询MySQL版本

SET autocommit = 0; # 关闭自动提交

SHOW VARIABLES LIKE '%autocommit%'; # 查询自动提交事务开关

SHOW VARIABLES LIKE '%ISOLATION%'; # 查询隔离级别

SHOW OPEN TABLES WHERE In_use > 0; # 查询表是否被使用以及namelock

SHOW PROCESSLIST; # 查询进程

SELECT	* FROM information_schema.INNODB_TRX; # 查看当前运行的所有事务

SET GLOBAL innodb_lock_wait_timeout = 120;# 设置锁等待时间

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

SELECT * FROM sys.`innodb_lock_waits` # 查询已经存在的锁

核心是查询事务的命令，查询结果得到的参数解释如下：

实验过程

表名 cards，具备字段：主键 id,type_id,username,game_id,code

字段都建了索引,并且存在查询联合索引，同时type_id为码的分类

实验1

进程1	进程2
SET autocommit = 0; # 关闭自动提交
begin;
SELECT * FROM cards WHERE type_id = '1' AND username = '' LIMIT 1 FOR UPDATE; 得到结果1条，id为 10
	UPDATE cards SET username='elinx' WHERE id=10; 出现锁等待
	UPDATE cards SET username='elinx' WHERE id=12; 执行成功
commit;
	执行等待的任务，成功

当出现锁问题是立即查看了事务进程状态，出现两个事务进程：
锁等待LOCK WAIT，RUNNING运行中的事务

LOCK WAIT 中trx_rows_locked值为1，RUNNING 中trx_rows_locked值为2

LOCK WAIT 中trx_tables_in_use为1，RUNNING 中trx_tables_in_use为0。

SHOW OPEN TABLES WHERE In_use > 0; # 查询是否被使用

结果存在一条：db, cards，1，0

实验2

进程1	进程2
SET autocommit = 0; # 关闭自动提交
begin;
SELECT * FROM cards WHERE id=41 FOR UPDATE; 得到结果1条，id为 41
	UPDATE cards SET username='elinx' WHERE id=41; 出现锁等待
	UPDATE cards SET username='elinx' WHERE id=43; 执行成功
commit;
	执行等待的任务，成功

当出现锁问题是立即查看了事务进程状态，出现两个事务进程：

锁等待LOCK WAIT，RUNNING运行中的事务

LOCK WAIT 中trx_rows_locked值为1，RUNNING 中trx_rows_locked值为1

LOCK WAIT 中trx_tables_in_use为1，RUNNING 中trx_tables_in_use为0

SHOW OPEN TABLES WHERE In_use > 0; # 查询是否被使用

结果存在一条：db, cards，1，0

实验3

手动直接锁表形式

进程1	进程2
lock tables cards write;
	UPDATE cards SET username='elinx' WHERE id=50; 出现锁等待
	UPDATE cards SET username='elinx' WHERE id=51; 出现锁等待
unlock tables;
	执行等待的任务，成功

结论

前提：在查询条件字段都存在索引的情况下

由实验1可以看出，执行范围查询的语句并不会出现表锁，属于临键锁，一定范围的记录都会被锁定，这是由于还存在innodb的间隙锁机制。

由实验2可以看出，直接使用主键id可以实现，行锁，锁定行数更少，仅指定的一行，不会出现间隙锁，属于记录锁。

实验3仅为对照组，当表锁时，所有记录均无法进行写操作。

结合实验1和实验2，innodb行锁机制都检查了唯一索引，而主键索引也可以视为唯一索引一种，故行锁在主键和唯一索引下最优，不涉及间隙锁和临键锁，同时，结论得出，存在索引条件下，存在指定范围的行锁。

参考文献

1.mysql InnoDB锁等待的查看及分析
2.MySQL for update 详解