Discussion Forum

Main >> MS SQL Server and other Databases>> System.Transactions.IsolationLevel.ReadUncommitted does not result in set transaction isolation level read uncommitted

All Threads Register Login New Discussion
AuthorPostings
Jane

System.Transactions.IsolationLevel.ReadUncommitted does not result in set transaction isolation level read uncommitted

Thursday, November 22 2018 11:42 AM

I am trying to set set transaction isolation level read committed by using the System.Transactions.IsolationLevel.ReadUncommitted option in razor pages. The code executes fine but when I look into the sql profiler I see “set transaction isolation level read committed” instead of “set transaction isolation level read uncommitted”
Basically I am trying to replicate with (nolock) for a report query.
I tried several things using the movie demo that Microsoft uses in their tutorial on razor pages “RazorPagesMovie”.
I did several things:
Through context
////////////////
using (var t = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, new System.Transactions.TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}, System.Transactions.TransactionScopeAsyncFlowOption.Enabled))
{
Movie = _context.Movie.ToList();
//Movie = await _context.Movie.ToListAsync();
t.Complete();
t.Dispose();
}

///////////////////
Through direct connection
******************************
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{

SqlConnection cnn;
string connetionString = "Server=(localdb)\\mssqllocaldb;Database=RazorPagesMovieContext-bea184b4-d217-450a-997b-1b9c8c9f15b2;Trusted_Connection=True;MultipleActiveResultSets=true";
cnn = new SqlConnection(connetionString);

cnn.Open();
SqlCommand command;
string sql = null;
SqlDataReader dataReader;
sql = "set transaction isolation level read uncommitted";
command = new SqlCommand(sql, cnn);
command.ExecuteNonQuery();
sql = "SELECT * FROM dbo.Movie";
command = new SqlCommand(sql, cnn);
dataReader = command.ExecuteReader();

dataReader.Close();
command.Dispose();

cnn.Close();

}
************
Also I tried in VB.net
*******************
Dim top As New TransactionOptions
top.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
Dim ts As New TransactionScope(TransactionScopeOption.Required, top)
With ts
Dim objConn As SqlConnection = Nothing
objConn = New SqlConnection("Server=(localdb)\mssqllocaldb;Database=RazorPagesMovieContext-bea184b4-d217-450a-997b-1b9c8c9f15b2;Trusted_Connection=True;MultipleActiveResultSets=true")
objConn.Open()
Dim objComm As New SqlCommand("SELECT * FROM dbo.Movie", objConn)
objComm.ExecuteNonQuery()
End With


*************
I wonder if it happens in the background somehow, but would not take chances and will use stored procedure for that I guess.

Post a Replyreply

AT Integrated Inc. | 909 Ridgebrook Road, Suite 112, Sparks, MD 21152 | T: 410-472-2490
Copyright © 2019 AT Integrated Inc. All rights reserved.