当前位置：首页 > news >正文

Mysql锁实战

news 2025/11/15 22:03:45

mysql版本：8.0.32
通过实战验证mysql的Record lock 与 Gap lock原理

准备工作

设置隔离级别为：RR，以及innodb状态输出锁相关信息

show variables like '%innodb_status_output_locks%';
show variables like '%isolation%';set global innodb_status_output_locks=ON;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

建立测试表

CREATE TABLE `my_test_user` (`id` int unsigned NOT NULL AUTO_INCREMENT,`user_name` varchar(45) DEFAULT NULL,`user_age` int DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_user_age` (`user_age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

关闭自动提交

set autocommit=OFF;

实战

数据库数据分布

# user_age	count(0)
12	1
13	1
14	1
15	1
16	1
17	1
18	1
19	1
40	1
41	1

insert-存在唯一索引

session 1：

INSERT INTO `gallant`.`my_test_user`
(`id`,
`user_name`,
`user_age`)
values
(1,'jack1','12'),
(2,'jack2','13'),
(3,'jack3','14'),
(4,'jack4','15'),
(5,'jack5','16'),
(6,'jack6','17'),
(7,'jack7','18'),
(8,'jack8','19'),
(9,'jack9','40'),
(10,'jack10','41')
;

查看锁信息

show engine innodb status\G

没有写入冲突，加：意向排他锁：IX

---TRANSACTION 18795, ACTIVE 3 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 6253998080, query id 229 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18795 lock mode IX
---TRANSACTION 18794, ACTIVE 11 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 6252883968, query id 228 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18794 lock mode IX

开启第2个session，同时写入一个id数据，IX锁额外增加排他锁X，行锁
session 2：

INSERT INTO `gallant`.`my_test_user`
(`id`,
`user_name`,
`user_age`)
values
(1,'jack1','12');

锁日志

---TRANSACTION 18793, ACTIVE 31 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 6253998080, query id 221 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18793 lock mode IX
RECORD LOCKS space id 15 page no 477 n bits 128 index PRIMARY of table `gallant`.`my_test_user` trx id 18793 lock_mode X locks rec but not gap
Record lock, heap no 55 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00164233; asc   B3;;1: len 6; hex 000000004969; asc     Ii;;2: len 7; hex 820000009d0110; asc        ;;3: len 5; hex 616c696365; asc alice;;4: len 4; hex 80000013; asc     ;;---TRANSACTION 18791, ACTIVE 300 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 6252883968, query id 224 localhost root update
INSERT INTO `my_test_user` (`id`,`user_name`,`user_age`) VALUES (1458739,'alice','19')
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 477 n bits 128 index PRIMARY of table `gallant`.`my_test_user` trx id 18791 lock mode S locks rec but not gap waiting
Record lock, heap no 55 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00164233; asc   B3;;1: len 6; hex 000000004969; asc     Ii;;2: len 7; hex 820000009d0110; asc        ;;3: len 5; hex 616c696365; asc alice;;4: len 4; hex 80000013; asc     ;;------------------
TABLE LOCK table `gallant`.`my_test_user` trx id 18791 lock mode IX
RECORD LOCKS space id 15 page no 6 n bits 528 index PRIMARY of table `gallant`.`my_test_user` trx id 18791 lock_mode X locks rec but not gap
Record lock, heap no 229 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 0006fff3; asc     ;;1: len 6; hex 000000004967; asc     Ig;;2: len 7; hex 820000009c0110; asc        ;;3: len 5; hex 616c696365; asc alice;;4: len 4; hex 80000013; asc     ;;RECORD LOCKS space id 15 page no 477 n bits 128 index PRIMARY of table `gallant`.`my_test_user` trx id 18791 lock mode S locks rec but not gap waiting
Record lock, heap no 55 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00164233; asc   B3;;1: len 6; hex 000000004969; asc     Ii;;2: len 7; hex 820000009d0110; asc        ;;3: len 5; hex 616c696365; asc alice;;4: len 4; hex 80000013; asc     ;;

insert-不存在唯一索引

加：意向排他锁，无锁冲突，可以并行写入
session 1:

INSERT INTO `my_test_user`
(`user_name`,`user_age`)
VALUES
('alice','40');

session 2:

INSERT INTO `my_test_user`
(`user_name`,`user_age`)
VALUES
('alice_1','40');

锁日志

---TRANSACTION 18797, ACTIVE 3 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 6253998080, query id 235 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18797 lock mode IX
---TRANSACTION 18796, ACTIVE 11 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 6252883968, query id 234 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18796 lock mode IX

update-根据唯一索引

结论是：record lock
session：

update `my_test_user` set user_age=41 where id = 1867767;

锁日志

---TRANSACTION 18798, ACTIVE 3 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 9, OS thread handle 6252883968, query id 241 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18798 lock mode IX
RECORD LOCKS space id 15 page no 477 n bits 128 index PRIMARY of table `gallant`.`my_test_user` trx id 18798 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;

update-根据非唯一索引

场景1：等值更新存在的数据

结论：next-key-lock

session1更新user_age=40，行锁：user_age=19以及40，gap锁，user_age=19-40区间：(19,40)
session2更新user_age=19，行锁：user_age=18以及19，gap锁，user_age=18-19区间：(18,19)
session2锁超时，gap锁超时

session1:

update `my_test_user` set user_age=41 where user_age=40;

session2:
更新user_age=19锁超时

update `my_test_user` set user_age=41 where user_age=19;

锁日志：

---TRANSACTION 18906, ACTIVE 4 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 6256226304, query id 466 localhost root updating
update `my_test_user` set user_age=41 where user_age=19
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18906 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 00000009; asc     ;;------------------
TABLE LOCK table `gallant`.`my_test_user` trx id 18906 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18906 lock_mode X
Record lock, heap no 9 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000013; asc     ;;1: len 4; hex 00000008; asc     ;;RECORD LOCKS space id 17 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 18906 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000008; asc     ;;1: len 6; hex 0000000049da; asc     I ;;2: len 7; hex 02000002090151; asc       Q;;3: len 5; hex 6a61636b38; asc jack8;;4: len 4; hex 80000029; asc    );;RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18906 lock_mode X locks gap before rec
Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000028; asc    (;;1: len 4; hex 00000009; asc     ;;RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18906 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 00000009; asc     ;;---TRANSACTION 18905, ACTIVE 80 sec
4 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 6257340416, query id 464 localhost root
Trx read view will not see trx with id >= 18905, sees < 18905
TABLE LOCK table `gallant`.`my_test_user` trx id 18905 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18905 lock_mode X
Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000028; asc    (;;1: len 4; hex 00000009; asc     ;;RECORD LOCKS space id 17 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 18905 lock_mode X locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000009; asc     ;;1: len 6; hex 0000000049d9; asc     I ;;2: len 7; hex 01000001ce0151; asc       Q;;3: len 5; hex 6a61636b39; asc jack9;;4: len 4; hex 80000029; asc    );;RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18905 lock_mode X locks gap before rec
Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 0000000a; asc     ;;Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 00000009; asc     ;;

注：先更新user_age=19的数据也一样

场景2：等值更新不存在的数据与更新存在的数据并发冲突

结论

session1：更新user_age=42，行锁：user_age=42
session2：更新user_age=40（或者user_age=19也一样），行锁：user_age=19以及40，gap锁，user_age=19-40区间：(19,40)
session2锁超时，gap锁成功，行锁冲突

session1:

update `my_test_user` set user_age=43 where user_age=42;

session2:

update `my_test_user` set user_age=44 where user_age=40;

锁日志

---TRANSACTION 18910, ACTIVE 3 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 6256226304, query id 471 localhost root updating
update `my_test_user` set user_age=44 where user_age=40
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18910 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;------------------
TABLE LOCK table `gallant`.`my_test_user` trx id 18910 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18910 lock_mode X
Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000028; asc    (;;1: len 4; hex 00000009; asc     ;;RECORD LOCKS space id 17 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 18910 lock_mode X locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000009; asc     ;;1: len 6; hex 0000000049de; asc     I ;;2: len 7; hex 020000020a0151; asc       Q;;3: len 5; hex 6a61636b39; asc jack9;;4: len 4; hex 8000002c; asc    ,;;RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18910 lock_mode X locks gap before rec
Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 0000000a; asc     ;;RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18910 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;---TRANSACTION 18909, ACTIVE 7 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 23, OS thread handle 6257340416, query id 470 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18909 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18909 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;

场景3: 区间更新between and

结论：next-key-lock

session1更新user_age >= 19 and user_age <= 40，行锁：user_age=19至40所有行以及要set更新的值user_age=41，共3条记录，6把record锁，gap锁：(19,40)+19,40 record 锁，有两把锁与record锁重复，因此是8把锁。执行session2之后会导致session1事务再次重复增加一条行锁仅锁定了索引，未锁定对应的主键对应的行数据（因为之前已经存在该锁）user_age=19。因此共8+1，9把锁。对比锁日志也可以看到，主键加锁不回重复，而普通索引加锁会出现重复加锁，类似于读写锁，主键加锁是为了更新写入数据，普通索引加锁是为了读取（检验是否存在写冲突）
session2更新user_age=41锁超时，行锁超时
session3更新user_age=18成功
session4更新user_age=19锁超时

注意：

仅session1如果改为更新非边界值，例如：
update `my_test_user` set user_age=13 where user_age between 19 and 40;
mysql仅加了record锁，3条记录，6把record锁

session1:

update `my_test_user` set user_age=41 where user_age between 19 and 40;

session2:

update `my_test_user` set user_age=44 where user_age = 41;

update `my_test_user` set user_age=44 where user_age = 18;

update `my_test_user` set user_age=44 where user_age = 19;

锁日志（更新user_age=41锁超时）

---TRANSACTION 18917, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 24, OS thread handle 6256226304, query id 476 localhost root Searching rows for update
update `my_test_user` set user_age=44 where user_age = 41
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18917 lock_mode X waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 00000008; asc     ;;------------------
TABLE LOCK table `gallant`.`my_test_user` trx id 18917 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18917 lock_mode X waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 00000008; asc     ;;---TRANSACTION 18912, ACTIVE 11 sec
5 lock struct(s), heap size 1128, 9 row lock(s), undo log entries 2
MySQL thread id 23, OS thread handle 6257340416, query id 475 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18912 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18912 lock_mode X
Record lock, heap no 9 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000013; asc     ;;1: len 4; hex 00000008; asc     ;;Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000028; asc    (;;1: len 4; hex 00000009; asc     ;;Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 0000000a; asc     ;;RECORD LOCKS space id 17 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 18912 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000008; asc     ;;1: len 6; hex 0000000049e0; asc     I ;;2: len 7; hex 01000001cf0151; asc       Q;;3: len 5; hex 6a61636b38; asc jack8;;4: len 4; hex 80000029; asc    );;Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000009; asc     ;;1: len 6; hex 0000000049e0; asc     I ;;2: len 7; hex 01000001cf0182; asc        ;;3: len 5; hex 6a61636b39; asc jack9;;4: len 4; hex 80000029; asc    );;Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 0000000a; asc     ;;1: len 6; hex 0000000049d0; asc     I ;;2: len 7; hex 82000000bf0185; asc        ;;3: len 6; hex 6a61636b3130; asc jack10;;4: len 4; hex 80000029; asc    );;RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18912 lock_mode X locks gap before rec
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 00000008; asc     ;;Record lock, heap no 13 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 00000009; asc     ;;RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18912 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 00000008; asc     ;;

