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.