How does a serializable transaction level affect global temporary tables in SQL Server? Creating table affects it?

Question

I have a knowledge gap in SQL Server 2019 and if someone could explain it to me how the following works appreciate it. This will be a long post as I want to explain the full context as best I can without posting the real code.

I thought that I had found a design issue with some global temporary tables in stored procedures that write to databases where if two users ran the same process at the same time, then they could intermingle. My coworker disagreed and said because since this was all executed in a serializeable transaction there actually is nothing to worry about. I wanted to run an experiment to make myself feel more assured.

I wrote the following two procedures that essentially mimic our real one - creating a global temporary table from scratch with a exec sp_executesql 'SELECT INTO' statement, and the at the end selecting it, though in our real production stored procedures we insert this global temporary table into the database.

I started with these two - one that inserts even into a global temporary table and another that inserts odds

CREATE PROCEDURE [dbo].[testGlobalTableEvens] 
AS
BEGIN
    LINENO 0     
    SET NOCOUNT ON;
    IF OBJECT_ID('tempdb..##counterTable') IS NOT NULL DROP TABLE ##counterTable
    DECLARE @cnt INT = 0;
    DECLARE @query NVARCHAR(MAX);
    WHILE @cnt < 500000
    BEGIN
        IF @cnt = 0
            BEGIN
                SET @query = N'SELECT ' + cast(@cnt as VARCHAR(10)) + ' as col INTO ##counterTable'
                print @query
                exec sp_executesql @query
            END
        ELSE
            BEGIN
                IF @cnt % 2 = 0
                BEGIN
                    SET @query = N'INSERT INTO ##counterTable(col) VALUES (' + cast(@cnt as VARCHAR(10)) + ')' 
                    print @query
                    exec sp_executesql @query
                END
            END
        SET @cnt = @cnt + 1;
    END
    SELECT * FROM ##counterTable;
END

CREATE  PROCEDURE [dbo].[testGlobalTableOdds] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    IF OBJECT_ID('tempdb..##counterTable') IS NOT NULL DROP TABLE ##counterTable
    -- Insert statements for procedure here
    DECLARE @cnt INT = 0;
    DECLARE @query NVARCHAR(max);
    WHILE @cnt < 500000
    BEGIN
        IF @cnt = 1
            BEGIN
                SET @query = N'SELECT ' + cast(@cnt as VARCHAR(10)) + ' as col INTO ##counterTable'
                print @query
                exec sp_executesql @query
            END
        ELSE
            BEGIN
                IF @cnt % 2 = 1
                BEGIN
                    SET @query = N'INSERT INTO ##counterTable(col) VALUES (' + cast(@cnt as VARCHAR(10)) + ')' 
                    print @query
                    exec sp_executesql @query
                END
            END
        SET @cnt = @cnt + 1;
    END
    SELECT * FROM ##counterTable ORDER BY col;
END

Running these separately provide 250,000 results, all the odds or all the evens. Running them at the same time and I get more than 250,000 in each, as they are sharing the table, which is what I was expected.

Additionally, if I only run one of these and run a DROP TABLE ##counterTable in the middle, it messes the whole operation up.

In production, we really call the stored procedures like this within a transaction (with different error handling I just wanted to get something down) -

CREATE PROCEDURE [dbo].[globalTestEvens_Caller] 
    -- Add the parameters for the stored procedure here
AS
BEGIN

    BEGIN TRY
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
        BEGIN TRANSACTION
        execute dbo.testGlobalTableEvens
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        print cast(ERROR_LINE() AS varchar(max))
        print ERROR_MESSAGE()
    END CATCH
END

CREATE PROCEDURE [dbo].[globalTestOdds_Caller] 
    -- Add the parameters for the stored procedure here
AS
BEGIN

    BEGIN TRY
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
        BEGIN TRANSACTION
        execute dbo.testGlobalTableOdds
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        print cast(ERROR_LINE() AS varchar(max))
        print ERROR_MESSAGE()
    END CATCH
END

Calling from these _caller stored procedures at the same time and I get 250,000 results each, odds and evens completely separated. Running a SELECT * FROM ##counterTable after the fact shows me whatever procedures table finished last. Running a DROP TABLE ##counterTable does nothing.

Is this safe? Is this an okay practice? How exactly is this working, these tables are both global, but also local to the transaction they are in? If someone can explain this and if this is a safe way to do things, that would put my mind at ease. This database to be migrated to a SQL Cluster of many SQL Server databases and I feared one of them having a temporary table of the same name or clearing the tempdb table, but it appears that can't happen anyways - is there any concern with this sort of logic being in a SQL Cluster ie any other database being able to interfere?

Last bit context, our UI Application connects to the database as a single user and I believe has very long lived sessions.

Answer 1

It's "safe" because DDL is transactional in SQL Server, and any attempt to reference a global temporary table that was created in an uncommitted transaction in another session will block. So you're sacrificing concurrency by using global temp tables.

But you should rarely use global temp tables. Unless

1. You're creating the temp table in a stored procedure or nested batch and need it to be visible in an outer batch or subsequent query on the same session.

2. You need the data to be visible to another session.

If you are loading a temp table and then returning its contents to the client in a single stored procedure call, just use a local temp table. It will automatically be destroyed after the stored procedure executes, so you don't have to drop it or check if it exists.

And for scenario 1) above you can create a local temp table first, then call a procedure that uses it. They are visible in all nested batches.