场景4: 区间更新大于

结论

更新user_age > 19，行锁：user_age>19所有行以及user_age=41与12，共3条记录，6把record锁，gap锁：(19,+∞)
更新user_age=19成功
更新user_age=42成功
插入user_age=42锁超时

session1:

update `my_test_user` set user_age=41 where user_age > 19;

session2:

update `my_test_user` set user_age=12 where user_age = 19;

update `my_test_user` set user_age=12 where user_age = 42;

INSERT INTO `gallant`.`my_test_user`
(`user_name`,
`user_age`)
values
('jack1','42');

锁日志（插入user_age=42锁超时）

---TRANSACTION 18965, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 6256226304, query id 540 localhost root update
INSERT INTO `gallant`.`my_test_user`
(`user_name`,
`user_age`)
values
('jack1','42')
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18965 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;------------------
TABLE LOCK table `gallant`.`my_test_user` trx id 18965 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18965 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;---TRANSACTION 18964, ACTIVE 13 sec
4 lock struct(s), heap size 1128, 6 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 6257340416, query id 539 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18964 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18964 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000028; asc    (;;1: len 4; hex 00000009; asc     ;;Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 0000000a; asc     ;;RECORD LOCKS space id 17 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 18964 lock_mode X locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000009; asc     ;;1: len 6; hex 000000004a14; asc     J ;;2: len 7; hex 01000001dc0151; asc       Q;;3: len 5; hex 6a61636b39; asc jack9;;4: len 4; hex 80000029; asc    );;Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 0000000a; asc     ;;1: len 6; hex 0000000049d0; asc     I ;;2: len 7; hex 82000000bf0185; asc        ;;3: len 6; hex 6a61636b3130; asc jack10;;4: len 4; hex 80000029; asc    );;RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18964 lock_mode X locks gap before rec
Record lock, heap no 13 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000029; asc    );;1: len 4; hex 00000009; asc     ;;

