tsql - Alternate synonym in SQL Server in one transaction

Question

I am new to Transact SQL programming.

I have created a stored procedure that would drop and create an existing synonym so that it will point to another table. The stored procedure takes in 2 parameters:

• synonymName - an existing synonym
• nextTable - the table to be point at

This is the code snippet:

...
BEGIN TRAN SwitchTran
   SET @SqlCommand='drop synonym ' + @synonymName
   EXEC sp_executesql @SqlCommand
   SET @SqlCommand='create synonym ' + @synonymName + ' for ' + @nextTable
   EXEC sp_executesql @SqlCommand
COMMIT SwitchTran
...

We have an application that would write data using the synonym regularly.

My question is would I run into a race condition where the synonym is dropped, while the application try to write to the synonym?

If the above is a problem, could someone give me suggestion to the solution.

Thanks

Answer 1

Yes, you'd have a race condition.

One way to manage this is to have sp_getapplock after BEGIN TRAN in Transaction mode and trap/handle the return status as required. This will literally serialise (in the execution sense, not isolation) callers so only one SPID executes at any one time.