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:
The GEN_CLUST_INDEX indicates innodb use table-lock.
Could anyone help me?
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:

Asked by study_20160808
(13 rep)
Nov 3, 2019, 12:47 PM
Last activity: Jul 3, 2025, 12:07 AM
Last activity: Jul 3, 2025, 12:07 AM