场景5: 区间更新小于

结论

更新user_age < 13，共一条记录+user_age=41，4把recored锁
更新user_age=13锁超时，为什么会超时？分析锁日志是因为set值都是user_age=41，因此在user_age=41这条record锁获取时发生竞争锁等待
更新user_age=11成功
插入user_age=11锁超时

session1:

update `my_test_user` set user_age=41 where user_age < 13;

session2:

update `my_test_user` set user_age=41 where user_age = 13;

update `my_test_user` set user_age=41 where user_age = 11;

INSERT INTO `gallant`.`my_test_user`
(`user_name`,
`user_age`)
values
('jack1','11');

锁日志（更新user_age=13锁超时）

---TRANSACTION 18972, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 24, OS thread handle 6256226304, query id 545 localhost root Searching rows for update
update `my_test_user` set user_age=41 where user_age = 13
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18972 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 8000000d; asc     ;;1: len 4; hex 00000002; asc     ;;------------------
TABLE LOCK table `gallant`.`my_test_user` trx id 18972 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18972 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 8000000d; asc     ;;1: len 4; hex 00000002; asc     ;;---TRANSACTION 18967, ACTIVE 23 sec
3 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 6257340416, query id 544 localhost root
TABLE LOCK table `gallant`.`my_test_user` trx id 18967 lock mode IX
RECORD LOCKS space id 17 page no 5 n bits 80 index idx_user_age of table `gallant`.`my_test_user` trx id 18967 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 8000000c; asc     ;;1: len 4; hex 00000001; asc     ;;Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 8000000d; asc     ;;1: len 4; hex 00000002; asc     ;;RECORD LOCKS space id 17 page no 4 n bits 80 index PRIMARY of table `gallant`.`my_test_user` trx id 18967 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000001; asc     ;;1: len 6; hex 000000004a17; asc     J ;;2: len 7; hex 01000001dd0151; asc       Q;;3: len 5; hex 6a61636b31; asc jack1;;4: len 4; hex 80000029; asc    );;Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 00000002; asc     ;;1: len 6; hex 0000000049d0; asc     I ;;2: len 7; hex 82000000bf011d; asc        ;;3: len 5; hex 6a61636b32; asc jack2;;4: len 4; hex 8000000d; asc     ;;

