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

if (@isRemote = 1) 
-- do some complex work on SY_SUBJECTS

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?

  • How certain are you that every connection will be @is_remote = 1? What do you see as the consequences if they are not? – RLF Jun 3 '14 at 15:14
  • 3
    There may be a more robust way to handle this, depending on what "complex work" entails. If, for example, you just read from the remote table once you could use a temp table. If you're using dynamic SQL heavily you could create a single-purpose synonym with a unique name, and insert it in your subsequent code. – Jon of All Trades Jun 3 '14 at 15:49
  • Hello RLF. The @Remote=1 case only happens after-hours so is OK to be less-safe. The 99% case is "CREATE SYNONYM SY_SUBJECTS FOR [SUBJECTS]". – RaoulRubin Jun 3 '14 at 17:04

The code is already thread-safe because CREATE SYNONYM takes a SCH-M (schema-modification) lock, which blocks others' attempts to create the same synonym, and they will wait until the lock owner's transaction completes.

This will also inherently prevent errors related to the synonym object already existing. In fact, even checking for existence of the synonym (without using NOLOCK/READ UNCOMMITTED) from another thread will also block until the lock is released.

Therefore, this design could never allow concurrent operations in the first place. If concurrent access is a requirement, the design could be changed in the following way:

Since most of the time the local version of the synonym would be used, create it permanently in the database with the local definition. When the remote version is needed, the code can (within a transaction) drop and recreate the synonym with the remote definition, do its work, then drop and recreate with the local definition to return the state to where it was when the transaction started.

This will block other callers for the same reason above if the remote caller acquires the lock first. If a local caller is using the synonym, it acquires and potentially holds a SCH-S (schema-stability) lock, which will prevent the synonym from being dropped, and therefore block a remote caller for as long as the lock is held. This sets up a situation where a local caller could be in process and wait in the middle while a remote caller does its work.

I'm a little wary of allowing that because it makes things a little unpredictable (i.e., if you're recording timing metrics of the process, it could be all over the place). To solve that, we can use sp_getapplock and request a Shared lock for a local caller, and an Exclusive lock for a remote caller. This will allow concurrent local callers (which will wait if a remote caller is running), and a remote caller will wait for all local callers to complete before running.

  • +1 but I'm not sure what sp_getapplock buys you over just raising the isolation level of the existing transaction to serializable. – Aaron Bertrand Jun 3 '14 at 23:19
  • @Aaron: Your comment prompted me to actually try the example... and we were both fooled by RLF's erroneous answer. I updated my answer. – Jon Seigel Jun 4 '14 at 2:24
  • A very thorough answer. Thanks for digging deeper into it. – RaoulRubin Jun 4 '14 at 2:40
  • @RaoulRubin: You're welcome. – Jon Seigel Jun 4 '14 at 13:20
  • @JonSeigel You mention dropping and recreating a synonym within a transaction. I have the exact problem that the transaction does not prevent other queries to hit the synonym while it's dropped. Can you help? stackoverflow.com/questions/38474025/… – Niels Brinch Jul 20 '16 at 7:13

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.

  • Thanks for the great answer. It seems like the transactions will block each-other from doing any real damage. It will cause some potential locking and waiting. – RaoulRubin Jun 3 '14 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.