Mysql Lock Update

相关版本

Mysql： 5.7
事务级别：read-committed

数据准备

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('1', '关羽');
INSERT INTO `user` VALUES ('2', '张飞');
INSERT INTO `user` VALUES ('3', '刘备');
INSERT INTO `user` VALUES ('4', '董卓');
INSERT INTO `user` VALUES ('5', '曹操');
INSERT INTO `user` VALUES ('6', '赵云');
INSERT INTO `user` VALUES ('7', '鲁肃');
INSERT INTO `user` VALUES ('8', '孙权');

无索引场景

窗口A

start transaction;

update user set name='关羽2' where name='关羽';

窗口B

start transaction;

update user set name ='张飞3' where id=2;

窗口C

set global innodb_status_output_locks = 1 
show engine innodb status\G

MySQL thread id 15, OS thread handle 10864, query id 171 localhost ::1 ODBC
TABLE LOCK table `test`.`user` trx id 1287901 lock mode IX
RECORD LOCKS space id 927 page no 3 n bits 80 index GEN_CLUST_INDEX of table `test`.`user` trx id 1287901 lock_mode X locks rec but not gap
Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000989f01; asc       ;;
 1: len 6; hex 00000013a6dd; asc       ;;
 2: len 7; hex 31000001581650; asc 1   X P;;
 3: len 4; hex 00000002; asc     ;;
 4: len 7; hex e5bca0e9a39e33; asc       3;;

---TRANSACTION 1287900, ACTIVE 45 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 14, OS thread handle 10760, query id 168 localhost ::1 ODBC
TABLE LOCK table `test`.`user` trx id 1287900 lock mode IX
RECORD LOCKS space id 927 page no 3 n bits 80 index GEN_CLUST_INDEX of table `test`.`user` trx id 1287900 lock_mode X locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000989f00; asc       ;;
 1: len 6; hex 00000013a6dc; asc       ;;
 2: len 7; hex 30000001c42ba3; asc 0    + ;;
 3: len 4; hex 00000001; asc     ;;
 4: len 7; hex e585b3e7bebd32; asc       2;;

分析过程

1、GEN_CLUST_INDEX

如果表既没有主键也没有合适的唯一索引，则 InnoDB 会为表创建一个隐藏的聚簇索引 GEN_CLUST_INDEX，该索引基于 InnoDB 为表自动添加的包含行ID值的列，所有表数据会基于该ID值排序。行ID值是一个6字节数值，会随着数据的插入单调递增，因此基于此列排序的表在物理上保持着数据插入顺序

2、lock_mode X locks rec but not gap 记录锁

3、explain 分析

EXPLAIN update user set name='关羽2' where name='关羽';

EXPLAIN update user set name ='张飞3' where id=2;

4、插入、更新、删除测试

insert into user values (10,'赵云');              //query ok
update user set name='赵云2' where id = 10        // query ok
delete from user where id = 10                    // Lock wait timeout exceeded; try restarting transaction

有索引场景

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

窗口A

rollback;
start transaction;
update user set name='关羽2' where name='关羽';

窗口B

rollback;
start transaction;
update user set name ='张飞3' where id=2;

窗口C

set global innodb_status_output_locks = 1 
show engine innodb status\G

MySQL thread id 15, OS thread handle 10864, query id 306 localhost ::1 ODBC
TABLE LOCK table `test`.`user` trx id 1287937 lock mode IX
RECORD LOCKS space id 928 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 1287937 lock_mode X locks rec but not gap
Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 00000013a701; asc       ;;
 2: len 7; hex 2a000002410110; asc *   A  ;;
 3: len 7; hex e5bca0e9a39e33; asc       3;;

---TRANSACTION 1287936, ACTIVE 32 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 14, OS thread handle 10760, query id 305 localhost ::1 ODBC
TABLE LOCK table `test`.`user` trx id 1287936 lock mode IX
RECORD LOCKS space id 928 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 1287936 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000013a700; asc       ;;
 2: len 7; hex 290000017f0266; asc )     f;;
 3: len 7; hex e585b3e7bebd32; asc       2;;

分析

1、index PRIMARY 主键索引 （mysql lock 是加在索引上的）
2、lock_mode X locks rec but not gap 记录锁
3、explain

EXPLAIN update user set name='关羽2' where name='关羽';

EXPLAIN update user set name ='张飞3' where id=2;

system：系统表，少量数据，往往不需要进行磁盘IO
const：常量连接
eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
ref：非主键非唯一索引等值扫描
range：范围扫描
index：索引树扫描
ALL：全表扫描(full table scan)；

system > const > eq_ref > ref > range > index > ALL

4、插入、更新、删除测试

insert into user values (11,'马谡')                // query ok
update user set name='马谡2' where id = 11         // query ok
delete from user where id = 11                     // query ok

总结

1、mysql 默认会通过主键索引，来更新数据，更新的条件不同，使用索引树的效率不同。
2、没有主键下， InnoDB 会为表创建一个隐藏的聚簇索引 GEN_CLUST_INDEX，来实现行级别的加锁，增改可以继续，删除需要锁等待。

引用

https://cloud.tencent.com/developer/article/2275943
https://blog.csdn.net/Allenzyg/article/details/107813500