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