SET TRANSACTION ISOLATION LEVEL does not work

[dhmacher]

Azure Data Studio does not appear to honor transaction isolation levels when set with a T-SQL statement. DBCC USEROPTIONS still reflects the default "read committed" isolation level, and queries still execute with the default isolation level.

• Azure Data Studio Version: 1.48.0
• OS Version: MacOS 14.3.1

Steps to Reproduce:

1. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2. DBCC USEROPTIONS;
3. In the result set, transaction isolation level is appears as "read committed".

Does this issue occur when all extensions are disabled?: Yes

[cheenamalhotra]

@dhmacher

• What server are you connecting to?
• What is the output of SELECT @@VERSION?
• Do you see different results with SSMS?

With SQL 2022 I see expected results in ADS 1.48.0.

[dhmacher]

@cheenamalhotra

I think I figured it out. If I run both SET TRANSACTION ISOLATION LEVEL and DBCC in the same batch, I get the same result as you:

However, if I run one command at a time (but not when I insert GO), the DBCC command consistently returns "read committed":

So it appears like when the last batch completes, ADS resets the isolation level.

I hope this helps. :)

[dhmacher]

I ran an extended events session on the server and it looks like ADS sets all the ANSI flags as well as the transaction isolation level every time I run a statement:

[cheenamalhotra]

That's helpful to understand the issue.

I ran an extended events session on the server and it looks like ADS sets all the ANSI flags as well as the transaction isolation level every time I run a statement:

@alanrenmsft @Charles-Gagnon you may know, is this design fixing an issue or is it a bug that should be addressed in a future release?

[Charles-Gagnon]

This is currently expected behavior, although it does differ from SSMS

#7643 (comment) for some context. I don't recall if there was any follow-up to that discussion, maybe @kburtram remembers.

So yes, ADS will run the SET commands each time it executes a query (effectively overwriting previous values). SSMS does not do this - it will keep the values from previous executions.

[dhmacher]

As a user and developer, I would have expected that isolation level (like the other flags) to be a session-level setting, one which I can change in T-SQL and one that would persist for the duration of the session after I've changed it.

I can't think of any other situation where you would have to explicitly (re-)declare a setting with every batch, so this feels like a significant departure from how users have been working with SQL Server in the last two or three decades. And it's not documented or made obvious anywhere at all.

I'm sorry, but this makes no sense at all to me. :)

[transmokopter]

I have to agree with @dhmacher about this.
If this is the intended behaviour, it needs to come with a big warning sign. Because not only is it different from what we are used to with every other database client. It's also countering what the documentation for SET TRANSACTION ISOLATION LEVEL say:
"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" (https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16).

In this case, I'm not explicitly changing the setting - ADS is invisibly doing it for me. This must be a bug, not a feature.

[sommarskog]

In one single word: Unacceptable.

I've never been keen on ADS, so I have not used it much, so this is why I have not noticed. Else I would have yelled long ago.

[kburtram]

I added some notes into #7643 regarding the current behavior. Where basically the query options are set each time a query is executed. IIRC, SSMS splits the options into two buckets where some options are set on the session only once initially and other options are reset each time a query is executed.

This can create some confusion when executing subsets of a script file by highlighting blocks of text. We should update the way we set options to follow the same options as SSMS.

[kburtram]

The fix for this issue was merged with the most recent STS bump and will be included in the upcoming July release (1.49.0). Related PR linked to issue above.