dotnet / dotnet/SqlClient

Wrong isolation level with Sql Azure and TransactionScope

Open
#146 9 comments 2 reactions 1 assignee Claimed by @priyankatiwari08 View on GitHub
Area\Connection Pooling Repro Available :heavy_check_mark:
Dominant language
C#
Stars
989
Forks
340
Avg merge
4d 18h
Merged PRs (30d)
69

Description

### Describe the bug
On SQL Server, executing two queries inside a common TransactionScope, both are executed using the isolation level defined in the TransactionScope, as expected.

The same does not happen on SQL Azure: the second query is executed with the default Azure isolation level that is "Read Committed Snapshot".

### To reproduce
Here the code to reproduce the issue.
```c#
class Program
{
const string AzureConnectionString = "Server=tcp:xxxx.database.windows.net,1433;Initial Catalog=xxxx; Persist Security Info=False;User ID=xxx; Password=xxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout = 30;";
const string SqlServerConnectionString = "Server=localhost,1433;Initial Catalog=LCMS;Integrated Security=True";

static void Main(string[] args)
{
Console.WriteLine("SQL SERVER");
string connectionString = SqlServerConnectionString;
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
TestExec(connectionString); // Expected print: "read uncommitted"
TestExec(connectionString); // Expected print: "read uncommitted"
}

Console.WriteLine("SQL AZURE");
connectionString = AzureConnectionString;
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
TestExec(connectionString); // Expected print: "read uncommitted"
TestExec(connectionString); // Expected print: "read uncommitted", Actual: "read committed snapshot"
}
}

static void TestExec(string connectionString)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var cmd = new SqlCommand()
{
CommandText = "dbcc useroptions",
Connection = conn
};

var reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader.GetString(0) == "isolation level")
Console.WriteLine(reader.GetString(1));
}
}
}
}
```

### Expected behavior
A a new SqlConnection opened inside a TransactionScope must have the same isolation level defined in the TransactionScope.

### Further technical details

**Additional context**
The issue seems related to the connection pooling and the sp_reset_connection, because does not happen using Pooling=No in the connection string.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.