CODEWITHSUNDEEP
sqlsql-server
Liran Friedman
Liran Friedman

Reputation: 4287

What is the scope of using SQL Server's "set transaction isolation level read uncommitted"

I wanted to ask about using the

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

in SQL Server 2008.

I have a system in Winforms that gets some data from the DB with select queries.

When I use this statement in a select query from my C# code does it affect other select queries that will run after? Should I reset this statement somehow, and if so, how do I reset it ? Should I use

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

after each query in which I used the READ UNCOMMITTED statement ?

I want this statement to be relevant only to a specific queries that I need.

Upvotes: 0

Views: 455

Answers (1)

Alireza
Alireza

Reputation: 5056

Parts of the documentation that are related to scopes:

  • Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed.
  • If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked.
  • User-defined functions and common language runtime (CLR) user-defined types cannot execute SET TRANSACTION ISOLATION LEVEL.

So Yes. it will "affect other select queries that will run after". You should change the ISOLATION LEVEL back or use table hints just for that particular query: 

SELECT Column1 FROM Table1 WITH (READUNCOMMITTED)

Upvotes: 1

Related Questions