Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code.

BEGIN TRANSACTION
if (@isRemote = 1) 
    BEGIN
       CREATE SYNONYM SY_SUBJECTS FOR [MT00011].[DBO].[SUBJECTS];
    END
ELSE
    BEGIN
       CREATE SYNONYM SY_SUBJECTS FOR [SUBJECTS];
    END
-- do some complex work on SY_SUBJECTS
DROP SYNONYM SY_SUBJECTS
COMMIT

This code gets called by multiple requests simultaneously. I am concerned that it will cause problems if this code gets called at the same time. It executes 99% of the time on a single SQL database, and the "remote" case is run after hours.

Is there a problem here?

Whenever you create or drop a SYNONYM you are changing the definition of your database landscape. So, yes I would consider that risky unless you have tight controls on when those steps can run.

If a connection resets the SYNONYM it is changing that synonym for the Server and database not for the connection. This means that a set of "complex work" running in another process could wind up switching from your remote data (for example) to the local data without any warning. That would leave a mess to clean up.

View this like you would view dropping and recreating tables in a running system. It may often work without anyone knowing, but it can indeed cause you problems.

Of course, if another connection tries to define a SYNONYM that already exists it will raise an error such as: "There is already an object named 'SY_SUBJECTS' in the database." This will save you from switching context (but you must deal with the error) until after the synonym is dropped. (There is no ALTER SYNONYM function, which would apparently be much more dangerous.)

Therefore if you try to change the SYNONYM without dropping it first, it will fail.

If a code path runs DROP SYNONYM it will succeed once it can acquire the needed locks. From MSDN: "References to synonyms are not schema-bound; therefore, you can drop a synonym at any time."

So, the DROP SYNONYM will not stop a running transaction, but will wait for the transaction to finish.

StackExchange Database Administrators Q#66470, answer score: 4