CODEWITHSUNDEEP
mysqlsqlforeign-keys
Columbus
Columbus

Reputation: 153

When adding a foreign key via a migration, what tables are locked?

I am adding a foreign key to an existing MYSQL table. Lets say I have a table sales and a table products. I add a FK to my sales table:

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

While this is running, what tables will be locked? sales, products, both?

(Edited to specify MYSQL as per comment below)

Upvotes: 4

Views: 3720

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562701

Both tables will be locked with a SHARED lock. This allows you to run SELECT against the tables, and it allows you to do other operations that need a SHARED lock, but it does not allow you to do anything that requires an EXCLUSIVE lock, such as UPDATE.

You can confirm this: Create the two tables sales and products, and fill the sales table with a few million rows, enough so that it takes at least a few seconds to do that ALTER TABLE. The value of product_id can be the same on every row in this test table.

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

And make sure there's a row in the products table to satisfy the foreign key.

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

Now open two windows and be ready to run queries against the tables in each window.

Run the ALTER TABLE to add the foreign key. This will build a new index if there's no index already for the foreign key. Building the index is what takes the most time. In fact, even adding an index without a foreign key will exhibit the same locking behavior.

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

This waits while it's building the index. On my laptop, with 8 million rows, it takes over 30 seconds. Plenty of time to do queries in your second window.

mysql> update sales set product_id = null limit 2;

This blocks, waiting to acquire a lock on some rows in sales. You probably expected this. You can't update a table while there's an index building.

mysql> update products set name = 'othername';

This also blocks, waiting for an exclusive lock on the row in products. It can't get an exclusive lock while there's a shared lock on the table.

Can you lift the requirement for the lock with the LOCK=NONE clause?

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.

Nope. Changing the lock level is limited by the type of alteration you're requesting.

Upvotes: 4

Related Questions