Mysql – why innodb generate table-lock when two sql using different index

innodblockingMySQL

I know innodb-engine row lock by add lock to index item. But I don't understand the following scene.

Prepare sql:

DROP TABLE IF EXISTS `tbl_order`;
CREATE TABLE tbl_order (
    `order_id` BIGINT NOT NULL,
    `product_name` VARCHAR(16) NOT NULL,
    KEY `order_id_idx`(`order_id`),
    KEY `product_name_idx`(`product_name`)
) Engine=InnoDB CHARSET=utf8mb4;
INSERT INTO tbl_order(`order_id`, `product_name`) VALUES(1, 'prod1'), (2, 'prod2');

Session-1 execute sql:

SET autocommit=0;
SELECT @@autocommit;
SELECT `order_id`, `product_name` FROM tbl_order WHERE order_id=1 FOR UPDATE;

Session-2 execute sql:

SET autocommit=0;
SELECT @@autocommit;
SELECT `order_id`, `product_name` FROM tbl_order WHERE product_name='prod1' FOR UPDATE;

I know session-1 use order_id_idx index item, session-2 use product_naem_idx index item. I don't understand why session-1 blocks session-2.
The locks information:
enter image description here

The GEN_CLUST_INDEX indicates innodb use table-lock.
Could anyone help me?

Best Answer

Every InnoDB table should be given an explicit PRIMARY KEY. It must be "unique" and can either be a column (or combination of columns) or can be an AUTO_INCREMENT surrogate.

GEN_CLUSTER_INDEX is the fallback when you don't provide a PK.

VALUES(1, 'prod1') -- You are locking the one row from two different approaches (the secondary indexes). Hence, it is quite proper to have a lock. This lock will either lead to delaying one of the transactions until the other finished, or lead to a deadlock which will abort (and rollback) one of the transactions.

I don't believe you getting a "table lock", but instead a "row lock".

If your table is a "many-to-many" mapping table, please follow the advice in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Related Question