测试环境
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步处理下一条满足过滤条件的数据。MySQL LOCK--按照非聚集索引更新时如何加锁
标签：record mode dem format from tin its select fetch
查看更多关于MySQL LOCK--按照非聚集索引更新时如何加锁的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did117408