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

Prepare sql:

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?

  • If a record can be updated by both statements (order_id=1 and product_nema='prod1') than this is logical. – Luuk Nov 3 '19 at 16:08
  • I don't know the details around InnoDB looking, but clearly it has to lock something besides the index entries. Your scenario would otherwise cause a lost update. I had a quick glance at dev.mysql.com/doc/refman/8.0/en/innodb-locking.html but I can't tell from there how it does it. – Lennart Nov 3 '19 at 17:00

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

  • I found a answer on dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html. As you said, my table don't have explicit primary key(cluster index). The InnoDB find cluster index(GEN_CLUST_INDEX) of secondary index and lock it when I execute first sql.Second sql is the same.Index order_id_idx and product_name_idx are secondary. So two sql use same cluster index lock. Is my understanding right? – study_20160808 Nov 4 '19 at 1:48
  • @study_20160808 - I think your understanding is correct. InnoDB must lock the row; the way to do that is via the PRIMARY KEY, which is, in your case, the GEN_CLUST_INDEX. – Rick James Nov 4 '19 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.