0

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.

1 Answer 1

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.

3
  • I appreciate the answer. My last consideration I suppose would be that this database is planning to be put in a SQL Server cluster with 40+ other databases of which I don't know what their contents at all. Since mine does ` IF OBJECT_ID('tempdb..##counterTable') IS NOT NULL DROP TABLE ##counterTable` at the top of every SP, I could be stepping on other databases toes by dropping the table and then locking it correct (or they could do it to this database as well if they did the same sort of logic and used the same name for their global tables) right? Jan 27, 2023 at 13:07
  • All these stored procedures are just meant to return a dataset to a specific client or run inserts on behalf of one client, so based on what you said it does sound like Local Tables would be best practices here? Jan 27, 2023 at 13:08
  • 1
    Yes. Sounds like local temp tables would be much better. And yes, blocking would be worse with more databases using the same global temp table names. Jan 27, 2023 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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