innodb full table lock during simple insert

Question

Our code does

insert into user (email, name) values ((),()) // 1000 rows at a time, without specifying primary key value

Such inserts run in parallel on multiple server and with high concurrency (sometimes even at 2 parallel) many of them fails due to lock wait timeout. Show innodb status shows below

 mysql tables in use 1, locked 1
2592 lock struct(s), heap size 319696, 109498 row lock(s), undo log entries 51785
MySQL thread id 1623383, OS thread handle 47552100853504, query id 1459767730 10.250.232.11 admin update
insert into user (account_id, email, first_name, manager_id) values (....)
TABLE LOCK table `user` trx id 1952681605 lock mode IX
RECORD LOCKS space id 2217 page no 1034274 n bits 280 index usertbl_email_account_id_unique_idx of table `user` trx id 1952681605 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

RECORD LOCKS space id 2217 page no 273318 n bits 280 index usertbl_email_account_id_unique_idx of table `user` trx id 1952681605 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 33 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 30; hex 647368656e2b31303037314061646f6265746573742e636f6d2020202020; asc [email protected]     ; (total 64 bytes);
 1: len 4; hex 000004c3; asc     ;;
 2: len 4; hex 01953758; asc   7X;;


 TABLE LOCK table `account` trx id 1952681605 lock mode IS
 RECORD LOCKS space id 1954 page no 617 n bits 72 index PRIMARY of table `account` trx id 1952681605 lock mode S locks rec but not gap
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 40; compact format; info bits 0
  0: len 4; hex 000004c3; asc     ;;
  1: len 6; hex 000070666e53; asc   pfnS;;
  2: len 7; hex 020000100b1b33; asc       3;;
  3: len 30; hex 396236396235306633656561343536373938343064616637323762316534; asc 9b69b50f3eea45679840daf727b1e4; (total 32 bytes);
  4: len 1; hex 61; asc a;;
  5: SQL NULL;
  6: len 5; hex 496e646961; asc ;;
  7: SQL NULL;
  8: SQL NULL;

We are on mysql 8.0.13, innodb_autoinc_lock_mode is set to engine default (2 interleaved) which is most relaxed mode. We do not use MySQL level LOCK TABLE in code, so we are confused why is innodb locking full user table in IX mode? The table looks like below

CREATE TABLE user (
  `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id` INT UNSIGNED NOT NULL,
  `email` CHAR(64) NOT NULL,
  `first_name` VARCHAR(63) NOT NULL,
  `manager_id` INT UNSIGNED NOT NULL
  PRIMARY KEY (`user_id`),
  INDEX `usertbl_account_id_fk_idx` (`account_id` ASC),
  INDEX `usertbl_user_id_account_id_idx` (`user_id` ASC, `account_id` ASC, `first_name` ASC) COMMENT 'To server as a FK into other tables.',
  UNIQUE INDEX `usertbl_email_account_id_unique_idx` (`email` ASC, `account_id` ASC)
 CONSTRAINT `usertbl_account_id_fk`
    FOREIGN KEY (`account_id`)
    REFERENCES `account` (`account_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 CONSTRAINT `usertbl_manager_id_fk_idx`
    FOREIGN KEY (`manager_id` , `account_id`)
    REFERENCES `user` (`user_id` , `account_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

usertbl_user_id_account_id_idx is needed because few tables have a Foreign Key into user table as shown below

CREATE TABLE `course_author` (
  `course_id` INT UNSIGNED NOT NULL,
  `author_id` INT UNSIGNED NOT NULL,
  `account_id` INT UNSIGNED NOT NULL,
  `first_name` VARCHAR(63) NOT NULL,
  PRIMARY KEY (`course_id`, `author_id`),
  INDEX `catbl_author_id_fk_idx` (`author_id` ASC, `account_id` ASC) COMMENT 'For searching courses of an author' ,
  CONSTRAINT `catbl_author_fk`
    FOREIGN KEY (`author_id` , `account_id`, `first_name`)
    REFERENCES `user` (`user_id` , `account_id`, `first_name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Answer 1

It is almost never useful to start a secondary index with the PRIMARY KEY; DROP INDEX usertbl_user_id_account_id_idx. Note: A PRIMARY KEY is an index.

You say 'To server as a FK into other tables.' -- Let's see the other table(s).

Please provide more of SHOW ENGINE INNODB STATUS; -- the one line you provide is not sufficient.

Answer 2

I assume you're asking about these:

TABLE LOCK table `user` trx id 1952681605 lock mode IX

TABLE LOCK table `account` trx id 1952681605 lock mode IS

The IX and IS locks are table locks that block other table locks, like those from ALTER TABLE or DROP TABLE or CREATE TRIGGER or such. These are called metadata locks.

Any SELECT/INSERT/UPDATE/DELETE against a table acquires a metadata lock, so no other session tries to ALTER or DROP the table while you're querying it. But these metadata locks don't block each other; multiple sessions can still do concurrent non-DDL queries against the same table without necessarily interfering with each other, unless they conflict on the same rows.

The reason your INSERT to user causes an IS metadata lock on account is that there's a foreign key reference from user to account. You're inserting a row that references the account table via the foreign key, therefore the account table must not be altered or dropped until your transaction is done.