是否可以在 SQL Server 中强制行级锁定? [英] Is it possible to force row level locking in SQL Server?

问题描述

我可以看到如何在 SQL Server 中关闭行级和页级锁定，但我找不到强制 SQL Server 使用行级锁定的方法.有没有办法强制 SQL Server 使用行级锁定而不使用页级锁定?

I can see how to turn off row level and page level locking in SQL Server, but I cannot find a way to force SQL Server to use row level locking. Is there a way to force SQL Server to use row level locking and NOT use page level locking?

推荐答案

您可以使用 ROWLOCK 提示，但如果资源不足，AFAIK SQL 可能会决定升级它

You can use the ROWLOCK hint, but AFAIK SQL may decide to escalate it if it runs low on resources

来自文档:

ROWLOCK 指定行锁是当页或表锁定时采取通常采取.在指定时在 SNAPSHOT 上操作的交易隔离级别，行锁不是除非 ROWLOCK 与其他需要锁的表提示，例如UPDLOCK和HOLDLOCK.

ROWLOCK Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

和

锁定提示 ROWLOCK、UPDLOCK 和 XLOCK获取行级锁可能会放置锁定索引键而不是实际数据行.例如，如果一个表有一个非聚集索引，和一个使用锁定提示的 SELECT 语句是由覆盖索引处理，锁是在索引键上获取覆盖索引而不是数据基表中的行.

Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.

最后关于 SQL Server 2005 中的锁升级的非常深入的解释，在 SQL Server 2008 中进行了更改.

And finally this gives a pretty in-depth explanation about lock escalation in SQL Server 2005 which was changed in SQL Server 2008.

还有，非常深入:锁定数据库引擎(在线书籍)

There is also, the very in depth: Locking in The Database Engine (in books online)

所以，一般来说

UPDATE
Employees WITH (ROWLOCK)
SET Name='Mr Bean'
WHERE Age>93

应该没问题，但是根据索引和服务器上的负载，它可能最终升级为页面锁定.

Should be ok, but depending on the indexes and load on the server it may end up escalating to a page lock.

这篇关于是否可以在 SQL Server 中强制行级锁定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！