2

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?

1
  • Why do you need a global temp table anyway? A local temp table (with a single #) will be completely separated from any other sessions May 29 at 2:39

1 Answer 1

6

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.

3
  • Thank you, but what a shame. The temporary table is only ment as a staging table to prepare some data, and should be dropped after being used. I don't want to the run the risk of it not getting disposed properly. I will go back to the drawing board.
    – Mads
    May 27 at 10:29
  • The temp table is dropped at end of connection/session, if that helps. Or, if possible, throw this in a stored procedure. A temp table created in a proc will disappear at end-of-proc. May 27 at 11:35
  • Thank you, what I ment was, I don't want to create a persistent table, because it might not get disposed properly. It will be too complex to explain the remaining problem in the comments, I will open a new question instead.
    – Mads
    May 27 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.