Sample Header Ad - 728x90

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

0 votes
1 answer
174 views
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?
Asked by study_20160808 (13 rep)
Nov 3, 2019, 12:47 PM
Last activity: Jul 3, 2025, 12:07 AM