mysql - 通过迁移添加外键时，锁定了哪些表？

Question

我正在向现有的 MYSQL 表添加一个外键。可以说我有一张表销售和一张表产品。我将 FK 添加到我的销售表中:

ALTER TABLE sales ADD CONSTRAINT sales_product_item_fk FOREIGN KEY (product_id)  REFERENCES products(id) ON DELETE RESTRICT  ON UPDATE RESTRICT

在运行时，哪些表将被锁定？销售、产品，两者兼而有之？

(根据下面的评论编辑以指定 MYSQL)

Answer 1

两个表都将被一个 SHARED 锁锁定。这允许您对表运行 SELECT，并允许您执行其他需要共享锁的操作，但不允许您执行任何需要独占锁的操作，例如 UPDATE。

你可以确认一下:创建sales和products这两个表，并用几百万行填充sales表，这样就足够了执行该 ALTER TABLE 至少需要几秒钟。 product_id 的值在此测试表中的每一行都可以相同。

mysql> select * from sales limit 3;
+----+------------+
| id | product_id |
+----+------------+
|  1 |          1 |
|  2 |          1 |
|  3 |          1 |
...

并确保 products 表中有一行满足外键。

mysql> select * from products;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | some name |
+----+-----------+

现在打开两个窗口并准备好对每个窗口中的表运行查询。

运行 ALTER TABLE 添加外键。如果外键没有索引，这将建立一个新索引。建立索引是最耗时的。事实上，即使添加一个没有外键的索引也会表现出相同的锁定行为。

mysql> ALTER TABLE sales ADD CONSTRAINT sales_product_item_fk FOREIGN KEY (product_id)
  REFERENCES products(id) ON DELETE RESTRICT  ON UPDATE RESTRICT;

这会在构建索引时等待。在我的笔记本电脑上，有 800 万行，需要 30 多秒。有充足的时间在您的第二个窗口中进行查询。

mysql> update sales set product_id = null limit 2;

这个 block ，等待获取对 sales 中某些行的锁定。你可能预料到了这一点。建立索引时无法更新表。

mysql> update products set name = 'othername';

这也会阻塞，等待 products 中行的独占锁。当表上有共享锁时，它无法获得排他锁。

你能用 LOCK=NONE 子句解除对锁的要求吗？

mysql> ALTER TABLE sales ADD CONSTRAINT sales_product_item_fk FOREIGN KEY (product_id)
  REFERENCES products(id) ON DELETE RESTRICT  ON UPDATE RESTRICT, LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try LOCK=SHARED.

没有。更改锁定级别受您请求的更改类型的限制。