当表没有任何索引时,为什么 innodb 使用 gen_clust_index 而不是表锁?

当表没有任何索引时,为什么 innodb 使用 gen_clust_index 而不是表锁?

我的mysql版本是5.7。我尝试测试一些有关innodb锁的情况。我知道:a.当表没有任何索引时,查询使用表锁。b.表没有任何索引,innodb将自动创建gen_clust_index。c.gen_clust_index是行锁。

我的例子:

DROP TABLE IF EXISTS `tbl_order`;
CREATE TABLE tbl_order (
    `order_id` BIGINT NOT NULL,
    `product_name` VARCHAR(16) NOT NULL
) Engine=InnoDB CHARSET=utf8mb4;
INSERT INTO tbl_order(`order_id`, `product_name`) VALUES(1, ' zhangsan1'), (1, 'lisi2');

客户端会话操作:

SET autocommit=0;
SELECT @@autocommit;
SELECT * FROM tbl_order WHERE product_name='zhangsan1' FOR UPDATE;

InnoDB引擎信息如下:

------------
TRANSACTIONS
------------
Trx id counter 36630
Purge done for trx's n:o < 36627 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 36629, ACTIVE 14 sec
2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 11, OS thread handle 9336, query id 376 localhost ::1 root
TABLE LOCK table `index_test`.`tbl_order` trx id 36629 lock mode IX
RECORD LOCKS space id 284 page no 3 n bits 72 index GEN_CLUST_INDEX of table `index_test`.`tbl_order` trx id 36629 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 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000567501; asc    Vu ;;
 1: len 6; hex 000000008f11; asc       ;;
 2: len 7; hex 30000001190858; asc 0     X;;
 3: len 8; hex 8000000000000001; asc         ;;
 4: len 5; hex 6c69736932; asc lisi2;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000567500; asc    Vu ;;
 1: len 6; hex 000000008f0f; asc       ;;
 2: len 7; hex 2f000001150932; asc /     2;;
 3: len 8; hex 8000000000000001; asc         ;;
 4: len 9; hex 7a68616e6773616e31; asc zhangsan1;;

我知道 innodb 使用 gen_clust_index 并锁定查询的所有索引记录。我不明白。我的问题是:

1. why my query use gen_clust_index instead table lock?
2. how do innodb know lock all record when it use gen_clust_index? 

相关内容