how does SQL Server know to lock view objects?

Question

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?

---

See the corresponding question on dba.stackexchange for further details.

Motor

Presumably it starts by locking the view to prevent design changes to the view while it's being used. – Michel

@JamieSee, it would take an S od Sch-M lock, then. – Dermatology

The execution plan is stored in a binary format. Not everything that it contains is represented in the XML shown to us. – Marshland

@MartinSmith I think this is probably the answer. The details of the mechanism by which the storage engine knows to lock the view can fairly be considered internal, but the fact that it does this is user-visible, and I would expect it to be documented somewhere. – Motor

Answer 1

Copying from my answer on dba.stackexchange:

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

Dehlia

Answer 2

If you look at the sys.dm_exec_query_optimizer_info view, which returns details of the SQL Server query optimizer, one of the details returned is the following field:

view reference - Number of times a view has been referenced in a query.

It would seem the number of times that a view is referenced is tracked somewhere, possibly as part of the execution plan... my assumption is that even if the view is expanded, the execution plan still contains details of which views were used in the query, and issues the appropriate IS locks against these referenced views.

Chromatid

Might make more sense if there was a separate data structure for these dependencies so it doesn't have to scan the whole plan cache when dependent objects are altered. Don't think this degree of internals are documented anywhere though. – Marshland

@MartinSmith I think you're right... like your comment to the question mentions, these dependencies could be included in the binary of the execution plan. I searched for a while, and this DMV was the only reference I could find that remotely addressed the subject. – Chromatid

@MichaelFredrickson yeah that's a good start, thanks for finding that. I'd really like to know if it's documented anywhere that this lock on the view is taken, and in particular I wouldn't consider that to be an internal implementation detail. Quite the contrary - the locks taken would seem to be a visible part of the semantics of reading through the view. – Motor

Answer 3

By default, views are expanded, like a macro, into the queries that reference them.

This can be turned off, or vary if they are materialised, etc, but macro-like inline-expansion is the norm. This means that locking, etc, behaves as if you did the following...

SELECT
  *
FROM
  blah
INNER JOIN
(
  yourViewCode
)
  AS aView
    ON aView.id = blh.id

Retardment

But it did not behave like this. – Dermatology

And this might be a nice convenience for understanding, but views are compiled when they are created. If the underlying tables change, the views will not, by defalt, be changed. In particular, you should avoid "select *" in a view for this reason. – Garneau

@Dems I'm aware that the view is expanded like this, and I'm not talking about behaviour outside this norm or materialised views. In any case, as usr points out, that not the behaviour I'm asking about. – Motor

@GordonLinoff merits of select * in a view notwithstanding, obviously that's only a simple convenient example of a view - this isn't relevant to the question. – Motor