Why does creating a table with a foreign key constraint in one transaction block access to the referenced table in another?

Question

In one transaction I am creating a table which references another table. In a second transaction, I run a SELECT-query against the referenced table but it is blocked by the first transaction. Why is that?

Transaction A:

BEGIN TRAN
CREATE TABLE Child (id int NOT NULL,
                    parentId int NOT NULL REFERENCES Parent (id));

Transaction B:

BEGIN TRAN
SELECT * FROM Parent; -- This query is blocked

Answer 1

Looking at the locks in the activity monitor we can see the that first process (creating the table) holds a Sch-M lock on some object (it only tells us the object id, if we looked it up I expect it would be the Parent table). At the same time the second process (doing a select from the Parent table) is blocked while attempting to obtain a Sch-S lock on the same object.

Looking at the MSDN documentation we can see that these locks are Schema modification (Sch-M) and Schema stability (Sch-S) - the schema stability lock is needed by the second query to ensure that the schema of the obejct does not change during the execution of the query and the schema modification lock is held by anyone making a schema change to an object.

Why is the Sch-M lock obtained on the Parent table?

Because the execution plan of anyone modifying rows in the parent table has changed (in particular changing the id or deleting rows) - when rows are deleted from this table SQL server now needs to make sure that there were no records in the child table that had the same parentId as the one you are deleting.

Even though the execution plan of your select statement wont change, there isn't the lock granuality required for SQL server to differentiate between queries that it can safely run and queries that it can't - after all schema changes aren't something that happens very frequently and so there isn't really a need to optimise these sorts of things to that degree.