SqlDependency subscription not working when using IsolationLevel.ReadUncommitted in (unrelated?) Transaction

Question

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.

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";

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

Here is my relevant code:

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

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

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.

What am I doing wrong here?

Answer 1

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

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:

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

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.

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

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:

• You can (must!) reset the isolation level explicitly after Open()
• You can use System.Transactions scopes (my recommendation). Mandatory reading: using new TransactionScope() Considered Harmful
• Do not use connection pooling.

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