Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
1  
+1 great info Paul –  Aaron Bertrand Apr 26 '12 at 1:07
    
One question: we agree that using the view should block concurrent attempts to change the view definition. Then why take IS rather than Sch-S lock on this? Only difference between the two is behaviour on bulk updates. I would expect it to take the weakest lock it could that gave the required protection. –  Joe Kearney Apr 30 '12 at 15:31
    
@JoeKearney IS conflicts with X as well as BU, so technically there are two differences. As to the reason - no clue offhand. There again, locking in SQL Server isn't always optimal; consider the Sch-M taken on base tables when indexing a view - TAB-S would be sufficient (and would not block concurrent readers). –  Paul White May 2 '12 at 11:36

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.