why is insert on table locked?

Question

I am trying something with transaction levels on mysql.

I have Session S1 and Session S2. S1 is working with default isolation lavel Repeatable Read. For S2 i set Isolation Level Serializable.

Here is the szenario:

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);

Can someone explain, why the insert into produkt from S2 is now blocked?

Here is the Tableschema:

    -- 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

You have a deadlock which MySQL describes as:

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.

Looking closer at the How to Minimize and Handle Deadlocks reads:

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.

Regarding the nature of innodb_lock_wait_timeout the documentation describe that it only applies to scenarios where inno_db_detect is disabled, which is not the default configuration:

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”.

Also, as a general tip, you'll often want to use start transaction followed as quickly as possible by a commit or rollback and not leave the session "hanging" to minimize these issues.