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

Mysql锁实战

news 2026/2/7 18:21:06

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

大型活动交通拥堵治理的视觉算法应用

大型活动下智慧交通的视觉分析应用一、背景与挑战大型活动（如演唱会、马拉松赛事、高考中考等）期间，城市交通面临瞬时人流车流激增、传统摄像头模糊、交通拥堵识别滞后等问题。以演唱会为例，暖城商圈曾因观众集中离场导致周边…...

编程新知 2026/1/23 7:15:40

《从零掌握MIPI CSI-2: 协议精解与FPGA摄像头开发实战》-- CSI-2 协议详细解析 (一）

CSI-2 协议详细解析 (一） 1. CSI-2层定义（CSI-2 Layer Definitions） 分层结构 ：CSI-2协议分为6层： 物理层（PHY Layer） ： 定义电气特性、时钟机制和传输介质（导线&#…...

编程新知 2026/1/23 12:18:20

DAY 47

三、通道注意力 3.1 通道注意力的定义 # 新增：通道注意力模块（SE模块） class ChannelAttention(nn.Module):"""通道注意力模块(Squeeze-and-Excitation)"""def __init__(self, in_channels, reduction_rat…...

编程新知 2026/2/4 23:29:22

Keil 中设置 STM32 Flash 和 RAM 地址详解

文章目录 Keil 中设置 STM32 Flash 和 RAM 地址详解一、Flash 和 RAM 配置界面（Target 选项卡）1. IROM1（用于配置 Flash）2. IRAM1（用于配置 RAM）二、链接器设置界面（Linker 选项卡）1. 勾选“Use Memory Layout from Target Dialog”2. 查看链接器参数（如果没有勾选上面…...

编程新知 2026/2/7 0:28:16

现代密码学 | 椭圆曲线密码学—附py代码

Elliptic Curve Cryptography 椭圆曲线密码学（ECC）是一种基于有限域上椭圆曲线数学特性的公钥加密技术。其核心原理涉及椭圆曲线的代数性质、离散对数问题以及有限域上的运算。椭圆曲线密码学是多种数字签名算法的基础，例如椭圆曲线数字签…...

编程新知 2026/2/6 7:30:33

优选算法第十二讲：队列 + 宽搜优先级队列

优选算法第十二讲：队列宽搜 && 优先级队列 1.N叉树的层序遍历2.二叉树的锯齿型层序遍历3.二叉树最大宽度4.在每个树行中找最大值5.优先级队列 -- 最后一块石头的重量6.数据流中的第K大元素7.前K个高频单词8.数据流的中位数 1.N叉树的层序遍历 2.二叉树的锯…...

编程新知 2026/1/20 17:18:59

分布式增量爬虫实现方案

之前我们在讨论的是分布式爬虫如何实现增量爬取。增量爬虫的目标是只爬取新产生或发生变化的页面，避免重复抓取，以节省资源和时间。在分布式环境下，增量爬虫的实现需要考虑多个爬虫节点之间的协调和去重。另一种思路：将增量判…...

编程新知 2026/2/6 1:18:56

企业如何增强终端安全？

在数字化转型加速的今天，企业的业务运行越来越依赖于终端设备。从员工的笔记本电脑、智能手机，到工厂里的物联网设备、智能传感器，这些终端构成了企业与外部世界连接的 “神经末梢”。然而，随着远程办公的常态化和设备接入的爆炸式…...

编程新知 2026/1/29 9:14:58

基于IDIG-GAN的小样本电机轴承故障诊断

目录 🔍 核心问题一、IDIG-GAN模型原理 1. 整体架构 2. 核心创新点 (1) 梯度归一化（Gradient Normalization） (2) 判别器梯度间隙正则化（Discriminator Gradient Gap Regularization） (3) 自注意力机制（Self-Attention） 3. 完整损失函数二…...

编程新知 2026/1/31 10:58:46

Linux nano命令的基本使用

参考资料 GNU nanoを使いこなすnano基础目录一. 简介二. 文件打开2.1 普通方式打开文件2.2 只读方式打开文件三. 文件查看3.1 打开文件时，显示行号3.2 翻页查看四. 文件编辑4.1 Ctrl K 复制和 Ctrl U 粘贴4.2 Alt/Esc U 撤回五. 文件保存与退出5.1 Ctrl …...

编程新知 2026/1/31 2:17:41

准备工作

实战

insert-存在唯一索引

insert-不存在唯一索引

update-根据唯一索引

update-根据非唯一索引

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

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

场景3: 区间更新between and

场景4: 区间更新大于

场景5: 区间更新小于

幻读

场景1

场景2

总结

相关文章：