MySQL LOCK--按照非聚集索引更新时如何加锁

2022-05-24 09:41:38

测试环境

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步处理下一条满足过滤条件的数据。