Reading from table being created in transaction

Question

I would like to view data in a table that is undergoing a transaction. I will use this super simple temporary table as an example:

SELECT 5 AS NUMBER INTO ##temptable

I will create a transaction to insert a new row with the number 10:

BEGIN TRAN;
    INSERT INTO ##temptable 
    SELECT 10 AS Number
    WAITFOR DELAY '00:00:20';
COMMIT TRAN;

Now, if I open a new query, and do a regular select statement, I will not get any results, the wheel will be spinning until the transaction has been committed. I can instead set the isolation level to read committed:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

I would expect this to return the committed number 5 but not the uncomitted number 10, but again I just get a spinning wheel. I guess that is not how it works.

If I set the isolation level to read uncommited:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This successfully returns both numbers from the table.

I still have a problem though, because I want to create the table inside of the transaction like so:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN;
    SELECT 5 AS NUMBER INTO ##temptable

    INSERT INTO ##temptable 
    SELECT 10 AS Number
    WAITFOR DELAY '00:00:20';
COMMIT TRAN;

In this case I once again get a spinning wheel if I try to read from the table, even with isolation level set to read uncommitted.

Is there any way to read from a table that is being created within an ongoing transaction?

Answer 1

No, I'm afraid not. Creating a table requires a schema modification lock, which isn't with compatible anything, not even a schema stability lock which is used when doing read uncommitted.

You most likely have to take a step back and re-think your approach. I.e.,create the table before the transaction.