在（无关？）交易使用IsolationLevel.ReadUncommitted时的SqlDependency订阅不工作 [英] SqlDependency subscription not working when using IsolationLevel.ReadUncommitted in (unrelated?) Transaction

问题描述

我设法得到的SqlDependency的工作，但只是只要我不就是我想用 IsolationLevel.ReadUncommited 是一个SQL事务无关的SqlDependency。

I've managed to get SqlDependency working, but only as long as I do not use IsolationLevel.ReadUncommited in what I thought was a SQL transaction unrelated to the SqlDependency.

当我使用 IsolationLevel.ReadUncommitted 在交易（下大量注释）在认购的SqlDependency失败，立即的OnChange 通知：

When I use IsolationLevel.ReadUncommitted in the transaction (heavily commented below) the SqlDependency subscription fails with an immediate OnChange notification of:

sqlNotificationEventArgs.Info = "Isolation";
sqlNotificationEventArgs.Source = "Statement";
sqlNotificationEventArgs.Type = "Subscribe";

当我删除的IsolationLevel一切正常（当然，隔离是不正确的，当然。）

When I remove the IsolationLevel everything works as expected (well, the isolation isn't right, of course).

下面是我的相关代码：

private static string connString = "the connection string";
[MTAThread]
private static void Main(string[] args)
    while(true)
    {
        using (var context = new LinqDataContext(connString))
        {
            var conn = context.Connection;
            conn.Open();
            /***********************************************************************/
            /* Remove `IsolationLevel.ReadUncommitted` and the SqlDependency works */
            /***********************************************************************/
            using (var trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted))
            {
                // simplified query, the real query uses UPDATE OUTPUT INSERTED
                const string sqlCommand = "SELECT [Columns] FROM dbo.[TABLE] WHERE [Status] = 'ready'";
                results = conn.Query({transaction: trans, sql: sqlCommand});
                trans.Commit();
            }
            DoAwesomeStuffWithTheResults(results, context);
        }
        WaitForWork();
    }
}

的

的SqlDependency相关的代码：

The SqlDependency related code:

private static ManualResetEvent _quitEvent = new ManualResetEvent(false);

/// <summary>
/// Sets up a SqlDependency a doesn't return until it receives a Change notification
/// </summary>
private static void WaitForWork(){
    // in case we have dependency running we need to go a head and stop it first. 
    SqlDependency.Stop(connString);
    SqlDependency.Start(connString);

    using (var conn = new SqlConnection(connString))
    {
        using (var cmd = new SqlCommand("SELECT [Status] From dbo.[TABLE]", conn))
        {
            cmd.Notification = null;

            var dependency = new SqlDependency(cmd);
            dependency.OnChange += dependency_OnDataChangedDelegate;

            conn.Open();

            cmd.ExecuteReader();
        }
    }
    _quitEvent.WaitOne();
    SqlDependency.Stop(connString);
}
private static void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e)
{
    ((SqlDependency)sender).OnChange -= dependency_OnDataChangedDelegate;
    _quitEvent.Set();
}

我感觉好像我已经妥善处置的背景下，它的连接，和交易 - 前设立的SqlDependency，但它似乎不是这样的。

I feel as though I've properly disposed of the context, its connection, and the transaction - before setting up the SqlDependency, but it would seem that isn't the case.

我在做什么错在这里？

推荐答案

在获得的SqlDependency 工作祝贺（我不是在讽刺在所有的，很多人在这个失败）。

Congrats on getting SqlDependency working (I'm not being sarcastic at all, many had failed at this).

现在是时候看的创建查询通知为 MSDN上的话题。你会看到其下的查询是有效的通知的条件，包括这一要求：

Now is time to read Creating a Query for Notification topic on MSDN. You'll see the conditions under which queries are valid for notifications, including this requirement:

语句不能READ_UNCOMMITTED或快照隔离下运行水平。

The statement must not run under READ_UNCOMMITTED or SNAPSHOT isolation levels.

我写的如何的SqlDependency 工作，也许会澄清一些误解的一些基本知识。而且，作为一个边节点，因为你使用LINQ，你可能有兴趣在 LinqToCache ，它提供了的LINQ 查询和的SqlDependency 。

I wrote about the basics of how SqlDependency works, maybe will clear up some some misunderstandings. And, as a side node, since you're using Linq, you may be interested in LinqToCache, which provides a bridge between Linq queries and SqlDependency.

另一个意见：不要开始（）和停止（）你的的SqlDependency 愿意不愿意，威利。很快你就会后悔的。 开始（）应该是调用一次，应用程序启动时，和停止（）应用程序关闭期间恰好一次（严格的说，是的AppDomain装卸过程中）。

Another comment: do not Start() and Stop() your SqlDependency nilly-willy. You'll regret it soon. Start() is supposed to be called exactly once, during app startup, and Stop() exactly once during app shutdown (strictly speaking, is during appdomain loading and unloading).

现在，关于你的问题：重要的隔离级别是的通知查询之一的。这意味着，在其上附加订阅查询，的不的上你做的更新查询（我不会的智慧发表评论在肮脏的做UPDATE的读取...或者的使用肮脏的智慧读取任何）。据我所知，在READ_UNCOMMITTED您展示代码不应发布查询。您发出 SET TRANSACTION ISOLATION后... 在会话中的所有后续交易（ERGO所有语句）将根据该隔离级别。关闭（通过在DataContext的Dispose）的连接，然后使用不同的连接。除非......你使用连接池。欢迎来到俱乐部无辜受害者的:)。 连接池泄漏横渡隔离级别更改关闭（） / 打开（）边界的。那就是你的问题。有一些简单的解决办法：（！必须的）

Now, about your problem: the isolation level that matters is the one of the notified query. That means, the query on which you attach the subscription, not the query on which you do the UPDATE (I won't comment on the wisdom of doing UPDATE under dirty reads... or the wisdom of using dirty reads for anything). As far as I can tell, the code you show should not post the query under read_uncommitted. After you issue a SET TRANSACTION ISOLATION ... all subsequent transactions (ergo all statements) in that session will be under that isolation level. You close the connection (via the dispose of the DataContext) and then use a different connection. Unless ... you use connection pools. Welcome to the club of innocent victims :). Connection pooling leaks isolation level changes across Close()/Open() boundaries. And that is your problem. There are some easy solutions:

• 您可以重置明确的隔离级别后打开（）


• 您可以使用System.Transactions的范围（我的建议）。强制性阅读：使用新的TransactionScope（）是有害的


• 请不要使用连接池。

和而我们在谈论，你也需要阅读：使用如表队列的。

And while we're talking, you need to read this also: Using Tables as Queues.

这篇关于在（无关？）交易使用IsolationLevel.ReadUncommitted时的SqlDependency订阅不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！