幻读

RR隔离级别下会有幻读吗？
会：MVCC快照读，未加锁，其他事务写入/更新新数据，再次使用当前读（for update）。均会出现幻读

场景1

session 1	session 2
select * from my_test_user where user_age>41;（无数据返回）
	INSERT INTO `gallant`.`my_test_user` (`id`, `user_name`, `user_age`) values (14,‘jack10’,‘42’);
	commit（执行成功）
select * from my_test_user where user_age>41 for update;（返回数据）

场景2

session 1	session 2
select * from my_test_user where user_age>41;（无数据返回）
	update my_test_user set user_age=42 where user_age=41;
	commit（执行成功）
select * from my_test_user where user_age>41 for update;（返回数据）

总结

基于唯一索引更新，行锁
基于非唯一索引更新，next-key-lock，行锁+gap锁，锁范围前后均为闭区间，以user_age索引字段为例：
1. between 18 and 40, 锁范围：[18,40]
2. 大于> 19，锁范围：(19,+∞)
3. <13，锁范围：(-∞,13)
4. =40，锁范围：[19,40]，锁的是距离user_age=40最近的一条数据至40的范围
5. =44，数据不存在，加意向排他锁IX，此时如果要并发更新数据库中存在的数据会锁超时
基于唯一索引插入，IX锁，可以并行写入，只有索引冲突时会失败
基于非唯一索引插入，IX锁，可以并行写入，无冲突
MVCC或者next-key-lock均可以解决幻读，但是RR隔离级别下，某些场景可以脱离MVCC或者next-key-lock，因此RR隔离级别下可能会出现幻读
很多博客强调幻读是针对insert场景，其实不然，幻读场景2中就是update产生的幻读，幻读是指在同一个事务相同条件下的两次读的数据结果不一致
普通索引加record锁，每次都需要两把锁，普通索引一把锁（可以看作为读写锁中的读锁），primary key一把锁（可以看作为读写锁中的写锁），普通索引的锁可以重入不排斥，写锁不可以

