Query hangs out

Question

This code is working:

CREATE SYNONYM S FOR [ServerIP].[MyDatabase].[dbo].[MyTable]
SELECT * FROM S
DROP SYNONYM S

And this one is not working.

BEGIN TRAN              
    CREATE SYNONYM S FOR [ServerIP].[MyDatabase].[dbo].[MyTable]
    SELECT * FROM S
    DROP SYNONYM S
COMMIT TRAN

I get exclusive locks on executing SELECT statement (request_mode is X), with resource_type = KEY on sysschobjs and sysobjvalues.

Can anyone explains why it hangs out?

P.S. The sample is just a simplification of a more complicated code, where the outer transaction is invoked in a caller SP.

Answer 1

I think the problem might be that synonym is accessible out of the scope of transaction (and current connection, too). Try to run two sets of code in different tabs in SSMS.

What will happen, is that the second query in another tab will wait 10 seconds until the first one completes:

-- first tab:
    BEGIN TRAN              
        CREATE SYNONYM S FOR [ServerIP].[MyDatabase].[dbo].[MyTable]
        waitfor delay '00:00:10'
        SELECT * FROM S
        DROP SYNONYM S
    COMMIT TRAN

-- second tab:
BEGIN TRAN              
    CREATE SYNONYM S FOR [ServerIP].[MyDatabase].[dbo].[MyTable]
    SELECT * FROM S
    DROP SYNONYM S
COMMIT TRAN

When you run these queries, the second will fail on CREATE statement, but then will return rows from select, and will not be waiting for the transaction to commit:

-- first tab:
CREATE SYNONYM S FOR [ServerIP].[MyDatabase].[dbo].[MyTable]
waitfor delay '00:00:10'
SELECT * FROM S
DROP SYNONYM S

--second tab:
CREATE SYNONYM S FOR [ServerIP].[MyDatabase].[dbo].[MyTable]
SELECT * FROM S
DROP SYNONYM S

Possible solutions to prevent multiple sessions trying to drop and create the same synonym:

1. Don't use synonyms :)
2. Check if the synonym exists first (altough there's no guarantee that synonym with that particular name is pointing to the right table): if not exists(select * from sys.synonyms where name = 's') create synonym s for [MyDatabase].[dbo].[MyTable]
3. Create a permanent synonym that will be used by every instance of the procedure