Mysql锁实战

绝地灬酷狼 2024-03-23 22:15 33阅读 0赞

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;

### 实战 ###

数据库数据分布

<table> 
 <thead> 
  <tr> 
   <th># user_age</th> 
   <th>count(0)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>12</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>14</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>15</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>16</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>17</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>18</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>19</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>40</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>41</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

#### 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 0
     0: 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 0
     0: 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 0
     0: 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 0
     0: 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 0
     0: len 8; hex 73757072656d756d; asc supremum;;

#### update-根据非唯一索引 ####

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

结论：next-key-lock

1.  session1更新user\_age=40，行锁：user\_age=19以及40，gap锁，user\_age=19-40区间：(19,40)
2.  session2更新user\_age=19，行锁：user\_age=18以及19，gap锁，user\_age=18-19区间：(18,19)
3.  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 0
     0: 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 32
     0: 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 0
     0: 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 32
     0: 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 0
     0: 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 32
     0: 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 0
     0: 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 0
     0: 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 0
     0: len 4; hex 80000029; asc    );;
     1: len 4; hex 00000009; asc     ;;

注：先更新user\_age=19的数据也一样

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

结论

1.  session1：更新user\_age=42，行锁：user\_age=42
2.  session2：更新user\_age=40（或者user\_age=19也一样），行锁：user\_age=19以及40，gap锁，user\_age=19-40区间：(19,40)
3.  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 0
     0: 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 32
     0: 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 0
     0: 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 0
     0: 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 0
     0: 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 0
     0: len 8; hex 73757072656d756d; asc supremum;;

##### 场景3: 区间更新between and #####

结论：next-key-lock

1.  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把锁。对比锁日志也可以看到，主键加锁不回重复，而普通索引加锁会出现重复加锁，类似于读写锁，主键加锁是为了更新写入数据，普通索引加锁是为了读取（检验是否存在写冲突）
2.  session2更新user\_age=41锁超时，行锁超时
3.  session3更新user\_age=18成功
4.  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 0
     0: 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 0
     0: 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 32
     0: 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 32
     0: 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 0
     0: 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 0
     0: 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 0
     0: 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 0
     0: 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 0
     0: 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 0
     0: 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 0
     0: len 4; hex 80000029; asc    );;
     1: len 4; hex 00000008; asc     ;;

##### 场景4: 区间更新大于 #####

结论

1.  更新user\_age > 19，行锁：user\_age>19所有行以及user\_age=41与12，共3条记录，6把record锁，gap锁：(19,+∞)
2.  更新user\_age=19成功
3.  更新user\_age=42成功
4.  插入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 0
     0: 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 0
     0: 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 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
     0: 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 0
     0: 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 0
     0: 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 0
     0: 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 0
     0: len 4; hex 80000029; asc    );;
     1: len 4; hex 00000009; asc     ;;

##### 场景5: 区间更新小于 #####

结论

1.  更新user\_age < 13，共一条记录+user\_age=41，4把recored锁
2.  更新user\_age=13锁超时，为什么会超时？分析锁日志是因为set值都是user\_age=41，因此在user\_age=41这条record锁获取时发生竞争锁等待
3.  更新user\_age=11成功
4.  插入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 0
     0: 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 0
     0: 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 32
     0: 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 0
     0: 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 0
     0: 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 0
     0: 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 ####

<table> 
 <thead> 
  <tr> 
   <th>session 1</th> 
   <th>session 2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>select * from my_test_user where user_age&gt;41;（无数据返回）</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>INSERT INTO <code>gallant</code>.<code>my_test_user</code> (<code>id</code>, <code>user_name</code>, <code>user_age</code>) values (14,‘jack10’,‘42’);</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>commit（执行成功）</td> 
  </tr> 
  <tr> 
   <td>select * from my_test_user where user_age&gt;41 for update;（返回数据）</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

#### 场景2 ####

<table> 
 <thead> 
  <tr> 
   <th>session 1</th> 
   <th>session 2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>select * from my_test_user where user_age&gt;41;（无数据返回）</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>update my_test_user set user_age=42 where user_age=41;</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>commit（执行成功）</td> 
  </tr> 
  <tr> 
   <td>select * from my_test_user where user_age&gt;41 for update;（返回数据）</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

### 总结 ###

1.  基于唯一索引更新，行锁
2.  基于非唯一索引更新，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，此时如果要并发更新数据库中存在的数据会锁超时
3.  基于唯一索引插入，IX锁，可以并行写入，只有索引冲突时会失败
4.  基于非唯一索引插入，IX锁，可以并行写入，无冲突
5.  MVCC或者next-key-lock均可以解决幻读，但是RR隔离级别下，某些场景可以脱离MVCC或者next-key-lock，因此RR隔离级别下可能会出现幻读
6.  很多博客强调幻读是针对insert场景，其实不然，幻读场景2中就是update产生的幻读，幻读是指在同一个事务相同条件下的两次读的数据结果不一致
7.  普通索引加record锁，每次都需要两把锁，普通索引一把锁（可以看作为读写锁中的读锁），primary key一把锁（可以看作为读写锁中的写锁），普通索引的锁可以重入不排斥，写锁不可以