mysql - 为什么表上的插入被锁定？

Question

我正在 mysql 上尝试一些事务级别的东西。

我有 session S1 和 session S2。 S1 使用默认隔离层可重复读取。对于 S2，我设置了可串行化的隔离级别。

这是场景:

S1:

set innodb_lock_wait_timeout = 5;
start transaction;

S2:

set session transaction isolation level serializable;
start transaction;
select count(*) from produkt;

S1:

select count(*) from produkt;
update kategorie set bezeichnung = 'Smartphone' where kategorieid = 1;

S2:

 insert into produkt(produktid, bezeichnung, kategorieid_fk) values (201, 'iPhone 8z', 1);

有人可以解释一下，为什么从 S2 的插入 produkt 现在被阻止了？

这是表架构:

    -- Exportiere Datenbank Struktur für transaktiondb
    CREATE DATABASE IF NOT EXISTS `transaktiondb`;
    USE `transaktiondb`;


    -- Exportiere Struktur von Tabelle transaktiondb.kategorie
    CREATE TABLE IF NOT EXISTS `kategorie` (
      `KategorieID` int(11) NOT NULL AUTO_INCREMENT,
      `Bezeichnung` varchar(255) NOT NULL,
      PRIMARY KEY (`KategorieID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    -- Exportiere Struktur von Tabelle transaktiondb.produkt
    CREATE TABLE IF NOT EXISTS `produkt` (
      `ProduktID` int(11) NOT NULL AUTO_INCREMENT,
      `Bezeichnung` varchar(255) NOT NULL,
      `KategorieID_FK` int(11) NOT NULL,
      PRIMARY KEY (`ProduktID`),
      KEY `fk_Produkt_Kategorie_idx` (`KategorieID_FK`),
      CONSTRAINT `fk_Produkt_Kategorie` FOREIGN KEY (`KategorieID_FK`) REFERENCES `kategorie` (`KategorieID`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

您有一个deadlock MySQL 描述为:

A situation where different transactions are unable to proceed, because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither will ever release the locks it holds.

仔细观察How to Minimize and Handle Deadlocks内容如下:

InnoDB uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really “atomic”; they automatically set locks on the (possibly several) index records of the row inserted or deleted.

关于 innodb_lock_wait_timeout 的性质，文档描述它仅适用于禁用 inno_db_detect 的场景，这不是默认配置:

The lock wait timeout value does not apply to deadlocks when innodb_deadlock_detect is enabled (the default) because InnoDB detects deadlocks immediately and rolls back one of the deadlocked transactions. When innodb_deadlock_detect is disabled, InnoDB relies on innodb_lock_wait_timeout for transaction rollback when a deadlock occurs. See Section 14.7.5.2, “Deadlock Detection and Rollback”.

此外，作为一般提示，您通常会希望使用启动事务，然后尽快使用提交或回滚并且不要让 session “挂起”，以尽量减少这些问题。