T-SQL 中的悲观锁
如果我在 MS SQL Server 中选择一行进行更新，并希望将其锁定直到更新或取消，哪个选项更好：-
1）使用像 UPDLOCK 这样的查询提示 2）对事务使用REPEATABLE READ隔离级别 3) 任何其他选项。
谢谢， 查克。
If i SELECT a row for updating in MS SQL Server, and want to have it locked till i either update or cancel, which option is better :-
1) Use a query hint like UPDLOCK
2) Use REPEATABLE READ isolation level for the transaction
3) any other option.
Thanks,
Chak.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您正在等待其他资源（例如最终用户），那么请采纳戴夫·马克尔的建议，不要这样做。
否则，请尝试以下 T-SQL 代码：
HOLDLOCK 提示礼貌地要求 SQL Server 保持锁定，直到您提交事务。 ROWLOCK 提示礼貌地要求 SQL Server 仅锁定该行，而不是发出页锁或表锁。
请注意，如果大量行受到影响，SQL Server 要么会采取主动并升级为页锁，要么您将有一整群行锁填满服务器的内存并导致处理陷入困境。
If you're waiting on another resource such as an end-user, then take Dave Markle's advice and don't do it.
Otherwise, try the following T-SQL code:
The HOLDLOCK hint politely asks SQL Server to hold the lock until you commit the transaction. The ROWLOCK hint politely asks SQL Server to lock only this row rather than issuing a page or table lock.
Be aware that if lots of rows are affected, either SQL Server will take the initiative and escalate to page locks, or you'll have a whole army of row locks filling your server's memory and bogging down processing.
两者都不。 您几乎不想在用户输入数据时保持事务打开。 如果您必须实现这样的悲观锁，人们通常会通过滚动自己的功能来实现。
考虑一下你正在做的事情的全部后果。 我曾经开发过一个像这样实现锁定的系统。 您经常会遇到大量陈旧的锁，当您将其强加给用户时，他们很快就会感到困惑和愤怒。 在我们的案例中，我们的解决方案是完全删除此锁定功能。
Neither. You almost never want to hold a transaction open while your user is inputting data. If you have to implement a pessimistic lock like this, people generally do it by rolling their own functionality.
Consider the full ramifications of what you are doing. I once worked on a system that implemented locking like this. You often run into tons of stale locks, and your users get confused and angry very quickly when you foist this on them. The solution for us in our case was to remove this locking functionality entirely.
请注意，尽管使用 ROWLOCK，SQL Server 可能会选择在其认为需要的情况下仍采用整页锁定。
just note that despite using ROWLOCK SQL Server might choose to still take a full page lock if it deems needed.