Greetings,
We have code that monitors a table and if a record is in it, that does not have a matching record in another table, it creates it. The record must have existed between 6 hours ago and 5 minutes ago to be considered in the first table.
The problem is that it picked up a record that actually had a record created 5 minutes ago in the other table. Let’s call the tables P and Q. thus it goes something like this:
select * from P
outer join Q with nolock on P.id = Q.id
Where p.created between (now-6 hours) and (now-5 minutes)
and Q.id is null
(No, that isn’t executable SQL 🙂 but has all the clauses in it. I don’t have the syntax correct, but please realize that the concepts that are applied are what is important to the question)
We had a record created at 19 minutes past the hour, with a Primary Key of X (in Q), The query above was run at 24 minutes pas the hour, and returned 1 record. It then created a new record that had a Primary Key of X+400. (in Q)
It appears, that the index didn’t get updated for the table Q, and it missed finding that record! We have had 2 DBA’s, 4 Sr. Programmers look at the code, and there doesn’t appear to be any problems.
This table gets hit a LOT, and has 60 million rows in it… And the key that it is keying off of is ever increasing (which won’t work well with Binary tree style indexes) Is it possible that while searching for the record, another process added a record and caused a missearch for that key? (Sort of a Retorical question, yes I know it is possible, after all MSSql is a program like any other.)
Is it possible it is because of the No Lock?
Thanks in advance,
-Chert
—
http://www.traderhut.com