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_locksG
*************************** 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_locksG的结果

mysql> select * from performance_schema.data_locksG
*************************** 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_locksG
*************************** 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_locksG
*************************** 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_locksG
*************************** 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_locksG
*************************** 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_locksG
*************************** 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_locksG
*************************** 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条件，在数据库中提取与应用浅析