MySQL加锁规则试验及汇总

加锁规则

1. 加锁的基本单位是临键锁，锁的区间的是左开右闭区间
2. 只对访问到的对象加锁，例如利用到了索引覆盖加锁就只会对该索引加锁，主键不加锁
3. 唯一键等值查询，记录存在临键锁会退化成记录锁，记录不存在临键锁退化成间隙锁
4. 非唯一索引上的查询，在索引上向右遍历且最后一个记录不满足条件时，临键锁退化成间隙锁，记录存在主键上加记录锁，记录不存在临键锁退化成间隙锁
5. 条件都没有索引就锁全表

数据准备

MySQL版本：8.3.0

DDL：

CREATE TABLE `t` (
  `id` int NOT NULL,
  `idx` int DEFAULT NULL,
  `col` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx` (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

DML：

insert into t values 
(0, 100, 1000),
(5, 105, 1005),
(10, 110, 1010),
(15, 115, 1015),
(20, 120, 1020);

普通索引

普通索引等值查询记录存在

DQL：select * from t where idx = 105 for update;
以下表的加锁数据用select index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;查询得到，下面不再赘述

NULL,'TABLE','IX',NULL
'idx','RECORD','X','105, 5'                     临键锁idx(100, 105]
'PRIMARY','RECORD','X,REC_NOT_GAP','5'          记录锁id=5
'idx','RECORD','X,GAP','110, 10'                间隙锁(105, 110)

普通索引等值查询记录不存在

select * from t where idx = 107 for update;

NULL,'TABLE','IX',NULL
'idx','RECORD','X,GAP','110, 10'                间隙锁idx(105, 110)

普通索引范围查询大于等于且记录存在

select * from t where idx >= 115 for update;

NULL,'TABLE','IX',NULL
'idx','RECORD','X','supremum pseudo-record'     临键锁idx(120, 正无穷]
'idx','RECORD','X','115, 15'                    临键锁idx(110, 115]
'idx','RECORD','X','120, 20'                    临键锁idx(115, 120]
'PRIMARY','RECORD','X,REC_NOT_GAP','15'         记录锁id=15
'PRIMARY','RECORD','X,REC_NOT_GAP','20'         记录锁id=20

普通索引范围查询大于

select * from t where idx > 115 for update;

NULL,'TABLE','IX',NULL
'idx','RECORD','X','supremum pseudo-record'     临键锁idx(120, 正无穷]
'idx','RECORD','X','120, 20'                    临键锁idx(115, 120]
'PRIMARY','RECORD','X,REC_NOT_GAP','20'         记录锁id=20

普通索引范围查询小于等于且记录存在

select * from t where idx <= 105 for update;

NULL,'TABLE','IX',NULL
'idx','RECORD','X','100, 0'                     临键锁idx(负无穷, 100]
'idx','RECORD','X','105, 5'                     临键锁idx(100, 105]
'idx','RECORD','X','110, 10'                    临键锁idx(105, 110]
'PRIMARY','RECORD','X,REC_NOT_GAP','0'          记录锁id=0
'PRIMARY','RECORD','X,REC_NOT_GAP','5'          记录锁id=5

普通索引范围查询小于等于且记录不存在

select * from t where idx <= 107 for update;

NULL,'TABLE','IX',NULL
'idx','RECORD','X','100, 0'                     临键锁idx(负无穷, 100]
'idx','RECORD','X','105, 5'                     临键锁idx(100, 105]
'idx','RECORD','X','110, 10'                    临键锁idx(105, 110]
'PRIMARY','RECORD','X,REC_NOT_GAP','0'          记录锁id=0
'PRIMARY','RECORD','X,REC_NOT_GAP','5'          记录锁id=5

普通索引范围查询小于

select * from t where idx < 105 for update;

NULL,'TABLE','IX',NULL
'idx','RECORD','X','100, 0'                     临键锁idx(负无穷, 100]
'idx','RECORD','X','105, 5'                     临键锁idx(100, 105]
'PRIMARY','RECORD','X,REC_NOT_GAP','0'          记录锁id=0

唯一索引

唯一索引等值查询记录存在

select * from t where id = 5 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X,REC_NOT_GAP','5'          记录锁id=5

唯一索引等值查询记录不存在

select * from t where id = 7 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X,GAP','10'                 间隙锁id(5, 10]

唯一索引范围查询大于等于且记录存在

select * from t where id >= 10 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X,REC_NOT_GAP','10'         记录锁id=10
'PRIMARY','RECORD','X','supremum pseudo-record' 临键锁id(20, 正无穷]
'PRIMARY','RECORD','X','15'                     临键锁id(10, 15]
'PRIMARY','RECORD','X','20'                     临键锁id(15, 20]

唯一索引范围查询大于

select * from t where id > 10 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X','supremum pseudo-record' 临键锁id(20, 正无穷]
'PRIMARY','RECORD','X','15'                     临键锁id(10, 15]
'PRIMARY','RECORD','X','20'                     临键锁id(15, 20]

唯一索引范围查询小于等于且记录存在

select * from t where id <= 10 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X','0'                      临键锁id(负无穷, 0]
'PRIMARY','RECORD','X','5'                      临键锁id(0, 5]
'PRIMARY','RECORD','X','10'                     临键锁id(5, 10]
NULL,'TABLE','IX',NULL

唯一索引范围查询小于等于且记录不存在

select * from t where id <= 12 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X','0'                      临键锁id(负无穷, 0]
'PRIMARY','RECORD','X','5'                      临键锁id(0, 5]
'PRIMARY','RECORD','X','10'                     临键锁id(5, 10]
'PRIMARY','RECORD','X,GAP','15'                 间隙锁id(10, 15)

唯一索引范围查询小于

select * from t where id < 10 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X','0'                      临键锁id(负无穷, 0]
'PRIMARY','RECORD','X','5'                      临键锁id(0, 5]
'PRIMARY','RECORD','X,GAP','10'                 间隙锁id(5, 10)

无索引

等值查询存在

select * from t where col = 1010 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X','supremum pseudo-record'
'PRIMARY','RECORD','X','0'
'PRIMARY','RECORD','X','5'
'PRIMARY','RECORD','X','15'
'PRIMARY','RECORD','X','10'
'PRIMARY','RECORD','X','20'
NULL,'TABLE','IX',NULL

所有记录加临键锁，锁全表

等值查询不存在

select * from t where col = 10 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X','supremum pseudo-record'
'PRIMARY','RECORD','X','0'
'PRIMARY','RECORD','X','5'
'PRIMARY','RECORD','X','15'
'PRIMARY','RECORD','X','10'
'PRIMARY','RECORD','X','20'

所有记录加临键锁，锁全表

范围查询大于等于

select * from t where col >= 1010 for update;

NULL,'TABLE','IX',NULL
'PRIMARY','RECORD','X','supremum pseudo-record'
'PRIMARY','RECORD','X','0'
'PRIMARY','RECORD','X','5'
'PRIMARY','RECORD','X','15'
'PRIMARY','RECORD','X','10'
'PRIMARY','RECORD','X','20'

所有记录加临键锁，锁全表