Are SQL joins consistent in a resultset

I feel a bit stupid since I'm not sure what words to use in this situation. So let me do my best to paint a picture. If I use a join to combine two tables,

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

t1.prop is not singular, and assuming there are two of themt2.prop is distinct, is there even a remote chance that mid-query computingt2.prop is not populated in the other but in the first? I can't see it in my brain, but I'd expect it would discover every result fort2.prop then use it on the outcomes.

If the answer to that question is "no," then maybe someone can point me anything I could be missing in this situation. I'm attempting to repair a queue table, and luckily for me, I'm spotting a race condition. I've limited it down to the possibility that the query is ignoring things based on locking hints, dirty reads (isolation level is read committed), or that I'm confused. I don't trust the statement above.

Here is the fundamental process.

1. Bulk copy (SQLBulkCopy.net) is used to push a batch of items into the queue at any moment. It is committed with a timestamp while being contained inside a transaction (Only one thread is populating this queue as well, so really at any point, 1 thread might be doing this).

2. There is just one customer currently scanning the queue with a query that like this.

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

The oldest committed items in the queue, arranged by timestamp, should be returned to me, I assume (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).

My issue is that I often process goods for one person out of order.t.Prop .

Although I've inserted triggers indicating which items' timestamps come first, they are still read from the queue out of order. Any advice or guidance?

Update

I demonstrated that I may get a partial result set when I would have anticipated 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 make it an all-or-nothing proposition viainner hash join , however on a different subject, I may experiment with the snapshot isolation level.