MySQL update语句加锁分析
1. 通过二级唯一索引更新聚簇索引
表结构如下：
CREATE TABLE `test_lock_cluster` (
`id` int NOT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
- 无主键冲突
插入一条记录：
insert into test_lock_cluster values(2, 3);
接着开启事务，执行更新操作
begin;
update test_lock_cluster set id=id+1 where age=3;
此时会加什么锁呢？容易想到的是where条件中的二级唯一索引加X锁，即age=3这条记录加上X锁，并且对应的聚簇索引也加X锁，同时，RR隔离级别下为避免幻读，会加GAP锁，那么GAP锁究竟会加在哪里呢？
首先，使用show engine innodb status;看一下innodb的状态（已去掉跟锁无关的日志）：
---TRANSACTION 3113, ACTIVE 73 sec
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2
MySQL thread id 11, OS thread handle 123145555865600, query id 82 localhost root
TABLE LOCK table `dian_test`.`test_lock_cluster` trx id 3113 lock mode IX # lock mode IX: 表级意向写锁
# 下面一句表示在二级唯一索引上加了X锁
RECORD LOCKS space id 3 page no 5 n bits 72 index age of table `dian_test`.`test_lock_cluster` trx id 3113 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000002; asc ;;
# 聚簇索引上加了X锁
RECORD LOCKS space id 3 page no 4 n bits 72 index PRIMARY of table `dian_test`.`test_lock_cluster` trx id 3113 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000000c29; asc );;
2: len 7; hex 020000010f050f; asc ;;
3: len 4; hex 80000003; asc ;;
# 以下几行表明在二级唯一索引上加了2个GAP锁
RECORD LOCKS space id 3 page no 5 n bits 72 index age of table `dian_test`.`test_lock_cluster` trx id 3113 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000003; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000002; asc ;;
# 在上确界(supremum)加了读GAP锁，注意这里的lock mode S并不是next-key锁，因为heap no 1说明锁是加在supremum上，属于GAP锁
RECORD LOCKS space id 3 page no 5 n bits 72 index age of table `dian_test`.`test_lock_cluster` trx id 3113 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
以上日志说明总共加了6把锁，1个表锁(IX)和5个记录锁(2个X锁+3个GAP锁)，除了二级索引上确界的那把GAP锁，另外的两个GAP锁加的地方可以进一步从performance_schema.data_locks表里查看更多信息：
select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140428987080008:1062:140429194506000
ENGINE_TRANSACTION_ID: 3113
THREAD_ID: 51
EVENT_ID: 59
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140429194506000
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED #表级意向写锁
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140428987080008:3:5:3:140429198791712
ENGINE_TRANSACTION_ID: 3113
THREAD_ID: 51
EVENT_ID: 59
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140429198791712
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3, 2 # age=3的二级唯一索引上加X锁
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140428987080008:3:4:3:140429198792056
ENGINE_TRANSACTION_ID: 3113
THREAD_ID: 51
EVENT_ID: 59
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140429198792056
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2 # id=2的聚簇索引上加X锁
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140428987080008:3:5:2:140429198792400
ENGINE_TRANSACTION_ID: 3113
THREAD_ID: 51
EVENT_ID: 59
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140429198792400
LOCK_TYPE: RECORD
LOCK_MODE: S,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3, 3 # 修改后age=3的二级唯一索引之前加读GAP锁
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140428987080008:3:5:3:140429198792400
ENGINE_TRANSACTION_ID: 3113
THREAD_ID: 51
EVENT_ID: 59
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140429198792400
LOCK_TYPE: RECORD
LOCK_MODE: S,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3, 2 # 修改前age=3的二级唯一索引之前加读GAP锁
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140428987080008:3:5:1:140429198792744
ENGINE_TRANSACTION_ID: 3113
THREAD_ID: 51
EVENT_ID: 59
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140429198792744
LOCK_TYPE: RECORD
LOCK_MODE: S
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record # 上确界加读GAP锁
上面是增加主键的值所以在上确界加了GAP锁，如果是减少主键的值，就会在下确界加GAP锁，其他的锁基本相同。
总结起来，通过二级唯一索引更新聚簇索引时，总共会加6把锁，分别是：表级意向写锁(IX)、二级唯一索引上的X锁(age=3)、聚簇索引上的X锁(id=2)、二级索引上确界的读GAP锁(age>3)、二级索引更新之前记录前的读GAP锁(age=3,id=2)、二级索引更新之后记录前的读GAP锁(age=3,id=3).
- 主键冲突时的加锁情况
mysql> select * from test_lock_cluster;
+----+------+
| id | age |
+----+------+
| 1 | 4 |
| 2 | 5 |
| 9 | 10 |
| 10 | 11 |
| 11 | 12 |
| 12 | 13 |
| 13 | 14 |
| 15 | 15 |
+----+------+
mysql> update test_lock_cluster set id=id-1 where age=14;
ERROR 1062 (23000): Duplicate entry '12' for key 'test_lock_cluster.PRIMARY'
show engine innodb status;的结果：
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 11, OS thread handle 123145349824512, query id 80 localhost root
TABLE LOCK table `dian_test`.`test_lock_cluster` trx id 9761 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 80 index age of table `dian_test`.`test_lock_cluster` trx id 9761 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000e; asc ;;
1: len 4; hex 8000000d; asc ;;
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `dian_test`.`test_lock_cluster` trx id 9761 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000000261f; asc & ;;
2: len 7; hex 81000001070110; asc ;;
3: len 4; hex 8000000e; asc ;;
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `dian_test`.`test_lock_cluster` trx id 9761 lock mode S locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000c; asc ;;
1: len 6; hex 000000002619; asc & ;;
2: len 7; hex 82000001080110; asc ;;
3: len 4; hex 8000000d; asc ;;
select * from performance_schema.data_locks\G的结果
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:1062:140459838089152
ENGINE_TRANSACTION_ID: 9761
THREAD_ID: 52
EVENT_ID: 33
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140459838089152
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:3:5:7:140459842314272
ENGINE_TRANSACTION_ID: 9761
THREAD_ID: 52
EVENT_ID: 33
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140459842314272
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 14, 13
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:3:4:7:140459842314616
ENGINE_TRANSACTION_ID: 9761
THREAD_ID: 52
EVENT_ID: 33
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140459842314616
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 13
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:3:4:6:140459842314960
ENGINE_TRANSACTION_ID: 9761
THREAD_ID: 52
EVENT_ID: 33
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140459842314960
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 12
4 rows in set (0.00 sec)
通过二级索引更新主键产生主键冲突时，会加4把锁，分别是表级意向写锁、二级索引上的记录X锁、更新前主键上的记录X锁、产生冲突的主键记录X锁。
2. 通过聚簇索引更新二级唯一索引
- 二级唯一索引无冲突的情况
mysql> select * from test_lock_cluster;
+----+------+
| id | age |
+----+------+
| 2 | 3 |
+----+------+
begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_lock_cluster set age=age+1 where id=2;
---------------------------------------------------------------
mysql> show engine innodb status;
---TRANSACTION 4103, ACTIVE 3 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 123145454256128, query id 22 localhost root
---------------------------------------------------------------
# 查加锁情况
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140274368257352:1062:140274638573920
ENGINE_TRANSACTION_ID: 4103
THREAD_ID: 57
EVENT_ID: 20
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140274638573920
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140274368257352:3:4:3:140274630211616
ENGINE_TRANSACTION_ID: 4103
THREAD_ID: 57
EVENT_ID: 20
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140274630211616
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
2 rows in set (0.00 sec)
二级唯一索引无冲突时，只加了意向写锁(IX)和聚簇索引上的X锁。
- 二级唯一索引有冲突的情况
mysql> insert into test_lock_cluster values(3,5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_lock_cluster;
+----+------+
| id | age |
+----+------+
| 2 | 4 |
| 3 | 5 |
+----+------+
2 rows in set (0.00 sec)
#开启事务，执行updaet
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_lock_cluster set age=age+1 where id=2;
---------------------------------------------------------------
---TRANSACTION 4110, ACTIVE 4 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 16, OS thread handle 123145454256128, query id 33 localhost root
---------------------------------------------------------------
# 查询加锁情况
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140274368257352:1062:140274638573920
ENGINE_TRANSACTION_ID: 4110
THREAD_ID: 57
EVENT_ID: 31
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140274638573920
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140274368257352:3:4:3:140274630211616
ENGINE_TRANSACTION_ID: 4110
THREAD_ID: 57
EVENT_ID: 31
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140274630211616
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140274368257352:3:5:3:140274630211960
ENGINE_TRANSACTION_ID: 4110
THREAD_ID: 57
EVENT_ID: 31
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140274630211960
LOCK_TYPE: RECORD
LOCK_MODE: S
LOCK_STATUS: GRANTED
LOCK_DATA: 5, 3
3 rows in set (0.01 sec)
发生唯一键冲突时，除了在聚簇索引加X锁，冲突的二级索引上也加了S锁。
3. 通过主键更新二级非唯一索引字段
test_lock_cluster | CREATE TABLE `test_lock_cluster` (
`id` int NOT NULL,
`age` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `age` (`age`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB;
# 开启事务，执行更新语句
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_lock_cluster set name='aa' where id=16;
Query OK, 1 row affected (0.00 sec)
# 查询加锁情况
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:1062:140459838089152
ENGINE_TRANSACTION_ID: 9782
THREAD_ID: 52
EVENT_ID: 55
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140459838089152
LOCK_TYPE: TABLE
LOCK_MODE: IX //表级意向读锁
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:3:4:12:140459842314272
ENGINE_TRANSACTION_ID: 9782
THREAD_ID: 52
EVENT_ID: 55
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140459842314272
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP //主键记录X锁
LOCK_STATUS: GRANTED
LOCK_DATA: 16
2 rows in set (0.00 sec)
加锁分析：总共两把锁，分别是表级意向写锁和聚簇索引记录X锁
4. 通过唯一索引更新二级非唯一索引字段
表结构同上。
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> update test_lock_cluster set name='ab' where age=16;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:1062:140459838089152
ENGINE_TRANSACTION_ID: 9788
THREAD_ID: 52
EVENT_ID: 62
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140459838089152
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:3:5:8:140459842314272
ENGINE_TRANSACTION_ID: 9788
THREAD_ID: 52
EVENT_ID: 62
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140459842314272
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 16, 16
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:3:4:12:140459842314616
ENGINE_TRANSACTION_ID: 9788
THREAD_ID: 52
EVENT_ID: 62
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140459842314616
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 16
3 rows in set (0.00 sec)
加锁分析：总共3把锁，表级意向写锁，唯一索引上的记录锁，以及聚集索引上的记录锁。
5. 通过主键更新非索引字段
CREATE TABLE `test_lock_cluster` (
`id` int NOT NULL,
`age` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`addr` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `age` (`age`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB
# 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_lock_cluster set addr='hubei' where id=17;
Query OK, 1 row affected (0.00 sec)
# 查询加锁情况
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:1062:140459838089152
ENGINE_TRANSACTION_ID: 9803
THREAD_ID: 52
EVENT_ID: 76
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140459838089152
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:3:4:13:140459842314272
ENGINE_TRANSACTION_ID: 9803
THREAD_ID: 52
EVENT_ID: 76
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140459842314272
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 17
2 rows in set (0.00 sec)
加锁分析：总共2把锁，表级意向写锁，以及聚集索引上的x锁。
6. 通过唯一索引更新非索引字段
# 查询加锁情况（省略了SQL语句）
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:1062:140459838089152
ENGINE_TRANSACTION_ID: 9805
THREAD_ID: 52
EVENT_ID: 82
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140459838089152
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:3:5:11:140459842314272
ENGINE_TRANSACTION_ID: 9805
THREAD_ID: 52
EVENT_ID: 82
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140459842314272
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 17, 17
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140459623517512:3:4:13:140459842314616
ENGINE_TRANSACTION_ID: 9805
THREAD_ID: 52
EVENT_ID: 82
OBJECT_SCHEMA: dian_test
OBJECT_NAME: test_lock_cluster
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140459842314616
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 17
3 rows in set (0.00 sec)
加锁分析：总共3把锁，表级意向写锁，唯一索引上的记录锁，以及聚集索引上加记录锁。
7. 总结
以上总共列出了6大类更新语句的加锁情况，可以看出，每种更新都会加表级意向写锁，除此之外，还会有记录锁、GAP锁等出席，这里做一下总结（不再列出IX锁）：
- 通过二级唯一索引更新聚簇索引：
- 无主键冲突时，总共会加5把锁，分别是：二级唯一索引上的X锁、聚簇索引上的X锁、二级索引上确界的读GAP锁、二级索引更新之前记录前的读GAP锁、二级索引更新之后记录前的读GAP锁
- 主键冲突时，会加3把锁，二级索引上的记录X锁、更新前主键上的记录X锁、产生冲突的主键记录X锁。
- 通过主键更新唯一索引时
- 无索引冲突，加聚簇索引上的X锁。
- 索引冲突时，表级聚簇索引加X锁，冲突的二级索引上也加了S锁
- 通过主键更新二级非唯一索引字段，聚簇索引记录X锁
- 通过唯一索引更新二级非唯一索引字段，唯一索引上以及聚集索引上加记录锁
- 通过主键更新非索引字段, 聚集索引上加x锁
- 通过唯一索引更新非索引字段，唯一索引上的记录锁，以及聚集索引上加记录锁
8. 参考资料
- 解决死锁之路（终结篇） - 再见死锁
- 10分钟让你明白MySQL是如何利用索引的
- InnoDB 锁的类型
- 解决死锁之路 - 常见 SQL 语句的加锁分析
- 何登成的技术博客 » MySQL 加锁处理分析
- MySQL · 引擎特性 · InnoDB 事务锁系统简介
- 8.2.1.5 Index Condition Pushdown Optimization
- 何登成的技术博客 » SQL中的where条件，在数据库中提取与应用浅析
