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.
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.
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.
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.