mysql - 添加外键时在mysql中死锁

Question

我们的数据库中有一个名为company_competitors 的表。有一项工作每天截断和加载该表。该表有两列 company_id 和 competitor_id 都引用另一个表 companies。

CREATE TABLE `company_competitors` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) DEFAULT NULL,
  `competitor_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_rails_company_id_c1ac450a` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_rails_competitor_id_772a45c6` FOREIGN KEY (`competitor_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=268477 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

截断和加载中包含的步骤是:

1. 创建一个新表company_competitors_new
2. 分别在两列(company_id、competitor_id)上添加外键约束。
3. 将数据加载到新表中。
4. 交换表 company_competitors 和 company_competitors_new。

查询:

“创建表 company_competitors_new LIKE company_competitors;

alter table company_competitors_new ADD CONSTRAINT fk_rails_company_id_53f8f57a foreign key (company_id) references companies(id);'

在添加外键时，其他人可能会访问公司表。因此，当第二个查询运行时，数据库陷入死锁，公司表上的任何查询都不会执行。

show full processlist 显示所有查询说 Waiting to acquire Metadata lock 我必须终止所有选择查询，以便完成添加外键。

我需要帮助来理解这里出现死锁的原因以及我该如何处理它。我也想听听是否有更好的方法来截断和加载 0 停机时间。

Answer 1

就我个人而言，如果很少发生死锁，我只是重新准备一个新事务并重新启动查询(这不是最想要的解决方案，但在这种情况下有效)。但是，如果您的死锁如您所提到的那样在系统上出现，您可以检查更多 使用 SHOW ENGINE INNODB STATUS 命令查看信息以查看最近死锁的一些详细信息。我的建议也是将创建 company_competitors_new 的 block 放在

之间

SET foreign_key_checks=0;

...(查询)...

SET foreign_key_checks=1;