LINQ 和 TranscationScope 不工作

发布于 2024-09-16 23:20:33 字数 1317 浏览 8 评论 0原文

我正在使用包含在 TransactionScope 中的 LINQ select 语句（以更改锁定），但根据 SQL Profiler，它似乎不起作用。我的代码如下所示：

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

现在我希望 SQL Profiler 为我的 select 语句显示 BatchStarting 和 BatchComplete。但它显示 RPC:Completed。为什么？当我运行此代码时：

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

它显示 BatchStarting 和 BatchComplete。为什么 LINQ 似乎没有“看到”TransactionScope？

还有一种方法可以通过 Profiler 确认我的隔离级别是否正确？我只能通过审核登录看到初始连接的隔离级别。不显示“更新”来表明它已更改或每个查询正在使用的每个隔离级别。

任何帮助都会很棒！

此外，此代码正在连接到 SQL Server 2008 的 WCF (3.5) 服务中运行

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

木格 2024-09-23 23:20:33

更新：

尝试这样的方法来检查隔离级别：

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));
    }
}

添加：

查找SET TRANSACTION ISOLATION LEVEL

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 原文

~没有更多了~