SQL Server – Query Data Before a Commit Transaction

sql serverssmstransaction

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.

Best Answer

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;

Related Solutions

Sql-server – Transaction level difference between using a large IN filter VS. “BEGIN TRAN/COMMIT”

From a logging point of view 1 and 2 will be about the same as in both cases you are doing all the deletes within a single transaction. There will be lots of locking and probably blocking while the delete is being run. #3 is a single transaction per batch, so users won't be impacted very much and you'll have lots of small transactions in the transaction log instead of one large one. #2 and #3 should take about the same amount of time to run. #1 should take less time than #2 and #3 because it's just one command, however the run time will probably still cause problems.

I'd probably want to do something like this. This will minimize the locking and blocking that needs to happen by only dealing with 1000 rows at a time.

SELECT NULL
WHILE @@ROWCOUNT <> 0
BEGIN
  DELETE TOP (1000) FROM dbo.x
  WHERE ID IN ( /* if possible use "BETWEEN 1 and 100000" instead*/
  1
  ,2
  ,3
  ,4
  ...
  ,100000
  )
END

Sql-server – Error on DDL statement never invokes CATCH

Why isn't it caught? Because TRY / CATCH doesn't catch all types of errors. But you can get it to catch the non-system-critical stuff by wrapping it in an EXEC(), such as:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY

The error in the ALTER will fail out to the EXEC, which in turn will return to the TRY / CATCH block reporting a simple, catchable error.

For more detail, the MSDN page for TRY...CATCH states:

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

Compile errors, such as syntax errors, that prevent a batch from running.

Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

That second bullet point, about object name resolution errors, is what is causing the ALTER statement to fail and not be eligible to get handled by the CATCH block.

EDIT:
Regarding your statement about "DDL statements are implicit COMMIT": I am not sure what you mean by this exactly, but all (well, nearly all) queries are transactions by themselves. Meaning, single-query batches have no use for explicit BEGIN TRAN / COMMIT / ROLLBACK. If the statement fails it automatically rolls back, and if it succeeds then it automatically commits. You would only use the BEGIN TRAN / COMMIT / ROLLBACK construct if you want to conditionally rollback for some reason. It is still a good idea to use the TRY...CATCH structure to properly handle errors, but you gain nothing (at least with the code posted in the question) with the explicit transaction handling. There is absolutely no difference between the posted code and the following:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY
BEGIN CATCH
   SELECT 'I''m in Catch';
END CATCH;

Best Answer

Related Solutions

Sql-server – Transaction level difference between using a large IN filter VS. “BEGIN TRAN/COMMIT”

Sql-server – Error on DDL statement never invokes CATCH

Related Question