SQL server 2005, No Lock question.. Failed index??

By TechRepublic ·

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 =

Where p.created between (now-6 hours) and (now-5 minutes)
and 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,


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

It's possible

by Tony Hopkinson In reply to SQL server 2005, No Lock ...

Using Nolock in statement to modify content is not recomended

Some useful blurb here.

However, something damn strange must have been going on somewhere, that the index was not written five minutes after it should have been....

Unless your logs show something around the same time, this is getting down to littlw blue fellas from outer space playing a practical joke on you as an explanation.

Taking nolock out is going to cost you in an outer join, I'd be tempted to look at querying from P without nolock and then joining the result set to Q

Why couldn't you use an insert trigger on P by the way ?

Collapse -


by TechRepublic In reply to It's possible

Triggers are not allowed here...
Even when they would solve problems.

In this case, the normal processing flow is to create a record in P, send a message to a process that creates a record in Q.

The monitor process checks to make sure that the record is in Q within 5 minutes of when it got put in P to make sure the process is working right (failsafe) - and if it isn't, then it inserts it.

So far, little blue fellas from outer space seem to be the best explaination. :-)

Personally, I think that the Server hick-uped and missed the index search.. (400 records got inserted in those 5 minutes. If the index was changing while it was being searched, and it wasn't properly "locked" (Enter/Leave Critical Sections) inside the Server, in theory, it could miss the search.. If that is the case, this shouldn't happen again.. (if it happened often, Microsoft would have fixed it by now - so it would have to be a 1 in a million+ chance..)


Collapse -

It'certainly a possible explanation

by Tony Hopkinson In reply to Trigger

I'm never comfortable witha littel blues fellas explanation though, often it turns out to be an "Oh I f***ed up explanation" eventually.

Related Discussions

Related Forums