Is it best practice to use surrogate keys when creating foreign key constraints in SQL Server?

Question

I'm trying my best not to ask a leading question, but after a lot of searching I'm unable to find a similar answer or recommendation.

First, take a look at this question I asked on Stack Overflow last week: Why does inserting a row with a foreign key referencing a row by pk modified in another snapshot isolation transaction cause the transaction to hang?

In our database we have 4 tables. Two has a foreign key to One's primary clustered key. Four has a foreign key to Three via a surrogate key. In all cases, the keys are meaningless.

Imagine two scenarios:

In a snapshot transaction where a column is modified in One, another snapshot transaction begins where a row is simply inserted into Two, where the foreign key column has the value of the row being modified in One. The latter transaction will block until the former transaction is committed, and then the latter transaction will abort with a snapshot isolation error.

In a very similar situation, a snapshot transaction begins where a column is modified in Three, another snapshot transaction begins where a row is simply inserted into Four, where the foreign key column has the value of the row being modified in Three (via its surrogate key). The latter transaction will finish without issue before the former transaction is committed.

Despite the answer being rather simple, this is unintuitive to me. I almost want to consider this a bug in SQL Server. The first scenario's transaction doesn't have a legitimate reason to block, and it especially doesn't have a legitimate reason to roll back the transaction. It does so basically because of an implementation detail. But if you just use a surrogate foreign key, everything works as expected and nothing is blocked or conflicts.

Is it best practice to use surrogate keys when creating foreign key constraints in SQL Server?

Answer 1

Despite the answer being rather simple, this is unintuitive to me. I almost want to consider this a bug in SQL Server. The first scenario's transaction doesn't have a legitimate reason to block, and it especially doesn't have a legitimate reason to roll back the transaction. It does so basically because of an implementation detail. But if you just use a surrogate foreign key, everything works as expected and nothing is blocked or conflicts.

It's not the surrogate key aspect that is important, it is whether a key-only nonclustered index is used for the foreign key validation, or the key-plus-data clustered index.

The SQL Server implementation always takes a shared lock when validating foreign keys on the parent side, to ensure the most recent committed data is read, and stays that way until that row's update is complete.

Under RCSI, this can lead to unexpected blocking, as you show. Under SI (snapshot isolation) it can result in an arguably-unnecessary update conflict error being reported.

In this case, RCSI blocks but continues after the blocking transaction commits or rolls back. Under SI, the blocked transaction aborts with an update conflict if the blocking transaction commits (but succeeds if it rolls back).

The index used to check a foreign key need not be the primary key, it can be any key for the relation. You may choose to enforce that key with a nonclustered or clustered unique index for a variety of reasons. It is not uncommon for the primary key to be nonclustered, and another candidate key to be clustered.

Snapshot isolation is most appropriate for read-mostly workloads, where update conflicts (real or imagined) will likely be rare.

More information in two of my articles:

• Foreign Keys, Blocking, and Update Conflicts (this specific issue)
• The SNAPSHOT Isolation Level (other surprises)