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

Mysql锁实战

news 2025/7/8 6:12:54

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

golang循环变量捕获问题

在 Go 语言中，当在循环中启动协程（goroutine）时，如果在协程闭包中直接引用循环变量，可能会遇到一个常见的陷阱 - 循环变量捕获问题。让我详细解释一下： 问题背景看这个代码片段： fo…...

编程新知 2025/7/5 11:03:11

基于uniapp+WebSocket实现聊天对话、消息监听、消息推送、聊天室等功能，多端兼容

基于 UniApp + WebSocket实现多端兼容的实时通讯系统，涵盖WebSocket连接建立、消息收发机制、多端兼容性配置、消息实时监听等功能，适配微信小程序、H5、Android、iOS等终端目录技术选型分析WebSocket协议优势UniApp跨平台特性WebSocket 基础实现连接管理消息收发连接…...

编程新知 2025/7/7 7:20:30

CMake基础：构建流程详解

目录 1.CMake构建过程的基本流程 2.CMake构建的具体步骤 2.1.创建构建目录 2.2.使用 CMake 生成构建文件 2.3.编译和构建 2.4.清理构建文件 2.5.重新配置和构建 3.跨平台构建示例 4.工具链与交叉编译 5.CMake构建后的项目结构解析 5.1.CMake构建后的目录结构 5.2.构…...

编程新知 2025/7/5 11:46:14

鸿蒙中用HarmonyOS SDK应用服务 HarmonyOS5开发一个医院查看报告小程序

一、开发环境准备工具安装： 下载安装DevEco Studio 4.0（支持HarmonyOS 5）配置HarmonyOS SDK 5.0确保Node.js版本≥14 项目初始化： ohpm init harmony/hospital-report-app 二、核心功能模块实现 1. 报告列表…...

编程新知 2025/6/19 5:45:52

Neo4j 集群管理：原理、技术与最佳实践深度解析

Neo4j 的集群技术是其企业级高可用性、可扩展性和容错能力的核心。通过深入分析官方文档，本文将系统阐述其集群管理的核心原理、关键技术、实用技巧和行业最佳实践。 Neo4j 的 Causal Clustering 架构提供了一个强大而灵活的基石，用于构建高可用、可扩展且一致的图数据库服务…...

编程新知 2025/7/4 0:49:28

C# 求圆面积的程序（Program to find area of a circle）

给定半径r，求圆的面积。圆的面积应精确到小数点后5位。例子： 输入：r 5 输出：78.53982 解释：由于面积 PI * r * r 3.14159265358979323846 * 5 * 5 78.53982，因为我们只保留小数点后 5 位数字。输…...

编程新知 2025/7/6 18:34:01

CSS设置元素的宽度根据其内容自动调整

width: fit-content 是 CSS 中的一个属性值，用于设置元素的宽度根据其内容自动调整，确保宽度刚好容纳内容而不会超出。效果对比默认情况（width: auto）： 块级元素（如 <div>）会占满父容器…...

编程新知 2025/6/20 15:09:15

GO协程(Goroutine)问题总结

在使用Go语言来编写代码时，遇到的一些问题总结一下 [参考文档]：https://www.topgoer.com/%E5%B9%B6%E5%8F%91%E7%BC%96%E7%A8%8B/goroutine.html 1. main()函数默认的Goroutine 场景再现： 今天在看到这个教程的时候，在自己的电…...

编程新知 2025/7/6 20:54:39

uniapp 字符包含的相关方法

在uniapp中，如果你想检查一个字符串是否包含另一个子字符串，你可以使用JavaScript中的includes()方法或者indexOf()方法。这两种方法都可以达到目的，但它们在处理方式和返回值上有所不同。使用includes()方法 includes()方法用于判断一个字…...

编程新知 2025/7/6 3:16:52

Ubuntu系统复制（U盘-电脑硬盘）

所需环境电脑自带硬盘：1块 (1T) U盘1：Ubuntu系统引导盘（用于“U盘2”复制到“电脑自带硬盘”） U盘2：Ubuntu系统盘（1T，用于被复制） ！！！建议“电脑…...

编程新知 2025/7/3 21:40:06

准备工作

实战

insert-存在唯一索引

insert-不存在唯一索引

update-根据唯一索引

update-根据非唯一索引

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

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

场景3: 区间更新between and

场景4: 区间更新大于

场景5: 区间更新小于

幻读

场景1

场景2

总结

相关文章：