MySQL InnoDB锁实战解读

原创

byamao1 2023-02-02 21:50:06 ©著作权

文章标签 mysql MySQL 主键 文章分类 运维

©著作权归作者所有：来自51CTO博客作者byamao1的原创作品，请联系作者获取转载授权，否则将追究法律责任

环境准备

MySQL版本：8.0.12

mysql> select version();

以下实验中，所有的隔离等级都是：REPEATABLE READ
1 开启Lock Monitor
MySQL5.6.16后的推荐方法：

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

关闭服务器下次重启后该变量恢复OFF默认值
需要注意的是：

通过该方式开启Lock Monitor必须首先set GLOBAL innodb_status_output=ON;
而要关闭Lock Monitor仅set GLOBAL innodb_status_output_locks=OFF即可，要是set GLOBAL innodb_status_output=OFF 也会关闭Standard监控。

以上方法是将监控结果输出到数据目录的MySQL错误日志中，每隔15秒产生一次输出。

若是通过SHOW ENGINE INNODB STATUS 仅在必要时输出Standard监控结果到交互式mysql客户端，且还要显示Lock Monitor的话，只需开启innodb_status_output_locks参数即可，innodb_status_output开不开无所谓。

InnoDB Monitor
2 创建测试表

-- ----------------------------
-- Table structure for tb2
-- ----------------------------
DROP TABLE IF EXISTS `tb2`;
CREATE TABLE `tb2`  (
  `id` int(11) NOT NULL,
  `c` int(11) NULL DEFAULT NULL,
  `u` int(11) NULL DEFAULT NULL,
  `n` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_u_unique`(`u`) USING BTREE,
  INDEX `idx_n_normal`(`n`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb2
-- ----------------------------
INSERT INTO `tb2` VALUES (10, 11, 12, 13);
INSERT INTO `tb2` VALUES (20, 21, 22, 23);
INSERT INTO `tb2` VALUES (30, 31, 32, 33);

参考

MySQL InnoDB锁实战解读_MySQL

MySQL InnoDB锁实战解读_MySQL_02

MySQL InnoDB锁机制全面解析分享

测试

对普通索引的等值查询加锁

begin;
select * from tb2 where n=23 for update;
+—-+—-+—-+—-+
| id | c | u | n |
+—-+—-+—-+—-+
| 20 | 21 | 22 | 23 |
+—-+—-+—-+—-+
1 row in set (0.14 sec)

查看加锁情况

—TRANSACTION 2085, ACTIVE 16 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 15, OS thread handle 70800, query id 134 localhost 127.0.0.1 root
TABLE LOCK table test.tb2 trx id 2085 lock mode IX
RECORD LOCKS space id 2 page no 6 n bits 72 index idx_n_normal of table test.tb2 trx id 2085 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000017; asc ;; (13, 23]
1: len 4; hex 80000014; asc ;;

RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table test.tb2 trx id 2085 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;; 主键索引id=20
1: len 6; hex 000000000818; asc ;;
2: len 7; hex 81000001080110; asc ;;
3: len 4; hex 80000015; asc ;;
4: len 4; hex 80000016; asc ;;
5: len 4; hex 80000017; asc ;;

RECORD LOCKS space id 2 page no 6 n bits 72 index idx_n_normal of table test.tb2 trx id 2085 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000021; asc !;; (23, 33]
1: len 4; hex 8000001e; asc ;;

对普通索引的范围加锁

begin;
select * from tb2 where n>=23 for update;
+—-+—-+—-+—-+
| id | c | u | n |
+—-+—-+—-+—-+
| 20 | 21 | 22 | 23 |
| 30 | 31 | 32 | 33 |
+—-+—-+—-+—-+
2 rows in set (0.04 sec)

查看加锁情况

—TRANSACTION 1749, ACTIVE 3098 sec
3 lock struct(s), heap size 1136, 5 row lock(s) 3把锁
MySQL thread id 30, OS thread handle 3312, query id 674 localhost ::1 root

TABLE LOCK table test.tb2 trx id 1749 lock mode IX 表锁

RECORD LOCKS space id 3 page no 6 n bits 80 index idx_n_normal of table test.tb2 trx id 1749 lock_mode X 普通索引锁
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;; (33, +∞)
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000017; asc ;; (13, 23]
1: len 4; hex 80000014; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000021; asc !;; (23, 33]
1: len 4; hex 8000001e; asc ;;

RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table test.tb2 trx id 1749 lock_mode X locks rec but not gap 主索引的锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;; 主索引id=20的锁
1: len 6; hex 0000000006b0; asc ;;
2: len 7; hex 02000001250110; asc % ;;
3: len 4; hex 80000015; asc ;;
4: len 4; hex 80000016; asc ;;
5: len 4; hex 80000017; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000001e; asc ;; 主索引id=30的锁
1: len 6; hex 0000000006b2; asc ;;
2: len 7; hex 010000012b0110; asc + ;;
3: len 4; hex 8000001f; asc ;;
4: len 4; hex 80000020; asc ;;
5: len 4; hex 80000021; asc !;;

更新普通索引
这里直接给结论。Example：

begin;
update tb2 set c=0 where n=23;

加锁：

普通索引：(13,23] (23, 33] X
主键索引：20 X

可以看出，更新操作在MySQL源码实际上是执行两步：

select … for update;
update op

lock in share mode 和 for share 加的锁
测试发现两者加的锁是一样的。这里以 for share 举例。

注意：单独用for share无法查看加的锁，需要再用for update。

begin;
select * from tb2 where id=10 for share;
select * from tb2 where id=10 for update;

锁情况：

—TRANSACTION 2082, ACTIVE 39 sec
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 15, OS thread handle 70800, query id 117 localhost 127.0.0.1 root
TABLE LOCK table test.tb2 trx id 2082 lock mode IS 表锁是IS
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table test.tb2 trx id 2082 lock mode S locks rec but not gap 记录锁是S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;; 主键索引id=10
1: len 6; hex 000000000817; asc ;;
2: len 7; hex 82000001080110; asc ;;
3: len 4; hex 8000000b; asc ;;
4: len 4; hex 8000000c; asc ;;
5: len 4; hex 8000000d; asc ;;

这个是for update的锁

TABLE LOCK table test.tb2 trx id 2082 lock mode IX
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table test.tb2 trx id 2082 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000000817; asc ;;
2: len 7; hex 82000001080110; asc ;;
3: len 4; hex 8000000b; asc ;;
4: len 4; hex 8000000c; asc ;;
5: len 4; hex 8000000d; asc ;;

插入意向锁
对普通索引加排他锁。
事务1

mysql> begin;
mysql> select * from tb2 where n=23 for update;
+—-+—-+—-+—-+
| id | c | u | n |
+—-+—-+—-+—-+
| 20 | 10 | 22 | 23 |
+—-+—-+—-+—-+

在事务2中进行插入，例如：insert into tb2 values(1,0,1,34);。
经过测试发现，普通索引(13, 33]区间内无法插入：

insert 14 ，require X Gap Lock (13,23]
insert 23 ，require X Gap Lock (13,23]
insert 24 ，require X Gap Lock (23,33]
insert 33 ，require X Gap Lock (23,33]

总结下：只要插入意向锁加的X Gap Lock已经被加了X Gap Lock，就会失败。

快照读

不显式加『lock in share mode』与『for update』的『select』操作都属于快照读。

在一个事务中，进行快照读读取的版本不是事务执行begin;时的系统版本，而是第一次快照读语句执行时的系统版本（后面任何快照读的版本判断都是基于第一次快照读语句执行时的系统版本）：

begin; #不是在这个时间点的版本
select * from tb2 where n=23; #是在这个时间点的版本
事务2对n=23进行了更新，并提交
select * from tb2 where n=23; #不管其他事务是否提交，再执行该语句仍然是那个时间点的版本

那么换个方式再检测：

begin;
事务2对n=23进行了更新，并提交
select * from tb2 where n=23; #得到的是事务2的更新结果

但是同时要注意的是：

在快照读中是允许对其他事务提交的row进行修改/删除操作，并可被本事务查询。具体看官网。

MySQL InnoDB锁机制全面解析分享
四、当前读与快照读

下面开始做实验
(1) 事务1通过普通索引读取，事务2通过普通索引更新
事务1

mysql> begin;
mysql> select * from tb2 where n=23;
+—-+—-+—-+—-+
| id | c | u | n |
+—-+—-+—-+—-+
| 20 | 21 | 22 | 23 |
+—-+—-+—-+—-+

事务2 更新n=23并提交

mysql> begin;
mysql> update tb2 set c=0 where n=23;
mysql> commit;
mysql> select * from tb2 where n=23;
+—-+—+—-+—-+
| id | c | u | n |
+—-+—+—-+—-+
| 20 | 0 | 22 | 23 |
+—-+—+—-+—-+

说明更新成功。
事务1 查询

mysql> select * from tb2 where n=23;
+—-+—-+—-+—-+
| id | c | u | n |
+—-+—-+—-+—-+
| 20 | 21 | 22 | 23 |
+—-+—-+—-+—-+

说明MVCC可以实现可重复读取。

(2) 事务1通过普通索引读取，事务2通过主键索引更新
这里不再贴过程，结果是MVCC仍然可以实现可重复读取。
(3) 事务1通过普通索引读取，事务2通过普通索引插入
这里不再贴过程，结果是MVCC克服了幻读。

疑问
我在这里有点疑问：既然如此，为什么说幻读是被next-key lock克服的？
查看官网15.5.4 Phantom Rows，找到答案：
next-key lock不是针对快照读，而是针对锁读：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

The query scans the index starting from the first record where id is bigger than 100. Let the table contain rows having id values of 90 and 102. If the locks set on the index records in the scanned range do not lock out inserts made in the gaps (in this case, the gap between 90 and 102), another session can insert a new row into the table with an id of 101. If you were to execute the same SELECT within the same transaction, you would see a new row with an id of 101 (a “phantom”) in the result set returned by the query. If we regard a set of rows as a data item, the new phantom child would violate the isolation principle of transactions that a transaction should be able to run so that the data it has read does not change during the transaction.

【20180613】MySQL innodb 引擎如何解决幻读