In SQL Server 2008 I have a view V over tables A and B that looks roughly like
在SQL Server 2008中，我有一个视图V /表a和表B，看起来很像
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.
从V读取会导致查询在基表上获取意图共享锁，但也会在视图对象本身上获取意图共享锁。
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.
很明显，我们需要表上的is锁，并且我们可以看到视图上的is锁防止对视图下的表进行并发修改。这很好。
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.
查询计划中没有提到视图。它是完全编译出来的，本例中的结果计划是将两个基表中的行连接起来。实际上，查询计划XML中惟一提到的视图是语句文本。
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.
如果在表上添加第二个视图U，则从V读取不会导致对U进行任何锁定。这就排除了引擎只对a和B上的所有视图进行IS锁定的可能性。
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.
请参阅dba的相应问题。课件为进一步的细节。
3 个解决方案
#1
3
Copying from my answer on dba.stackexchange:
复制我在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. :-)
我相信的二进制版本执行计划(不是一个可读的,我们通过XML,只有一个子集的二进制版本)必须包含一些指向原始查询中引用视图(s)文本(这是上面提到的)。显然，它不是每次都解析查询文本。Conor暗示了以上内容，但要小心不要透露关于存储在何处或如何存储的任何细节，因为这可能会随着版本的不同而变化，甚至会随着服务包或累积更新而变化。他可能也不想鼓励任何侦探工作。:-)
#2
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:
如果你看一下系统。返回SQL Server查询优化器的详细信息的dm_exec_query_zer_info视图，返回的详细信息之一是以下字段:
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.
似乎在某个地方跟踪某个视图被引用的次数，这可能是执行计划的一部分……我的假设是，即使扩展了视图，执行计划仍然包含查询中使用哪些视图的细节，并针对这些引用的视图发出适当的is锁。
#3
0
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
#1
3
Copying from my answer on dba.stackexchange:
复制我在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. :-)
我相信的二进制版本执行计划(不是一个可读的,我们通过XML,只有一个子集的二进制版本)必须包含一些指向原始查询中引用视图(s)文本(这是上面提到的)。显然，它不是每次都解析查询文本。Conor暗示了以上内容，但要小心不要透露关于存储在何处或如何存储的任何细节，因为这可能会随着版本的不同而变化，甚至会随着服务包或累积更新而变化。他可能也不想鼓励任何侦探工作。:-)
#2
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:
如果你看一下系统。返回SQL Server查询优化器的详细信息的dm_exec_query_zer_info视图，返回的详细信息之一是以下字段:
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.
似乎在某个地方跟踪某个视图被引用的次数，这可能是执行计划的一部分……我的假设是，即使扩展了视图，执行计划仍然包含查询中使用哪些视图的细节，并针对这些引用的视图发出适当的is锁。
#3
0
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