HOLDLOCK 对 UPDLOCK 有什么影响?
我见过许多 HOLDLOCK 提示与 UPDLOCK 结合使用的示例(像这样)。但是 Microsoft 的文档提示使得 HOLDLOCK 看起来应该是多余的,因为 UPDLOCK 已经将锁保留到事务结束。 (另外,HOLDLOCK 似乎只适用于共享锁。)
HOLDLOCK 对查询有何影响(如果有的话)?
I have seen many examples of the HOLDLOCK hint being used in combination with UPDLOCK (like this). However Microsoft's documentation for these hints make it seem like HOLDLOCK should be redundant, since UPDLOCK already persists the lock until the end of the transaction. (Also it seems to say that HOLDLOCK only applies to shared locks anyway.)
How does HOLDLOCK affect the query, if at all?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
它有很大的影响。
更新锁采用行上的更新锁、页上的意图更新以及表/数据库上的共享锁。
这不会阻止其他查询访问表中的数据,因为页面/数据库上的锁纯粹是共享锁。他们只是可能不会通过尝试执行与锁相矛盾的操作来与单个行/页/表发生锁冲突。如果发生这种情况,请求将在当前锁后面排队并等待它可用,然后才能继续。
通过使用holdlock,查询将被强制序列化,以独占方式锁定表,直到操作完成。这可以防止任何人读取该表,除非使用 nolock 提示,从而允许潜在的脏读。
要查看效果,请生成一个示例表“foo”并在其中放入一些垃圾数据。
打开另一个窗口并尝试:
行返回,现在提交原始查询事务。重新运行它并更改为使用holdlock:
返回到另一个窗口并尝试再次选择数据,查询将不会返回值,因为它被排他锁阻止。在第一个窗口上提交事务,第二个查询的结果将出现,因为它不再被阻止。
最后的测试是使用nolock,使用updlock和holdlock再次运行事务。然后在第二个窗口中运行以下命令:
结果将自动返回,因为您已经接受了脏读(读未提交)的风险。
因此,它被认为具有很大的影响,因为您强制对该表执行的操作被序列化,这可能是您想要的(取决于正在进行的更新),或者会在该表上创建一个非常大的瓶颈。如果每个人都对具有长时间运行事务的繁忙表执行此操作,那么它将导致应用程序内出现严重延迟。
与所有 SQL 功能一样,如果使用正确,它们会非常强大,但错误使用功能/提示可能会导致严重问题。当我必须覆盖引擎时,我更喜欢使用提示作为最后的手段——而不是作为默认方法。
按要求编辑:在 SQL 2005、2008、2008R2(所有企业)中进行测试 - 全部安装在几乎默认设置上,使用所有默认设置创建测试数据库(仅输入数据库名称)。
It has a large impact.
The Update lock takes an Update lock on the row, Intent update on the page and a shared lock on the table / database.
This does not stop other queries from accessing the data within the table, since the locks on the page / database are purely share locks. They just may not clash locks against the individual row / page / table by attempting to perform an operation that would contradict locks. If that occured the request would queue behind the current locks and wait for it to come available before it could proceed.
By using holdlock, the query is being forced to be serialised, locking the table exclusively until the action has completed. This prevents anyone from reading the table unless the nolock hint is used, allowing a potentially dirty read.
To see the effect, generate an example table 'foo' and put some trash data in it.
Open another window and try:
The rows come back, now commit the original query transaction. Re-run it altered to use holdlock as well:
Go back to the other window and try select the data again, the query will not return values since it is blocked by the exclusive lock. Commit the transaction on the first window and the results to the second query will appear since it is no longer blocked.
Final test is to use the nolock, run the transaction again using updlock and holdlock. then run the following in the second window:
The results will come back automatically, since you have accepted the risk of a dirty read (read uncommitted).
So it is seen to have a large impact, in that you are forcing actions against that table to be serialised which might be what you want (depending on the update being made) or will create a very large bottleneck on that table. If everyone did that to a busy table with long running transactions then it would cause significant delays within an application.
As with all SQL features, when used correctly they can be powerful, but mis-use of a feature / hint can cause significant problems. I prefer to use hints as a last resort for when I have to override the engine - not as a default approach.
Edit as Requested : Tested in SQL 2005, 2008, 2008R2 (All Enterprise) - all installed on pretty much default settings, test database created using all defaults (just entered the name of the DB only).
根据 MSDN 文档,Andrew 的答案是正确的,但是我针对 2008R2 和 2012 进行了测试,但没有看到此行为,因此请自行测试
我看到的行为如下:
首先在 play 数据库上运行此 行为。
...然后放入几行。
现在将此代码粘贴到两个查询选项卡中(更改选项卡二中的“选项卡一”文本):
并将其放入另一个选项卡 3 中:
Make确保您指向表格所在的游戏数据库。
突出显示选项卡 1 中更新语句之前的所有内容并执行。
在选项卡 2 中执行相同操作,您会发现选项卡 2 不会完成并且仍在执行。
现在在选项卡 3 中执行简单的 SELECT 在我的环境中它已完成。
突出显示选项卡 1 中的更新语句并执行它(不要尚未执行提交),您将看到选项卡 2 仍在执行。
继续执行选项卡 1 中的提交...选项卡 2 现在将完成选择...您可以运行其余部分。
Andrew's answer's is correct as per MSDN documentation, however I tested against 2008R2 and 2012 and I am not seeing this behaviour so please TEST yourself
The behaviour I am seeing is as below:
First run this on a play database.
...and put a few rows in.
Now paste this code into two query tabs (change the 'tab one' text in tab two):
And put this in another tab 3:
Make sure your pointing at your play database where the table is.
Highlight everything BEFORE the update statement in tab 1 and execute.
Do the same in tab 2 you will find tab 2 will NOT complete and is still executing.
Now execute the simple SELECT in tab 3 in my environment it completes.
Highlight the update statement in tab 1 and execute it (do NOT do the commit yet), you will see tab 2 is STILL executing.
Go ahead and execute the commit in tab 1...tab 2 will now complete the select...you can run the rest.