how does SQL Server know to lock view objects?

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.

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

StackExchange Database Administrators Q#16999, answer score: 5