How is a T-SQL transaction not thread-safe?

Question

The following (sanitized) code sometimes produces these errors:

Cannot drop the table 'database.dbo.Table', because it does not exist or you do not have permission.
There is already an object named 'Table' in the database.

begin transaction  
    if exists (select 1 from database.Sys.Tables where name ='Table') 
        begin drop table database.dbo.Table end 

    Select top 3000 *
    into database.dbo.Table
    from OtherTable
commit

select * from database.dbo.Table

The code can be run multiple times simultaneously. Anyone know why it breaks?

Answer 1

Can I ask why your doing this first? You should really consider using temporary tables or come up with another solution.

I'm not positive that DDL statments behave the sameway in transactions as DML statements and have seen a blog post with a weird behavior and creating stored procedures within a DDL.

Asside from that you might want to verify your transaction isolation level and set it to Serialized.

Edit

Based on a quick test, I ran the same sql in two different connections, and when I created the table but didn't commit the transaction, the second transaction blocked. So it looks like this should work. I would still caution against this type of design.

Answer 2

If you are just using this table during this process I would suggest using a temp table or , depending on how much data , a ram table. I use ram tables frequently to avoid any transaction costs and save on disk activity.

Answer 3

In what part of the code are you preventing multiple accesses to this resource?

begin transaction  
    if exists (select 1 from database.Sys.Tables where name ='Table') 
        begin drop table database.dbo.Table end 

    Select top 3000 *
    into database.dbo.Table
    from OtherTable
commit

Begin transaction isn't doing it. It's only setting up for a commit/rollback scenario on any rows added to tables.

The (if exists, drop) is a race condition, along with the re-creation of the table with (select..into). Mutiliple people dropping into that code all at once will most certainly cause all kinds of errors. Some creating tables that others have just destroyed, others dropping tables that don't exist anymore, and others dropping tables that some are busy inserting into. UGH!

Consider the temp table suggestions of others, or using an application lock to block others from entering this code at all if the critical resource is busy. Transactions on drop/create are not what you want.