Adi Adi - 1 month ago 17
C# Question

SET TRANSACTION ISOLATION LEVEL works only with transactions?

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.

Answer

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