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?