Query data before a commit transaction

Question

My understanding is that, in a MS SQL Server Management Studio window, after doing a "begin transaction" and then making some changes like add data to a table , you can only query that table and those changes from the same window until you do a "commit transaction".

Is there any way to do a query from another source prior to doing the "commit transaction"?

Specific to my current goal and to add some context. I do some SQL queries from Excel Power Query. I'd really like to be able do do these queries prior to the "commit transaction" so that I can do some analysis and figure out if I should be doing a rollback instead of a commit.

Answer 1

Yes, it's possible if you change the transaction isolation level for the session (that's what you call "window" in SSMS) that queries modified data. Often this is not such a great idea, as you might get some unexpected results. Consider the side effects carefully. I have no idea if it's possible to change the transacion isolation level in the Excel Power Query.

For example, the following set of queries would insert some data and display the update correctly even without commit/rollback.

-- Session 1
begin tran tx_test;
-- Assume the Test table exists and insert is okay
insert dbo.Test(datadate, content) values (getdate(), 'transaction');
select * from Test; -- Shows the new data
-- After select, one would execute one of the following
-- commit;
-- rollback;

Meanwhile, the second session executes a select that doesn't seem to do anything:

-- Session 2
-- This waits for uncommitted transaction
-- and returns results after 1st session commits/rollbacks
select * from Test;

Create a third session and alter its isolation level:

-- Session 3
set transaction isolation level read uncommitted;
-- This reads the inserted data from the 1st session, even before commit
select * from Test;

Answer 2

As a matter of best practices, transactions should be kept as short as possible and never wait for user interaction; every time you perform some type of data or schema modification within a transaction, this places locks on the objects or rows that have been touched/modified, which keeps other users' queries waiting. This is turn can create chain effects that can bring your database server to a standstill.

In the scenario you're describing, I would instead recommend you to make a copy of the data to separate "what-if" tables where you can make your modifications and review the results. Once happy with the results, use a transaction to merge the data of this table back into the original table(s).