Mysql锁实战

mysql版本：8.0.32 通过实战验证mysql的Record lock 与 Gap lock原理准备工作设置隔离级别为：RR，以及innodb状态输出锁相关信息 show variables like %innodb_status_output_locks%; show variables like %isolation%;set global innodb_…...

编程日记 2023/8/15 5:03:33

HCIP-OpenStack发放云主机

1、云中的概念在云平台注册了一个账号，这个账号对于云平台来说，就是一个租户或者一个项目。租户/项目（tenant/project），租户就是项目的意思。主机聚合就是主机组的意思。 region（区域）&…...

编程日记 2023/8/15 5:02:32

时序预测 | MATLAB基于扩散因子搜索的GRNN广义回归神经网络时间序列预测（多指标，多图）

时序预测 | MATLAB基于扩散因子搜索的GRNN广义回归神经网络时间序列预测（多指标，多图）目录时序预测 | MATLAB基于扩散因子搜索的GRNN广义回归神经网络时间序列预测（多指标，多图）效果一览基本介绍程序设计学习小结参考资料效果一览...

编程日记 2023/8/15 5:01:30

Vulhub之Apache HTTPD 换行解析漏洞（CVE-2017-15715）

Apache HTTPD是一款HTTP服务器，它可以通过mod_php来运行PHP网页。其2.4.0~2.4.29版本中存在一个解析漏洞，在解析PHP时，1.php\x0A将被按照PHP后缀进行解析，导致绕过一些服务器的安全策略。 1、docker-compose build、docker-compo…...

编程日记 2023/8/15 5:00:29

ARTS 挑战打卡的第7天 --- Ubuntu中的WindTerm如何设置成中文，并且关闭shell中Tab键声音（Tips）

前言 （1）Windterm是一个非常优秀的终端神器。关于他的下载我就不多说了，网上很多。今天我就分享一个国内目前没有找到的这方面的资料——Ubuntu中的WindTerm如何设置成中文，并且关闭shell中Tab键声音。将WindTerm设置成中文 &…...

编程日记 2023/8/15 4:59:28

Oracle之执行计划

1、查看执行计划 EXPLAIN PLAN FOR SELECT * FROM temp_1 a ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 2、执行计划说明 2.1、执行顺序根据缩进来判断，缩进最多的最先执行；（缩进相同时，最上面的最先执行） 2.2…...

编程日记 2023/8/15 4:58:21

【Vue框架】菜单栏权限的使用与显示

前言在【Vue框架】Vue路由配置中的getters.js里，可以看到有一个应用程序的状态（变量）叫 permission_routes，这个就是管理前端菜单栏的状态。具体代码的介绍，都以注释的形式来说明。 1、modules\permission.js 1…...

编程日记 2023/8/15 4:57:19

案例研究｜大福中国通过JumpServer满足等保合规和资产管理双重需求

“大福中国为了满足安全合规要求引入堡垒机产品，在对比了传统型堡垒机后，发现JumpServer使用部署更加灵活，功能特性丰富，能够较好地满足公司在等保合规和资产管理方面的双重需求。” ——大福（中国）有限公…...

编程日记 2023/8/15 4:56:16

大数据课程I4——Kafka的零拷贝技术

文章作者邮箱：yugongshiyesina.cn 地址：广东惠州 ▲ 本章节目的 ⚪ 掌握Kafka的零拷贝技术； ⚪ 了解常规的文件传输过程； 一、常规的网络传输原理表面上一个很简单的网络文件输出的过程，在OS底层&…...

编程日记 2023/8/15 4:55:16

红日ATT＆CK VulnStack靶场（三）

