2

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.

Database diagram with four tables

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?

  • The difference between the 2 scenarios (as explained in the answer you link) is not that the foreign key references the pk or another key. It's that in the first scenario, the referenced (pk) is the clustered index of One while in the 2nd the referenced (surrogateId) is not in the clustered index. – ypercubeᵀᴹ Nov 14 '18 at 16:07
  • @ypercubeᵀᴹ Would you ever recommend having the PK not be the clustered key? – Sam Rueby Nov 14 '18 at 16:09
  • 1
    The applicable best-practice is not to use SNAPSHOT isolation unless you have a specific need for it. – David Browne - Microsoft Nov 14 '18 at 16:10
  • And the final question ("Is it best practice to use surrogate keys ...?") doesn't make much sense - to me anyway. Both Id and SurrogateId look like surrogate keys to me. – ypercubeᵀᴹ Nov 14 '18 at 16:10
  • 2
    Read Committed blocks, of course, but after the row is committed by the other session it succeeds, unlike SNAPSHOT. The only two isolation levels I would use for new OLTP/mixed workloads are Read Committed (with RCSI) and SNAPSHOT. But SNAPSHOT mostly for read-only scenarios. – David Browne - Microsoft Nov 14 '18 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.