背景
在编写一个领取码的时候,业务复杂度不高,所以直接做了个查询的排它锁 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行锁机制都检查了唯一索引,而主键索引也可以视为唯一索引一种,故行锁在主键和唯一索引下最优,不涉及间隙锁和临键锁,同时,结论得出,存在索引条件下,存在指定范围的行锁。