SQL 删除 ROWLOCK 的目的是什么，我应该什么时候使用它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3005973/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the purpose of ROWLOCK on Delete and when should I use it?
提问by RPS
Ex)
前任）
When should I use this statement:
我什么时候应该使用这个语句：
DELETE TOP (@count)
FROM ProductInfo WITH (ROWLOCK)
WHERE ProductId = @productId_for_del;
And when should be just doing:
什么时候应该只做：
DELETE TOP (@count)
FROM ProductInfo
WHERE ProductId = @productId_for_del;
回答by Guffa
The with (rowlock)is a hint that instructs the database that it should keep locks on a row scope. That means that the database will avoid escalating locks to block or table scope.
这with (rowlock)是一个提示，指示数据库应该在行范围内保持锁定。这意味着数据库将避免将锁升级到块或表范围。
You use the hint when only a single or only a few rows will be affected by the query, to keep the lock from locking rows that will not be deleted by the query. That will let another query read unrelated rows at the same time instead of having to wait for the delete to complete.
当只有一行或几行会受到查询影响时，您可以使用提示，以防止锁定锁定不会被查询删除的行。这将使另一个查询同时读取不相关的行，而不必等待删除完成。
If you use it on a query that will delete a lot of rows, it may degrade the performance as the database will try to avoid escalating the locks to a larger scope, even if it would have been more efficient.
如果您在将删除大量行的查询上使用它，它可能会降低性能，因为数据库将尝试避免将锁升级到更大的范围，即使它会更有效率。
Normally you shouldn't need to add such hints to a query, because the database knows what kind of lock to use. It's only in situations where you get performance problems because the database made the wrong decision, that you should add such hints to a query.
通常，您不需要向查询添加此类提示，因为数据库知道要使用哪种锁。只有在由于数据库做出错误决定而导致性能问题的情况下，您才应该向查询添加此类提示。
回答by Cobusve
Rowlock is a query hint that should be used with caution (as is all query hints).
Rowlock 是一个应谨慎使用的查询提示（与所有查询提示一样）。
Omitting it will likely still result in the exact same behaviour and providing it will not guarantee that it will only use a rowlock, it is only a hint afterall. If you do not have a very in depth knowledge of lock contention chances are that the optimizer will pick the best possible locking strategy, and these things are usually best left to the database engine to decide.
省略它可能仍然会导致完全相同的行为，并且提供它并不能保证它只会使用行锁，毕竟这只是一个提示。如果您对锁争用没有非常深入的了解，那么优化器可能会选择最佳的锁策略，而这些事情通常最好由数据库引擎来决定。
ROWLOCK means that SQL will lock only the affected row, and not the entire table or the page in the table where the data is stored when performing the delete. This will only affect other people reading from the table at the same time as your delete is running.
ROWLOCK 表示 SQL 将只锁定受影响的行，而不是在执行删除时锁定整个表或表中存储数据的页面。这只会影响其他人在您的删除运行的同时从表中读取数据。
If a table lock is used it will cause all queries to the table to wait until your delete has completed, with a row lock only selects reading the specific rows will be made to wait.
如果使用表锁，它将导致对表的所有查询等待直到删除完成，而行锁仅选择读取特定行将等待。
Deleting top N where N is a number of rows will most likely lock the table in any case.
在任何情况下，删除前 N 个（其中 N 是行数）很可能会锁定表。
