CODEWITHSUNDEEP
c#sql-servertransactionsisolation-level
Adi
Adi

Reputation: 5223

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.

Upvotes: 3

Views: 3791

Answers (3)

Randy Levy
Randy Levy

Reputation: 22655

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

Upvotes: 3

Ben Thul
Ben Thul

Reputation: 32667

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!

Upvotes: 1

Scott Hannen
Scott Hannen

Reputation: 29207

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.

Upvotes: 2

Related Questions