mysql - 简单插入期间innodb全表锁

Question

我们的代码可以

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

此类插入在多个服务器上并行运行，并且具有高并发性(有时甚至是 2 个并行)，其中许多插入由于锁等待超时而失败。显示innodb状态如下所示

 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 dummy+10071@dummy.com     ; (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;

我们使用的是 mysql 8.0.13，innodb_autoinc_lock_mode 设置为引擎默认值(2 个交错)，这是最宽松的模式。我们在代码中没有使用MySQL级别的LOCK TABLE，所以我们很困惑为什么innodb在IX模式下锁定完整的用户表？该表如下所示

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，因为很少有表具有用户表的外键，如下所示

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

我假设您问的是这些:

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

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

IX 和 IS 锁是阻止其他表锁的表锁，例如来自 ALTER TABLE 或 DROP TABLE 或 CREATE TRIGGER 等的表锁。这些称为元数据锁。

针对表的任何 SELECT/INSERT/UPDATE/DELETE 都会获取元数据锁，因此在您查询表时，其他 session 不会尝试更改或删除该表。但这些元数据锁不会互相阻塞；多个 session 仍然可以对同一个表执行并发非 DDL 查询，而不必相互干扰，除非它们在同一行上发生冲突。

对 user 的 INSERT 导致 account 上的 IS 元数据锁定的原因是，存在从 user 到 的外键引用帐户。您将插入通过外键引用 account 表的行，因此在事务完成之前不得更改或删除 account 表。