我的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?