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