innodb锁-不同索引下更新sql的加锁过程

一、根据主键更新

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_user set status=1 where user_id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

show engine innodb status\G;打印锁信息如下:

---TRANSACTION 118244578, ACTIVE 33 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 674699, OS thread handle 0x7feca4e21700, query id 227770774 10.10.1.1 testdata init
show engine innodb status
锁：表锁 锁模式:意向排他锁
TABLE LOCK table `test`.`test_user` trx id 118244578 lock mode IX
索引：主键  锁模式：排他锁 锁：行锁非间隙锁
RECORD LOCKS space id 6638 page no 3 n bits 80 index `PRIMARY` of table `test`.`test_user` trx id 118244578 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

二、根据唯一索引更新

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_user set status=1 where unqiue_id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

show engine innodb status\G;打印锁信息如下:

---TRANSACTION 118247675, ACTIVE 22 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 674699, OS thread handle 0x7feca4e21700, query id 227779250 10.10.1.1 testdata init
show engine innodb status
锁：表锁 锁模式:意向排他锁
TABLE LOCK table `test`.`test_user` trx id 118247675 lock mode IX
索引：唯一索引index_unique  锁模式：排他锁 锁：行锁非间隙锁
RECORD LOCKS space id 6643 page no 4 n bits 72 index `index_unique` of table `test`.`test_user` trx id 118247675 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 8; hex 8000000000000001; asc         ;;
索引：主键  锁模式：排他锁 锁：行锁非间隙锁
RECORD LOCKS space id 6643 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_user` trx id 118247675 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 0000070c50fb; asc     P ;;
 2: len 7; hex 59000012521956; asc Y   R V;;
 3: len 10; hex 61202020202020202020; asc a         ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 8; hex 8000000000000001; asc         ;;

三、根据辅助索引更新

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_user set status=1 where name='a';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

show engine innodb status\G;打印锁信息如下:

---TRANSACTION 118248290, ACTIVE 18 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 674699, OS thread handle 0x7feca4e21700, query id 227781061 10.10.1.1 testdata init
show engine innodb status
锁：表锁 锁模式:意向排他锁
TABLE LOCK table `test`.`test_user` trx id 118248290 lock mode IX
索引：辅助索引index_user  锁模式：排他锁 锁：行锁
RECORD LOCKS space id 6643 page no 5 n bits 72 index `index_user` of table `test`.`test_user` trx id 118248290 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 10; hex 61202020202020202020; asc a         ;;
 1: len 8; hex 8000000000000001; asc         ;;

索引：主键  锁模式：排他锁 锁：行锁非间隙锁
RECORD LOCKS space id 6643 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_user` trx id 118248290 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 0000070c5362; asc     Sb;;
 2: len 7; hex 61000011610693; asc a   a  ;;
 3: len 10; hex 61202020202020202020; asc a         ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 8; hex 8000000000000001; asc         ;;

索引：辅助索引index_user  锁模式：排他锁 锁：间隙锁+行锁(也就是next-key锁)
RECORD LOCKS space id 6643 page no 5 n bits 72 index `index_user` of table `test`.`test_user` trx id 118248290 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 10; hex 63202020202020202020; asc c         ;;
 1: len 8; hex 8000000000000003; asc         ;;

四、根据非索引字段更新

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_user set status=1 where status=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

show engine innodb status\G;打印锁信息如下:

---TRANSACTION 118249742, ACTIVE 19 sec
2 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 674699, OS thread handle 0x7feca4e21700, query id 227785026 10.10.1.1 testdata init
show engine innodb status
锁：表锁 锁模式:意向排他锁
TABLE LOCK table `test`.`test_user` trx id 118249742 lock mode IX
索引：主键  锁模式：排他锁 锁：行锁
RECORD LOCKS space id 6643 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_user` trx id 118249742 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 0000070c4dab; asc     M ;;
 2: len 7; hex 45000001391fb1; asc E   9  ;;
 3: len 10; hex 61202020202020202020; asc a         ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 8; hex 8000000000000001; asc         ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 6; hex 0000070c4db0; asc     M ;;
 2: len 7; hex 490000115c065b; asc I   \ [;;
 3: len 10; hex 63202020202020202020; asc c         ;;
 4: len 4; hex 80000002; asc     ;;
 5: len 8; hex 8000000000000002; asc         ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 6; hex 0000070c4db8; asc     M ;;
 2: len 7; hex 4e000001ef108a; asc N      ;;
 3: len 10; hex 65202020202020202020; asc e         ;;
 4: len 4; hex 80000003; asc     ;;
 5: len 8; hex 8000000000000003; asc         ;;

总结：
1.查看锁的详细信息需要打开锁监控功能，可以参考:innodb锁-开启锁监控功能
2.根据主键索引、唯一索引、非索引字段更新数据不会产生间隙锁，只有辅助索引才会产生间隙锁