开发者

LINQ and TranscationScope not working

开发者 https://www.devze.com 2023-01-14 00:59 出处:网络
I am using LINQ select statement wrapped in a TransactionScope (to change the locking) but according to SQL Profiler, it doesn\'t seem to be working. My code looks like:

I am using LINQ select statement wrapped in a TransactionScope (to change the locking) but according to SQL Profiler, it doesn't seem to be working. My code looks like:

using (var ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted} ))
{
   using (myDBDataContext dbPKC = new myDBDataContext(conn))
   {
      ...query...
      ts.Complete();
      return xmlMachine;
   }
}

Now I would expect SQL Profiler to show BatchStarting and BatchComplete for my select statement. But it shows RPC:Completed开发者_StackOverflow中文版. Why? when I run this code:

using (SqlConnection conn1 = new SqlConnection())
    {
      conn1.ConnectionString = WebConfigurationManager.ConnectionStrings["myConnectionString"].ToString(); ;
      conn1.Open();
      using (SqlTransaction trans1 = conn1.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
      {
        SqlCommand cmd = new SqlCommand("select * from Machines where pkID = 5");
        cmd.Connection = conn1;
        cmd.Transaction = trans1;
        SqlDataReader reader = cmd.ExecuteReader(); // just execute something
      }
    }

It shows BatchStarting and BatchComplete. Why doesn't LINQ seem to "see" the TransactionScope?

Also is there a way to confirm that my isolationlevel is correct through Profiler? I can only see the initial connection's isolation level through Audit Login. No "update" is displayed to show that it was changed or what each isolationlevel each query is using.

Any help would be wonderful!

Also, this code is running in a WCF (3.5) service connecting to SQL Server 2008


UPDATED:

Try something like this to check isolation level:

using(TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, options))
{
    //Verify Scope using DBCC USEROPTIONS
    SqlCommand cmd = (SqlCommand)ctxt.Connection.CreateCommand();
    cmd.CommandText = "DBCC USEROPTIONS";
    SqlDataReader r = cmd.ExecuteReader();
    while (r.Read())
    {
        Console.WriteLine(r.GetValue(0) + ":" + r.GetValue(1));
    }
}   

ADDED:

Look for SET TRANSACTION ISOLATION LEVEL

0

精彩评论

暂无评论...
验证码 换一张
取 消