Insert if not exists avoiding race condition

Question

How can I make sure following statements don't have a race condition?

IF NOT EXISTS (select col1 from Table1 where SomeId=@SomeId)
INSERT INTO Table1 values (@SomeId,...)

IF NOT EXISTS (select col1 from Table2 where SomeId=@SomeId)
INSERT INTO Table2 values (@SomeId,...)

Is this enough

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

IF NOT EXISTS (SELECT col1 FROM Table1 WITH (UPDLOCK) WHERE SomeId=@SomeId)
INSERT INTO Table1 VALUES (@SomeId,...)

COMMIT TRAN

BEGIN TRAN

IF NOT EXISTS (SELECT col1 FROM Table2 WITH (UPDLOCK) WHERE SomeId=@SomeId)
INSERT INTO Table2 VALUES (@SomeId,...)

COMMIT TRAN

Answer 1

Yes. That is enough. Setting the transaction isolation level to serializable will create key locks that cover SomeId=@SomeId when you run your select-- which will prevent other processes from inserting values with the same key (SomeId=@SomeId) while your transaction is running.

The WITH(UPDLOCK) hint will cause the SELECT to obtain an update lock on the selected row(s), if they exist. This will prevent other transactions from modifying these rows (if they existed at the time of the select) while your transaction is running.

It doesn't look like you really need the WITH(UPDLOCK) hint, since you are committing the transaction right away if the record already exists. If you wanted to do something else before committing if the record does exist, you might need this hint-- but as it is, it appears you do not.