How to prevent Race Condition on SQL Server?

Question

I have tried using transactions and also setting the transaction isolation level to serializable but I still have the same issue.

Here is the way to reproduce my problem:

CREATE PROCEDURE dbo.TestRaceCondition
AS
    DROP TABLE IF EXISTS dbo.TABLE_A;

    WAITFOR DELAY '00:00:01';

    SELECT 1 ID INTO dbo.TABLE_A;
GO

Copy and paste the following code multiple times (I do 20 times) in 3 different sessions

EXEC dbo.TestRaceCondition;
GO

Then try to execute the 3 sessions at the same time.

This is the error I get in at least one of the sessions:

Msg 2714, Level 16, State 6, Procedure dbo.TestRaceCondition, Line 46
There is already an object named 'TABLE_A' in the database.

Hopefully someone can help me, thank you in advance!

I have tried using transactions

CREATE PROCEDURE dbo.TestRaceCondition
AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRAN
        DROP TABLE IF EXISTS dbo.TABLE_A;

        WAITFOR DELAY '00:00:01';

        SELECT 1 ID INTO dbo.TABLE_A;
    COMMIT TRAN

I expect running the procedure multiple times with no error.

Answer 1

It seems DROP TABLE IF EXISTS only applies a Sch-S lock temporarily, and if the table needs to be dropped only then does it upgrade to a Sch-M lock.

While there are many ways to force an Sch-M lock, a more idiomatic way to do this might be to use sp_getapplock, which can create an arbitrary application-defined lock.

CREATE PROCEDURE dbo.TestRaceCondition
AS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRAN;

EXEC sp_getapplock @Resource = N'TestRaceCondition', @LockMode = 'Update';

DROP TABLE IF EXISTS dbo.TABLE_A;

WAITFOR DELAY '00:00:01';

SELECT 1 ID INTO dbo.TABLE_A;

EXEC sp_releaseapplock @Resource = N'TestRaceCondition';

COMMIT TRAN;

I would maintain though, that if you are dropping and re-creating the same table continuously then you are probably doing something wrong.