网络拓扑 web阶段 1.扫描DMZ机器端口 2.进行ssh和3306爆破无果后访问web服务 3.已知目标是Joomla，扫描目录 4.有用的目录分别为1.php 5.configuration.php~中泄露了数据库密码 6.administrator为后台登录地址 7.直接连接mysql 8.找到管理员表，密码加密了…...

编程日记 2023/8/15 4:54:14

JavaScript之BOM+window对象+定时器+location,navigator,history对象

一.BOM概述 BOM即浏览器对象模型,它提供了独立于内容而与窗口进行交互的对象 BOM的顶级对象是window 二.window对象的常见事件 1.窗口加载事件window.onload window.onload function(){} 或者 window.addEventListener("onload" , function(){}); window.onlo…...

编程日记 2023/8/15 4:53:13

为MySQL新增一张performance_schema表 | StoneDB 技术分享会 #4

StoneDB开源地址 https://github.com/stoneatom/stonedb 设计：小艾审核：丁奇、李浩编辑：宇亭作者：王若添中国科学技术大学-软件工程-在读硕士、StoneDB 内核研发实习生 performance_schema 简介 MySQL 启动后会自动创建四…...

编程日记 2023/8/15 4:52:07

2023/8/12总结

增加了管理员功能点：（管理标签和分类） 另外加了一个转换成pdf的功能主要是通过wkhtmltopdf实现的，之前看过很多说用adobe的还有其他但是都没成功。然后就是在学习websocket和协同过滤算法实现，还只是初步了解了这些。…...

编程日记 2023/8/15 4:51:06

npm run dev报错解决出现错误前的操作步骤错误日志解决步骤出现错误前的操作步骤初始化Vue项目 $ npm create vue3.6.1创建项目文件夹client Vue.js - The Progressive JavaScript Framework✔ Project name: › client ✔ Add TypeScript? › No ✔ Add JSX Support? …...

编程日记 2023/8/15 4:50:00

如何使用PHP编写爬虫程序

在互联网时代，信息就像一条无休无止的河流，源源不断地涌出来。有时候我们需要从Web上抓取一些数据，以便分析或者做其他用途。这时候，爬虫程序就显得尤为重要。爬虫程序，顾名思义，就是用来自动化地获取Web页…...

编程日记 2023/8/15 4:48:58

分布式 - 服务器Nginx：一小时入门系列之HTTP反向代理

文章目录 1. 正向代理和反向代理2. 配置代理服务3. proxy_pass 命令解析4. 设置代理请求headers 1. 正向代理和反向代理正向代理是客户端通过代理服务器访问互联网资源的方式。在这种情况下，客户端向代理服务器发送请求，代理服务器再向互联网上的服务器…...

编程日记 2023/8/15 4:47:57

Android Fragment (详细版)

经典好文推荐，通过阅读本文，您将收获以下知识点: 一、Fragment 简介二、Fragment的设计原理三、Fragment 生命周期四、Fragment 在Activity中的使用方法五、动态添加Fragment到Activity的方法六、Activity 中获取Fragment 七、Fragment 获取宿主Activity的方法八、两个…...

编程日记 2023/8/15 4:46:52

如何使用Flask-RESTPlus构建强大的API

如何使用Flask-RESTPlus构建强大的API 引言： 在Web开发中，构建API（应用程序接口）是非常常见和重要的。API是一种允许不同应用程序之间交互的方式，它定义了如何请求和响应数据的规范。Flask-RESTPlus是一个基于Flask的…...

编程日记 2023/8/15 4:45:50

UGUI事件系统EventSystem

一. 事件系统概述 Unity的事件系统具有通过鼠标、键盘、游戏控制柄、触摸操作等输入方式，将事件发送给对象的功能。事件系统通过场景中EventSystem对象的组件EventSystem和Standalone Input Module发挥功能。EventSystem对象通常实在创建画布的同时被创建的&#xf…...

编程日记 2023/8/15 4:44:46

redis学习笔记（三）

文章目录 key操作（1）查找键（2）判断键是否存在（3）查看键的的值的数据类型（4）删除键以及键对应的值（5）查看键的有效期（6）设置key的有效期…...

编程日记 2023/8/15 4:43:44

可靠性+灵活性：电力载波技术在楼宇自控中的核心价值

可靠性灵活性：电力载波技术在楼宇自控中的核心价值在智能楼宇的自动化控制中，电力载波技术（PLC）凭借其独特的优势，正成为构建高效、稳定、灵活系统的核心解决方案。它利用现有电力线路传输数据，无需额外布…...

