测试环境
- MySQL版本： 5.7.30
- 事务级别： READ-COMMITTED
测试数据
mysql> show create table tb1001 G
*************************** 1. row ***************************
Table: tb1001
Create Table: CREATE TABLE `tb1001` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL,
`c2` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=524273 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from tb1001 limit 10;
+----+----+----+
| id | c1 | c2 |
+----+----+----+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 10 | 10 | 10 |
| 11 | 11 | 11 |
| 12 | 12 | 12 |
| 13 | 13 | 13 |
| 14 | 14 | 14 |
| 15 | 15 | 15 |
| 16 | 16 | 16 |
+----+----+----+
10 rows in set (0.00 sec)
测试准备
会话1：按照聚集索引锁定id=3的数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb1001 where id=2 for update;
+----+----+----+
| id | c1 | c2 |
+----+----+----+
| 2 | 2 | 2 |
+----+----+----+
1 row in set (0.00 sec)
测试01
会话2：按照非聚集索引查找c1<10的数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update tb1001 set c2=0 where c1<10;
会话2被阻塞，查看锁信息：
show engine innodb status G
---TRANSACTION 6334452, ACTIVE 10 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 3162307, OS thread handle 140609456948992, query id 44172685 172.16.24.1 mysql_admin updating
update tb1001 set c2=0 where c1<10
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 4 n bits 552 index PRIMARY of table `demodb`.`tb1001` trx id 6334452 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000005d73ec; asc ]s ;;
2: len 7; hex c1000040230110; asc @# ;;
3: len 4; hex 80000002; asc ;;
4: len 4; hex 80000002; asc ;;
------------------
---TRANSACTION 6334441, ACTIVE 39 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3162293, OS thread handle 140609595356928, query id 44172194 172.16.24.1 mysql_admin
--------
可以发现会话2等待id=2的主键索引(聚集索引)上的锁，同时会话2上持有3个ROW LOCK。
测试02
保持会话2的等待，执行会话3：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb1001 where id=1 for update;
会话3被阻塞，查看锁信息：
show engine innodb status G
---TRANSACTION 6334476, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3162313, OS thread handle 140610455860992, query id 44173872 172.16.24.1 mysql_admin statistics
select * from tb1001 where id=1 for update
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 4 n bits 552 index PRIMARY of table `demodb`.`tb1001` trx id 6334476 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000060a80b; asc ` ;;
2: len 7; hex 4c0000014929bb; asc L I) ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000000; asc ;;
------------------
---TRANSACTION 6334475, ACTIVE 18 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 3162307, OS thread handle 140609456948992, query id 44173766 172.16.24.1 mysql_admin updating
update tb1001 set c2=0 where c1<10
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 4 n bits 552 index PRIMARY of table `demodb`.`tb1001` trx id 6334475 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000005d73ec; asc ]s ;;
2: len 7; hex c1000040230110; asc @# ;;
3: len 4; hex 80000002; asc ;;
4: len 4; hex 80000002; asc ;;
------------------
---TRANSACTION 6334441, ACTIVE 112 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3162293, OS thread handle 140609595356928, query id 44174043 172.16.24.1 mysql_admin starting
show engine innodb status
--------
可以发现会话3等待id=1的主键索引(聚集索引)上的锁,持有锁的对象是会话2.
测试03
保持会话2的阻塞状态，执行会话3：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select c1 from tb1001 where c1=3 lock in share mode;
+----+
| c1 |
+----+
| 3 |
+----+
1 row in set (0.00 sec)
mysql> select * from tb1001 where id=3 lock in share mode;
+----+----+----+
| id | c1 | c2 |
+----+----+----+
| 3 | 3 | 3 |
+----+----+----+
1 row in set (0.01 sec)
可以发现会话2并未持有id=3的主键索引(聚集索引)上的锁,也没有持有c1=3的非聚集索引idx_c1上的锁。
测试04
保持会话2的阻塞状态，执行会话3：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select c1 from tb1001 where c1=2 for update;
会话被阻塞，查看锁信息：
mysql> select * from information_schema.INNODB_LOCKS;
+------------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| 6336196:168:37:3 | 6336196 | X | RECORD | `demodb`.`tb1001` | idx_c1 | 168 | 37 | 3 | 2 |
| 6336195:168:37:3 | 6336195 | X | RECORD | `demodb`.`tb1001` | idx_c1 | 168 | 37 | 3 | 2 |
| 6336195:168:4:3 | 6336195 | X | RECORD | `demodb`.`tb1001` | PRIMARY | 168 | 4 | 3 | 2 |
| 6335370:168:4:3 | 6335370 | X | RECORD | `demodb`.`tb1001` | PRIMARY | 168 | 4 | 3 | 2 |
+------------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
4 rows in set, 1 warning (0.00 sec)
show engine innodb status G
---TRANSACTION 6336196, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3162313, OS thread handle 140610455860992, query id 44254752 172.16.24.1 mysql_admin statistics
select c1 from tb1001 where c1=2 for update
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 37 n bits 1152 index idx_c1 of table `demodb`.`tb1001` trx id 6336196 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 4; hex 80000002; asc ;;
------------------
---TRANSACTION 6336195, ACTIVE 15 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 3162307, OS thread handle 140609456948992, query id 44254633 172.16.24.1 mysql_admin updating
update tb1001 set c2=0 where c1<10
------- TRX HAS BEEN WAITING 15 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 4 n bits 552 index PRIMARY of table `demodb`.`tb1001` trx id 6336195 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000005d73ec; asc ]s ;;
2: len 7; hex c1000040230110; asc @# ;;
3: len 4; hex 80000002; asc ;;
4: len 4; hex 80000002; asc ;;
------------------
---TRANSACTION 6335370, ACTIVE 2365 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3162293, OS thread handle 140609595356928, query id 44254874 172.16.24.1 mysql_admin starting
show engine innodb status
--------
可以发现会话3(trx_id=6336196)等待会话2(trx_id=6336195)的在idx_c1上c1=2的行锁(索引记录)，会话2(trx_id=6336195)等待会话1(trx=6335370)的在PRIMARY上id=2的行锁(主键记录)。
测试结论
当使用非聚集索引列进行数据更新时，MySQL会使用非聚集索引进行查找，对于查找到满足过滤条件的每一行索引记录：
- 在查找到的非聚集索引记录上加锁。
- 根据非聚集索引记录上包含的聚集索引键值进行回表查找。
- 在查找到的聚集索引记录上加锁。
- 循环1、2、3步处理下一条满足过滤条件的数据。