关于SQL Server锁定机制
我想问几个关于 SQL Server 锁定机制的问题
如果我没有在 SQL 语句中使用锁定提示，SQL Server 默认使用 PAGELOCK 提示。我说得对吗？？？如果是的话为什么？可能是由于管理太多锁的因素，这是我认为唯一的缺点，但如果还有其他缺点，请告诉我。并告诉我是否可以在合理的情况下更改此默认行为。
我正在编写一个服务器端应用程序，一个同步服务器（不使用同步框架），并且我已经在 C# 代码文件中编写了数据库查询并使用 ODBC 连接来执行它们。现在的问题是，将默认锁定从页更改为行的最佳方法是什么，同时牢记缺点（例如，在查询中添加锁定提示，这就是我计划的）。
如果在没有事务范围的情况下执行 SQL 查询（SELECT/DML）并且语句包含锁提示，那么将获取哪种类型的锁（例如共享、更新、独占）？并且在事务范围内，如果使用 ROWLOCK 提示，事务的隔离级别是否会影响锁类型。
最后，如果有人能给我样本，这样我就可以自己测试和体验所有上述场景（例如点网代码或sql脚本）
谢谢 穆巴沙尔
I would like to ask couple of questions regarding SQL Server Locking mechanism
If i am not using the lock hint with SQL Statement, SQL Server uses PAGELOCK hint by default. am I right??? If yes then why? may be its due to the factor of managing too many locks this is the only thing i took as drawback but please let me know if there are others. and also tell me if we can change this default behavior if its reasonable to do.
I am writing a server side application, a Sync Server (not using sync framework) and I have written database queries in C# code file and using ODBC connection to execute them. Now question is what is the best way to change the default locking from Page to Row keeping drawbacks in mind (e.g. adding lock hint in queries this is what i am planning for).
What if a sql query(SELECT/DML) is being executed without the scope of transaction and statement contains lock hint then what kind of lock will be acquired (e.g. shared, update, exclusive)? AND while in transaction scope does Isolation Level of transaction has impact on lock type if ROWLOCK hint is being used.
Lastly, If some could give me sample so i could test and experience all above scenarios my self (e.g. dot net code or sql script)
Thanks
Mubashar
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
没有。它在认为合适时锁定并根据需要升级锁
让数据库引擎管理它
请参阅第 2 点
请参阅第 2 点
如果您想要特定的特定行为，我只会使用锁定提示，例如 队列 或非阻塞（脏）读取。
更一般地说，为什么您认为数据库引擎默认情况下不能执行您想要的操作？
No. It locks as it sees fit and escalates locks as needed
Let the DB engine manage it
See point 2
See point 2
I'd only use lock hints if you want specific and certain behaviours eg queues or non-blocking (dirty) reads.
More generally, why do you think the DB engine can't do what you want by default?
默认锁定是行锁而不是页锁，尽管锁定机制的工作方式意味着您将在层次结构中的所有对象上放置锁，例如读取单个行将在表上放置共享锁，在页，然后是行上的共享锁。
这使得请求表上的独占锁的操作知道它可能还没有获得它，因为存在共享锁（否则它必须检查每个页/行是否有锁。）
如果您为一个表发出太多锁，然而，对于单个查询，它会执行锁升级，从而降低锁的粒度 - 因此它管理的锁更少。
这可以使用跟踪标志关闭，但我不会考虑它。
在您知道确实存在锁定/锁定升级问题之前，您可能会面临过早优化不存在问题的风险。
The default locking is row locks not page locks, although the way in which the locking mechanism works means you will be placing locks on all the objects within the hierarchy e.g. reading a single row will place a shared lock on the table, a shared lock on the page and then a shared lock on the row.
This enables an action requesting an exclusive lock on the table to know it may not take it yet, since there is a shared lock present (otherwise it would have to check every page / row for locks.)
If you issue too many locks for an individual query however, it performs lock escalation which reduces the granularity of the lock - so that is it managing less locks.
This can be turned off using a trace flag but I wouldn't consider it.
Until you know you actually have a locking / lock escalation issue you risk prematurely optimizing a non-existant problem.