Reading from table being created in transaction

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply