Reading from table being created in transaction



  • 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?



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2