SET TRANSACTION ISOLATION LEVEL works only with transactions?

Question

In the official example here we have the SET TRANSACTION ISOLATION LEVEL being used in conjunction with an explicitly defined transaction.

My question is, if I execute a query from a SqlCommand, like:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * from MyTable

would I benefit from the new isolation level I set?

Or do I need to explicitly define a transaction like this?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
    SELECT * from MyTable
COMMIT TRANSACTION;

UPDATE: As per Randy Levy's answer, I will update my query as follows:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * from MyTable;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

This is to overcome possible isolation level leaks when using pooling.

You shouldn't need a transaction. Unless I'm mistaken, `READ UNCOMMITTED` just makes _any_ select statement work as though it was using the `NOLOCK` hint. — ZLK, Oct 12 '16 at 00:20

score 3 · Accepted Answer · edited May 23 '17 at 10:34

Yes, you would benefit from the transaction isolation level that you set even if not within an explicit BEGIN TRANSACTION. When you set the transaction isolation level it is set on a connection level.

From SET TRANSACTION ISOLATION LEVEL (Transact-SQL):

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.

One "gotcha" (issue) that can occur is that the isolation level can leak between different connections when using pooling. If you are explicitly setting an isolation level in one (or some) particular piece(s) of code (but using the default most other places) and also using connection pooling. This can cause strange issues if code expects the default isolation level "A" but obtains a connection that had the isolation level explicitly set to "B".

It seems this issue is now fixed in later versions of SQL Server: SQL Server: Isolation level leaks across pooled connections

Excellent catch, thanks for pointing that out! Because [the default isolation level is READ COMMITTED](http://stackoverflow.com/questions/10003026/what-is-the-default-transaction-isolation-level-for-sql-server-with-ado-net), I think that it makes sense to update my query as follows: `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * from MyTable; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;` I should then be protected by this possible bug because I target SQL Server 2005+ — Adi, Oct 12 '16 at 17:02

Scott Hannen · Answer 2 · 2016-10-12T01:57:10.567

2

The first one

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * from MyTable

will work. The transaction level you set applies to each subsequent transaction, and your SELECT statement is its own implicit transaction.

You would only need to explicitly start a transaction if you needed to ensure some degree of consistency throughout multiple reads. For example if you use SERIALIZABLE then you could wrap multiple SELECTs in a transaction and ensure that the underlying data isn't modified while you're reading it.

edited Oct 12 '16 at 01:57

answered Oct 12 '16 at 01:10

Scott Hannen

27,588
3
45
62

Just want to add the explicit transaction is also needed in REPEATABLE_READ and SNAPSHOT levels when multi-statement consistency is needed, – Dan Guzman Oct 12 '16 at 01:33
Yes - I should I have clarified that I was just using SERIALIZABLE as an example. – Scott Hannen Oct 12 '16 at 01:56
@DanGuzman Can you link to any more information on that? In testing I was finding what you said to be true but it seemed contradictory to the selected answer and I didn't see anywhere in the docs that says whether or not a transaction must be started. – xr280xr Mar 10 '21 at 22:06

score 1 · Answer 3 · answered Oct 12 '16 at 03:42

Every statement in SQL Server is run in the context of a transaction. When you do something like

select * from [dbo].[foobar];

SQL Server really does:

begin transaction;
select * from [dbo].[foobar];
commit;

So, setting an explicit transaction isolation level does affect transactions. Even the implicit ones that the database engine starts on your behalf!

SET TRANSACTION ISOLATION LEVEL works only with transactions?

3 Answers3