TABLOCK HOLDLOCk

[Shaggy Hiker]

This may be a redd herring. I'm trying to track down a puzzling error in what looks like a simple SQL query.

Somebody noticed that a certain field was Null, so I went looking for why that was. The field was in a table that is essentially a materialized view. The view itself is nothing special, though a bit busy to show. It's basically a join between a parent table and a child table, with eight other joins in there to turn integer FKs into more human readable values for display. That's not exactly a complicated query, nor is there anything particularly special about this one. I could show all the joins, but there's nothing special about any of them. Left joins to the child table and the pick list tables, as there may not be any child records (and the parent table holds rather little data). The field with the Null values was one of those human readable fields, and it really can't be null if there are any child records.

In this case, there were 17 child records, and only two of them had nulls. Those two also happened to have the same value in the FK field. Naturally, that suggested that it was a value that no longer existed in the pic table. That would be simple...and I wouldn't be writing this.

Unfortunately, the first thing I did was ignore the materialized view and just create a query to look at an example. To do that, I built up a query with only those joins I needed to see the records. Also, since I knew that there would be child records for the parent record, I didn't bother with any left joins. This meant that the query was very simple indeed. In fact, it was now just two inner joins, one between the parent and child, and one to the one pic list that had the value in question. The problem is that this worked just fine. The value in the FK does, in fact, join to the pic list table and return the proper value. I got my 17 records, and they all had values in the field in question...and they were correct, too.

So, now I have a view that works just fine, though it only includes a couple fields, and a view that includes those fields along with a whole bunch of other fields. In both cases, one critical field displays a human readable value from a table. The joins are on the same tables in both fields, but one view returns a value for all 17 rows, while the other view returns 17 rows, but that one field only has values for 15 of them.

The only key difference that I have seen thus far is that the one view has TABLOCK HOLDLOCK, while the other does not. That doesn't seem like it should impact a join on one value, but I figured I'd put this out there just in case. The join is working, it's just that one value of the FK fails to return what it should.

I'll be trying other things, but any ideas would be welcome. How can a join on a table sometimes return a value for FK X, while in a different view, the join returns no value for FK X, though it returns values for all other FK values?

[Shaggy Hiker]

The plot thickens: Just copied the misbehaving query into a new query, and it worked. That query is part of a stored procedure that inserts new records, and updates existing records. It is the insert portion that I just tested and found to be working. That suggests it's the Update, and may absolve TABLOCK HOLDLOCK. What I was thinking was that perhaps those locks were preventing some aspect of the stored procedure from running, though I had no theory as to how that could only impact a single FK value out of many. The joins in the update and the insert portions are the same, as far as the field in question goes.

[wqweto]

The lock hints hardly affect this query (outside of a transaction).

I would personally check the ON clause of the “faulty” JOIN. Often aliases are wrong i.e. ON a.ID = b.FK_ID instead of ON c.ID = … so this is joining completely different table.

Try to create minimal problematic view by trimming the original view’s source code, not by writing it in a blank window. It’s a syntax error which is working because of all the other tables. Another possibility is ON MyID = b.FK_ID where MyID is unprefixed column from different table.

[Shaggy Hiker]

Yeah, that's the obvious location of the problem, but I have taken every part of the stored procedure out and tested it individually and it works.

Frankly, the most likely answer, at this point, is that somebody went into the table and ran a delete query on it. Every piece appears to be working...and yet the data is what it is.

As chance would have it, I'm in the midst of changing some rules for one of the fields in that table. Once those changes are in place, I'll need to run everything again. At that point, I will be able to find out whether somebody tampered with the data unwittingly. If the missing data still remains once I've updated everything...that will be very interesting.

[digitalShaman]

Hey, Merry Christmas first of all

Since you mentioned that it is a materialized view, have you tried to refresh it?
Do you know why the TABLOCK and HOLDLOCK was put there, i.e. what the purpose is?
Could it be that the LOCKs caused a deadlock when the underlying data was inserted/modified and the update of the materialized view was killed as deadlock victim and thus missing data? Just an idea that would explain what you descibe.

