Sql-server – SQL Transaction – use values before commit

sql serversql server 2014transaction

So I am creating a stored procedure in SQL2014. Towards the start it has:
Begin Transaction

Then it does an insert into a table, which has an auto-incrementing primary key:
Insert into tNotice.

And then latter on in the stored proc, I need to write a query against the tNotice table to use to build up another insert statement into a different table. My question is, will the newly inserted values be selected since this is going to be in a transaction, and the commit won't be until the end of the stored proc?

Best Answer

You can do a quick test to confirm this, I just ran a test out of curiousity, this is on sql server 2012. The current session will have visibility to a record that was previously inserted but not committed, however other sessions will not have visibility to that depending on their transaction isolation level.

Example

create table hs_test(
id int
)

begin transaction

insert into hs_test values (1)

select * from hs_test

--commit

I ran this in one ssms window, with the default isolation level of ReadCommitted. Without committing, the select will still see the value of 1 inserted.

Now if you open up another ssms window, and try to query that table, you won't see any records, because it hasn't been committed yet.

Related Solutions

Sql-server – SQL Server: affect other transactions

Assuming they are running on the same session, no, as you've already said. Are there ways around it? Sure, take it out of that session by plopping the messages in a service broker queue with an activated stored procedure. The only part I'm not quite sure on is if the WITH LOG part of raiserror would be subject to that or not - though you probably don't want to write the results to the errorlog.

It could also be shortened so that each part is its own transaction but that may not be suitable for your needs.

Sql-server – Query data before a commit transaction

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;

Best Answer

Related Solutions

Sql-server – SQL Server: affect other transactions

Sql-server – Query data before a commit transaction

Related Question