Are SQL joins consistent in a resultset

c# queue sql sqlbulkcopy sql-server

Question

I feel a little like an idiot, because I'm not sure what the terminology is to use here. So let me try and paint the best picture I can. If I'm joining two tables together with a join

select t1.prop, t2.prop from t1 join t2 on t1.prop = t2.prop

t1.prop isn't unique and let's assume there are 2 of them, t2.prop is unique, is there any remote possibility that mid query computing t2.prop is populated in one, but not the other? In my head I can't imagine it, I'd assume it'd find all the results for t2.prop then apply it to the results.

So if the answer to that is no, then maybe someone can point out a possible thing I'm missing here, I'm trying to fix a queue table and lucky me I'm seeing some sort of race condition. I've narrowed it down to either the statement above, which I don't believe, or maybe the query is skipping items based on locking hints, or maybe dirty reads ( isolation level is read committed), or I'm confused.

Here's the basic workflow.

  1. At any point a chunk of items are pushed into the queue using bulk copy (SQLBulkCopy .net), and it's within a transaction, and committed with a timestamp (Only one thread is populating this queue as well, so really at any point, 1 thread might be doing this).

  2. Only one consumer is actively scanning the queue with a query which looks essentially like this

    SELECT Q.* from Queue Q with(rowlock, updlock, nowait) 
    join table t on Q.Prop = t.Prop
    order by Q.Timestamp;
    

I'm under the assumption that this would return me the oldest committed items in the queue ordered by timestamp where they (at one point, I had readpast in here, but I was afraid that could return me things out of order, I also randomly tried tablock and locked the table on the insert, but that made no difference).

So, my problem is I keep getting items processed out of order for an individual t.Prop.

I've added triggers showing items Timestamp is before other items timestamp, yet they get read off the queue in the wrong order. Any hints or help?

Update

I proved I was able to get a partial result set where I would expect all or nothing

    private static void OutOfOrder()
    {
        var cnt = 100;
        var go = true;
        using (var db = new DBManager())
        {
            using (var cmd = db.GetCommand())
            {
                cmd.CommandText = "Delete from Foo";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "Delete from Bar";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "Insert Into Foo (B) Values ('joint')";
                for (var i = 0; i < cnt; i++)
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        var task1 = Task.Run(() =>
        {
            var inserted = false;
            while (go)
            {
                using (var db = new DBManager())
                {
                    using (var cmd = db.GetCommand())
                    {
                        var text = inserted ? "Delete from Bar" : "Insert Into Bar (B, C) Values ('joint', 'blah')";
                        cmd.CommandText = text;
                        Console.WriteLine(DateTime.Now.ToLongTimeString() + "  -  " + text);
                        cmd.ExecuteNonQuery();
                        inserted = !inserted;
                    }
                }
                Thread.Sleep(20);
            }
        });


        var task2 = Task.Run(() =>
        {
            var text = "Select * from Foo join Bar on Foo.B = Bar.B";
            while (go)
            {
                using (var db = new DBManager())
                {
                    using (var cmd = db.GetCommand())
                    {
                        cmd.CommandText = text;
                        Console.WriteLine(DateTime.Now.ToLongTimeString() + "  -  " + text);
                        var ret = cmd.ExecuteDataTable();
                        var retCount = ret.Rows.Count;
                        var valid = retCount == 0 || retCount == 100;

                        if (!valid)
                        {
                            Console.WriteLine("Error, got {0} rows back!!!", ret.Rows.Count);
                            go = false;
                        }
                    }
                }
                Thread.Sleep(17);
            }
        });
    }

I was able to get it to be all or nothing using inner hash join, but on another note I might try using snapshot isolation level.

Popular Answer

Either you use dirty reads (read uncommitted) in reader, or the bulk copy doesn't use transactions. To check both is quite easy:

SELECT Q.* from Queue Q with(rowlock, updlock, nowait, readcommitted) 
  left join table t on Q.Prop = t.Prop
order by Q.Timestamp;

(though personally I would not rely on the nowait hint and added an explicit where).

The latter can easily be traced by SQL Profiler. You don't have to include all bulks into the trace - just add transaction-related events.

P.S. Though the best way to implement a queue is to use one. Service Broker queue, for example.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why