编程新知 2025/9/27 2:43:26

【网络安全产品大调研系列】2. 体验漏洞扫描

前言 2023 年漏洞扫描服务市场规模预计为 3.06（十亿美元）。漏洞扫描服务市场行业预计将从 2024 年的 3.48（十亿美元）增长到 2032 年的 9.54（十亿美元）。预测期内漏洞扫描服务市场 CAGR（增长率&…...

编程新知 2025/11/13 19:15:08

微服务商城-商品微服务

数据表 CREATE TABLE product (id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 商品id,cateid smallint(6) UNSIGNED NOT NULL DEFAULT 0 COMMENT 类别Id,name varchar(100) NOT NULL DEFAULT COMMENT 商品名称,subtitle varchar(200) NOT NULL DEFAULT COMMENT 商…...

编程新知 2025/11/8 7:50:09

让AI看见世界：MCP协议与服务器的工作原理

让AI看见世界：MCP协议与服务器的工作原理 MCP（Model Context Protocol）是一种创新的通信协议，旨在让大型语言模型能够安全、高效地与外部资源进行交互。在AI技术快速发展的今天，MCP正成为连接AI与现实世界的重要桥梁。…...

编程新知 2025/11/8 9:59:27

聊一聊接口测试的意义有哪些？

目录一、隔离性 & 早期测试二、保障系统集成质量三、验证业务逻辑的核心层四、提升测试效率与覆盖度五、系统稳定性的守护者六、驱动团队协作与契约管理七、性能与扩展性的前置评估八、持续交付的核心支撑接口测试的意义可以从四个维度展开，首…...

编程新知 2025/10/14 6:46:13

LeetCode - 199. 二叉树的右视图

题目 199. 二叉树的右视图 - 力扣（LeetCode） 思路右视图是指从树的右侧看，对于每一层，只能看到该层最右边的节点。实现思路是： 使用深度优先搜索(DFS)按照"根-右-左"的顺序遍历树记录每个节点的深度对于…...

编程新知 2025/9/13 20:07:18

Go 并发编程基础：通道（Channel）的使用

在 Go 中，Channel 是 Goroutine 之间通信的核心机制。它提供了一个线程安全的通信方式，用于在多个 Goroutine 之间传递数据，从而实现高效的并发编程。本章将介绍 Channel 的基本概念、用法、缓冲、关闭机制以及 select 的使用。一、Channel…...

编程新知 2025/8/28 22:40:10

根目录0xa0属性对应的Ntfs!_SCB中的FileObject是什么时候被建立的----NTFS源代码分析--重要

根目录0xa0属性对应的Ntfs!_SCB中的FileObject是什么时候被建立的第一部分： 0: kd> g Breakpoint 9 hit Ntfs!ReadIndexBuffer: f7173886 55 push ebp 0: kd> kc # 00 Ntfs!ReadIndexBuffer 01 Ntfs!FindFirstIndexEntry 02 Ntfs!NtfsUpda…...

编程新知 2025/8/27 0:09:05

django blank 与 null的区别

1.blank blank控制表单验证时是否允许字段为空 2.null null控制数据库层面是否为空但是，要注意以下几点： Django的表单验证与null无关：null参数控制的是数据库层面字段是否可以为NULL，而blank参数控制的是Django表单验证时字…...

编程新知 2025/7/7 3:34:13

十九、【用户管理与权限 - 篇一】后端基础：用户列表与角色模型的初步构建

【用户管理与权限 - 篇一】后端基础：用户列表与角色模型的初步构建前言准备工作第一部分：回顾 Django 内置的 `User` 模型第二部分：设计并创建 `Role` 和 `UserProfile` 模型第三部分：创建 Serializers第四部分：创建 ViewSets第五部分：注册 API 路由第六部分：后端初步测…...

编程新知 2025/11/11 18:42:48

准备工作

实战

insert-存在唯一索引

insert-不存在唯一索引

update-根据唯一索引

update-根据非唯一索引

场景1：等值更新存在的数据

场景2：等值更新不存在的数据与更新存在的数据并发冲突

场景3: 区间更新between and

场景4: 区间更新大于

场景5: 区间更新小于

幻读

场景1

场景2

总结

相关文章：