how does SQL Server know to lock view objects?

Question

Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow.

---

In SQL Server 2008 I have a view V over tables A and B that looks roughly like

create view V as
    select * from A
    union all
    select * from B

Reading from V causes a query to take intent shared locks on the base tables, but also takes an intent shared lock on the view object itself.

It is clear why we need the IS locks on the tables, and we can see that the IS lock on the view prevents concurrent modification to the tables underlying the view. That's fine.

The query plan contains no mention of the view. It's completely compiled out, and the resulting plan in this case is a simple concatenation of rows from the two base tables. Indeed the only mention of the view in the query plan XML is in the statement text.

If you add a second view U over the tables, reading from V does not cause any lock to be taken on U. This rules out that the engine just takes an IS lock on all views over A and B.

How does the database engine know to take a lock on the view?

• Is the statement text parsed again?
• Is there some other channel of information between the query planner and underlying execution to pass this information? If so, what?

If the latter, the details of the mechanism by which the storage engine knows to lock the view can fairly be considered internal. However the fact that it does this is user-visible, and I would expect it to be documented somewhere.

Answer 1

Good question. While it is true that view definitions are expanded into the query text very early on in the compilation and optimization process, all sorts of information is tracked with the cached plan that is not directly visible e.g. statistics that were considered 'interesting' for plan exploration, so that we get the expected recompilation if those statistics change. This is true even if the final plan shape chosen did not directly make use of the changed statistics - we should still recompile because there may now be a better plan available.

In a similar vein, a user would reasonably expect that 'using' a view should block any concurrent attempt to change the definition of that view. This should hold true even though the implementation expands view text before any real compilation or optimization occurs. To that end, it makes sense that the view's object_id should be part of the cached plan and an otherwise-very-compatible Intent-Shared lock taken each time that plan is executed. Holding this lock prevents another session acquiring Sch-M (schema modification) on that object and modifying the view while we are logically referencing it (even though it was expanded). Obviously locks on the objects referenced by the view would not be enough.

To the question of whether this sort of thing should be documented or not. The shape of Books Online over the years suggests that documenting something means it has to go through a cycle of deprecation before it can be changed, so unless that apparent policy changes so that 'documentation' is expanded to include 'interesting details that nevertheless might change at any time', it seems unlikely that this sort of internal implementation detail would ever make it into the formal documentation.

Answer 2

From Conor Cunningham, the ultimate source of anything engine- or optimizer-related:

We track things during compile to check at runtime. We do not parse things at execution for this purpose.

Note: the internals of what we do from one release to another are not guaranteed. This is beneath the officially supported surface area.

My belief is that the binary version of the execution plan (not the one that is readable and exposed to us via XML, which is only a subset of the binary version) must contain some pointer to the view(s) referenced in the original query text (and this was alluded to in the SO question). It obviously isn't parsing the query text every time. Conor implies as much above, but is careful to not reveal any details about where or how this is stored, since this could potentially change from release to release or even with a service pack or cumulative update. He probably also doesn't want to encourage any detective work. :-)