GOOD TIMING SYNONYM CODE

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. It may often work without anyone knowing, but it can indeed cause you problems. View this like you would view dropping and recreating tables in a running system. 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. If a connection resets the SYNONYM it is changing that synonym for the Server and database not for the connection. So, yes I would consider that risky unless you have tight controls on when those steps can run. Whenever you create or drop a SYNONYM you are changing the definition of your database landscape. 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. 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. 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). 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. 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 will block other callers for the same reason above if the remote caller acquires the lock first.

GOOD TIMING SYNONYM CODE

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. Since most of the time the local version of the synonym would be used, create it permanently in the database with the local definition. If concurrent access is a requirement, the design could be changed in the following way: Therefore, this design could never allow concurrent operations in the first place. 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. This will also inherently prevent errors related to the synonym object already existing. 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.

Good timing synonym