在(无关?)事务中使用 IsolationLevel.ReadUncommitted 时，SqlDependency 订阅不起作用 [英] SqlDependency subscription not working when using IsolationLevel.ReadUncommitted in (unrelated?) Transaction

问题描述

我已经设法让 SqlDependency 正常工作，但前提是我不在我认为与 SqlDependency 无关的 SQL 事务中使用 IsolationLevel.ReadUncommited.

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 或 SNAPSHOT 隔离级别下运行.

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.

另一条评论:不要 Start() 和 Stop() 你的 SqlDependency 随意.你很快就会后悔的.Start() 应该在应用程序启动期间被调用一次，而 Stop() 在应用程序关闭期间应该被调用一次(严格来说，是在应用程序域加载和卸载期间).

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 的查询(我不会评论在脏读下执行 UPDATE 的智慧... 或 对任何事情使用脏读的智慧).据我所知，您显示的代码不应在 read_uncommitted 下发布查询.在您发出 SET TRANSACTION ISOLATION ... 之后，该会话中的所有后续事务(因此所有语句)都将处于该隔离级别.您关闭连接(通过处置 DataContext)，然后使用不同的连接.除非……您使用连接池.欢迎来到无辜受害者俱乐部:).连接池泄漏隔离级别跨Close()/Open() 边界变化.这就是你的问题.有一些简单的解决方案:

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:

• 您可以(必须！)在 Open()
之后显式重置隔离级别
• 您可以使用 System.Transactions 范围(我的建议).必读:使用new TransactionScope() 被认为是有害的
• 不要使用连接池.

在我们交谈时，您还需要阅读以下内容:使用表作为队列.

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

这篇关于在(无关?)事务中使用 IsolationLevel.ReadUncommitted 时，SqlDependency 订阅不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！