Isolation level issue

Question

I need some help in choosing the correct isolation level on my transactions.

My situation is as follows. I have a table with a unique constraint over two columns. For the sake of the argument, lets say the table is called Animal and the columns are Name and Owner. So every owner can only have one pet with a certain name.

My problem is that I'm receiving errors during inserts because they are coming in at such a phase that I'm getting exceptions triggered by the unique constraint. Inserts are happening by stored proc. At the start of the SP I do a select from Animal where Owner=x and Name=y to determine if the animal exists, if so, select the Id and move on, else insert the new animal. The rest of the proc uses the Id to insert/update Animal details in other tables.

I know I can set the isolation level to Serializable, but I'm hesitant to do so because I know that it can cause deadlocks and since I'm having to do loads of inserts rapidly, this is a very real threat.

Any advise will be welcomed, thanks.

Answer 1

Answer originally left by Dan Guzman in comments:

The method I usually use is an explicit transaction plus a UPDLOCK, HOLDLOCK locking hint on the SELECT query. That will help avoid deadlocks and avoid the race condition, without resorting to serializable isolation for the entire transaction.

Also SERIALIZABLE won't really help as the initial select will get shared (S) range locks. So both sessions will get the S lock, and then, instead of a PK violation, you'll get a deadlock.

With the UPDLOCK in addition to HOLDLOCK, only one session can select the same key regardless of whether it exists (holding update key lock) or not (holding update key range lock). This will serialize access to the specified key in this code path.

This worked for the OP, who implemented it as follows:

@Id = SELECT *
  FROM ANIMAL WITH(UPDLOCK, HOLDLOCK)
  WHERE Owner = x AND Name = y
IF(@id <> NULL)
  UPDATE ANIMAL
ELSE
  INSERT ANIMAL

--DO OTHER STUFF