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

you can test this out in management studio:

1) open a new window A, run this:

CREATE TABLE Parent (id int NOT NULL primary key);

2) open another new window B, run this:

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

3) go back to window A, run this:

select * from parent

it is blocked and runs and runs..

4) open a new window C, run this:

sp_lock

you can see the DDL lock and the X and IX locks on the table that are issued when it tries to create the FK

Answer 2

Could it be that the first transaction is still open thus keeping a lock on the table. (MSDN SQL Locking Hints)

For your SELECT statements thats locking you might want to try using the WITH (NOLOCK) table hint.

e.g.

SELECT * FROM Parent WITH (NOLOCK)

Answer 3

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 ParentId 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 isnt the lock granuality required for SQL server to differentiate between queries that it can safely run and queries that it cant - after all schema changes arent something that happens vry frequently and so there isnt really a need to optimise these sorts of things to that degree.