Not Honoring Transaction Isolation Level Settings

[BobPusateri]

• Azure Data Studio Version: 1.12.0-insider
• SQL Server Version: SQL Server 2017 CU17 on Ubuntu running in Docker
• SSMS Version: 17.9.1 (used for comparison)

Steps to Reproduce:

1. Open a new query window and run the following script (one line at a time) in Azure Data Studio:

   
   DBCC USEROPTIONS;
   -- expected isolation level: default (read committed or read committed snapshot)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

DBCC USEROPTIONS; -- expected isolation level: repeatable read

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

DBCC USEROPTIONS; -- expected isolation level: serializable

I would expect the "isolation level" row of each `DBCC USEROPTIONS` call to reflect the specified transaction isolation level, yet it only shows "read committed" each time.

I have further examples of queries not behaving properly (in particular for a presentation I wrote demonstrating isolation levels), but I believe it all boils down to something not changing the the session to the specified transaction isolation level. This same script provides the desired output (and my other queries run correctly) in SSMS, just not in Azure Data Studio.

I am happy to provide further information if necessary. Please feel free to contact me.

[EklipZgit]

This is a pretty big bug to be open for 3 years. Troubleshooting an issue where SSMS and Azure Data Studio did not agree on the state of the database was freaky. Not a good look, at least disable the setting so that users dont THINK that it is reading committed when really reading uncommitted.....

[kburtram]

The current implementation is that query execution options are applied to the session each time a query is executed. If the above script is executed as a single unit then the result is as expected, but line-by-line the query options get reapplied on each query execution, so if a previously executed SET statement conflicts with the user's query execution options then the previous statements changes will be overridden on subsequent executions.

When we looked at this previously the issue is that some execution options should be applied only once when a session is established, but we didn't have a hook to detect when a connection had been disconnected and reconnect in the reliable connection retry handlers, so it was possible in that case that the options could be dropped. The current design at least consistently ensures the user's options are applied for each execution. A simple workaround is to ensure that the either the desired execution options are set in the user settings or to include the SET commands in the batch with the other statements.

I'll move this back into an upcoming iteration to investigate properly hooking up to the query execution options to the on-connect events instead of the on-execute events. We're planning to make several improvements to our connection handling in the next few releases and this is an important bug to get fixed as part of that.

[EklipZgit]

I don't think you're understanding the problem here.

User A: Open SSMS, Begin transaction DELETE one row from table. DO NOT commit transaction.

User B: Open SSMS. SELECT FROM (TABLE) WHERE id = (deleted uncommitted id) returns 1 row, it isn't committed. Open ADS. SELECT FROM (TABLE) WHERE id = (deleted uncommitted id) returns 0 rows, reading the uncommitted transaction despite my settings clearly saying 'READ COMMITTED'.

Wasted an hour and a half of two engineers time and had to pull some senior DBAs into a call to finally figure out that what we were looking at was an uncommitted row being read by an application that was very clearly supposed to be configured to only read committed data.

The current design at least consistently ensures the user's options are applied for each execution. A simple workaround is to ensure that the either the desired execution options are set in the user settings or to include the SET commands in the batch with the other statements.

Clearly untrue per this demonstration, no? Or am I missing something?

[cheenamalhotra]

Hi @EklipZgit

Are you connecting to an instance of Azure Synapse Analytics or a Parallel Data Warehouse? In which case the default mode is indeed READ_UNCOMMITTED as its the only mode supported, also mentioned on MS Documentation.

I verified with Azure Synapse (uncommitted read only), Azure SQL DB as well as on-premise SQL Server and couldn't reproduce the behavior you described. Both SSMS and ADS behave same. Please let me know if I missed something!

[kburtram]

@EklipZgit thanks for providing the additional context. I also was unable to repro the above problem using the following repro. It's likely there is some detail I'm missing.

0. Open Azure Data Studio or SSMS with default query options and connect to SQL Server.
1. Open an editor and execute BEGIN TRAN and DELETE a row. Leave the editor open and the transaction pending.
2. Open another editor and run a SELECT statement on the table with the pending transaction.

What I'm seeing is that in both SSMS and ADS the SELECT query will block while the transaction is pending. If the transaction is rolled back or committed the the select completes and displays the expected resultset. Please let us know if we're missing something in the repo.

It is possible that you're hitting a different bug than is reported above which is about differences in behavior when the provided repro script is run as a single batch vs. line-by-line. The difference is due to query options being reapplied on each query execution vs. when a session is established. This behavior can be observed by running multiple queries in a query editor while profiling the session. For example, in the below screenshot there is a query that ADS is running prior to each SELECT statement that reapplies the user setting query options, including the isolation level, with the side-effect that SET statements previously executed on that session will be overwritten.

[cheenamalhotra]

I will proceed to close this issue as there's been no activity. Please open a new issue for any additional support.