[ChrisE]

if it is only with the UPDATE Query
are the Parameters Cleared before Update?
could be something simple

[Shaggy Hiker]

Hey, Merry Christmas first of all

And a Merry Christmas to you, as well.

Since you mentioned that it is a materialized view, have you tried to refresh it?

I may be using the term incorrectly. We had a view, but it is so horribly complicated that it was kind of slow. The view links views of views of views, plus numerous tables, some views and tables come from different databases, and at least one comes from a different SQL Server located in a different county (though with a fast connection). To speed up some reports, a stored procedure was written that takes the view and populates a table. It is that table that I am calling a materialized view, because it is the view, just built as a table and populated via the stored procedure. In this case, though, the DB is so large that I didn't want to refresh ALL of the table at any one run of the SP, so it updates anything that has changed, adds any new records, and deletes any old records. This is helped by the fact that it is virtually unheard of for anybody to edit last year's data, let alone that from a decade back. Still, it does mean that simply running the SP won't necessarily update anything. I do need to do that, but I have a few other things that have to be fixed before I can.

Do you know why the TABLOCK and HOLDLOCK was put there, i.e. what the purpose is?

Well, I did it, but no, I don't know why they are there. I know I wrote the SP, so I added those, and I can only assume that I did so for a reason, but that was six or seven years ago, and the reason escapes me. I can guess it was because I felt it likely that somebody else would be adding data to the underlying tables at the time the SP was running. Since the SP takes a few minutes to complete, I didn't want anybody to be doing something that would cause trouble. Just speculation, though.

Could it be that the LOCKs caused a deadlock when the underlying data was inserted/modified and the update of the materialized view was killed as deadlock victim and thus missing data? Just an idea that would explain what you descibe.

That's kind of what I was thinking, but the more I think about it, the less I like it. The missing data is far from random. That field holds the species name, while the FK is just a species ID. The missing data is just ONE species out of dozens, and it is just that one species, but spread across about a dozen different days. A lock shouldn't be specific to a single species, and since the SP runs after anybody uploads data, it also shouldn't impact a record here and a record there across a dozen different days. So, it's a good thought, but it seems like it can't be the explanation.

I'm leaning towards this being a human...possibly even me, though I don't think I have touched that DB in a couple years, and this would have been in the last few months.

[Shaggy Hiker]

if it is only with the UPDATE Query
are the Parameters Cleared before Update?
could be something simple

Nope...or at least perhaps nope. There is an INSERT or an UPDATE, and from what I can see in the audit fields, this was the INSERT, not the UPDATE. There also aren't any parameters, so there's that. It's a case of, "grab everything after this date and stuff it into that table." Only the date could be considered a parameter, and that's a pretty blunt parameter that is set only once. In any case I tested what I could test for the UPDATE, and the SELECT that precedes the INSERT, and all is well. Visually inspecting the INSERT....repeatedly, cause I don't trust visual inspections, didn't show anything even slightly odd.

[grindmore]

Tablock Holdlock shouldn’t directly impact the join logic, but it could be causing some blocking issues, especially if there are other queries locking rows or tables involved. Maybe try running the query with NOLOCK or without the hints to see if it behaves differently? Also, double-check for any data inconsistencies or subtle filtering differences between the views.

[Shaggy Hiker]

After further testing, the queries are all running exactly as expected. Locking really couldn't have caused this problem anyways, since it impacted only very specific records that were rare and interspersed with other records that were not impacted. Those records weren't in a block in either time or space, so it made no sense that only THOSE would be impacted. It was records for a particular (rare) species over the course of a month or so. One record here, one record there, amidst thousands of records that all worked just fine.

No explanation for this has been found, but it pretty much had to have been caused by somebodies hand, and not the automated process. Most likely, somebody went into the table and deleted out that specific field for reasons unknown. Slightly possible is that somebody was tinkering with the species list, and happened to not have that species available for the window of time when this happened. I talked to the person who works on, and tracks any work on, the species list, and he has no record of anything being done during the window of time, so this is unlikely....but, I won't say it's impossible.

In the end, I think I will never know what this was about.