Why doesn’t this work?
I believe the default behaviour of SQL Server is to release shared locks as soon as they are no longer needed. Your sub-query will result in a short-lived shared (S) lock on the table, which will be released as soon as the sub-query completes.
At this point there is nothing to prevent a concurrent transaction from inserting the very row you just verified was not present.
What modification do I need to make so there is no chance of an exception due to the constraint violation?
Adding the HOLDLOCK hint to your sub-query will instruct SQL Server to hold on to the lock until the transaction is completed. (In your case, this is an implicit transaction.) The HOLDLOCK hint is equivalent to the SERIALIZABLE hint, which itself is equivalent to the serializable transaction isolation level which you refer in your list of “other approaches”.
The HOLDLOCK hint alone would be sufficient to retain the S lock and prevent a concurrent transaction from inserting the row you are guarding against. However, you will likely find your unique key violation error replaced by deadlocks, occurring at the same frequency.
If you’re retaining only an S lock on the table, consider a race between two concurrent attempts to insert the same row, proceeding in lockstep — both succeed in acquiring an S lock on the table, but neither can succeed in acquiring the Exclusive (X) lock required to execute the insert.
Luckily there is another lock type for this exact scenario, called the Update (U) lock. The U lock is identical to an S lock with the following difference: whilst multiple S locks can be held simultaneously on the same resource, only one U lock may be held at a time. (Said another way, whilst S locks are compatible with each other (i.e. can coexist without conflict), U locks are not compatible with each other, but can coexist alongside S locks; and further along the spectrum, Exclusive (X) locks are not compatible with either S or U locks)
You can upgrade the implicit S lock on your sub-query to a U lock using the UPDLOCK hint.
Two concurrent attempts to insert the same row in the table will now be serialized at the initial select statement, since this acquires (and holds) a U lock, which is not compatible with another U lock from the concurrent insertion attempt.
NULL values
A separate problem may arise from the fact that FieldC allows NULL values.
If ANSI_NULLS is on (default) then the equality check FieldC=NULL would return false, even in the case where FieldC is NULL (you must use the IS NULL operator to check for null when ANSI_NULLS is on). Since FieldC is nullable, your duplicate check will not work when inserting a NULL value.
To correctly deal with nulls you will need to modify your EXISTS sub-query to use the IS NULL operator rather than = when a value of NULL is being inserted. (Or you can change the table to disallow NULLs in all the concerned columns.)
SQL Server Books Online References