Is REPEATABLE READ the right choice for validation before modifying data?

Question

In my database I have a Task table. This table has a number of fields that are foreign keys to other tables. There are also a few link tables, that have Task on one end.

When a new task is created (or an old task is updated) I need to make sure that these foreign keys exists, and return a meaningful error if they do not. This is important because the call originates from a web api, so consumers really need to know why their call did not work, so a detailed error message (without giving out too much) is crucial.

Since there are a dozen of foreign keys (and also some other conditions, like current user application account being active) that make task correct, instead of trying to insert and failing I'm trying to validate these before I insert.

Of course when I do this I need to make sure that when I actually insert the record these validations still hold true.

So let's assume a task references a group. From the web api perspective group is a reference data, and it returns a list of valid group keys in a prior call. It receives one of them back when it creates or updates a task as a GroupId field on the Task object.

To make sure that this (and other) foreign keys are present in the database I open a REPEATABLE READ transaction and check them one by one for existence before I attempt to insert the task. I then compile a list of those that are absent (if any) and I roll back the transaction and return these back so that the API can form a meaningful error message.

The question is if REPEATABLE READ isolation level appropriate in this case. Paul White mentioned that often it is not .

Answer 1

To make sure that this (and other) foreign keys are present in the database I open a REPEATABLE READ transaction and check them one by one for existence before I attempt to insert the task. I then compile a list of those that are absent (if any) and I roll back the transaction and return these back so that the API can form a meaningful error message.

REPEATABLE READ is probably the right isolation level to use in this case, assuming the data you are checking does not change very often. This isolation level guarantees that rows actually read earlier in the transaction will continue to exist, unchanged, until the transaction completes by taking and holding shared locks.

A transaction running at this isolation level may miss rows that existed at the start of the transaction, but which moved during the transaction in such a way that they moved from in front to behind the seek or scan trying to locate them. It might also encounter new rows on a second run of the same query that did not exist at the beginning (phantoms).

So, in rare cases, your transaction might fail to approve an insert that would succeed if tried. It would not result in a validated insert subsequently failing, which is probably your primary concern.

If you need a more complete guarantees of isolation, consider SERIALIZABLE - but ensure the proper indexes exist to support minimal key-range locks.

Using SNAPSHOT isolation (as I mentioned on your previous question) would provide a consistent view of the data (as it appeared at the time the transaction started), but you might encounter an update conflict when trying to insert the 'validated' entry, if any concurrent modifications result in the foreign key checks failing. This would not provide the level of detail you need for a meaningful error message, so SNAPSHOT is not the right choice here.

For more information see:

The Repeatable Read Isolation Level
The Serializable Isolation Level
The ACID properties of statements and transactions