是否可以强制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?

我可以看到如何在SQL Server中关闭行级和页级锁，但是我无法找到强制SQL Server使用行级锁的方法。是否有办法强制SQL服务器使用行级别锁定，而不使用页面级别锁定?

3 个解决方案

#1

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

您可以使用ROWLOCK提示，但是AFAIK SQL可能决定在资源不足时将其升级。

From the doco:

从doco:

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指定通常在获取页或表锁时获取行锁。当在快照隔离级别的事务中指定时，除非ROWLOCK与需要锁的其他表提示(如UPDLOCK和HOLDLOCK)相结合，否则不会使用行锁。

and

和

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.

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

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

最后，对SQL Server 2005中的锁升级进行了深入的解释。

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

此外，还有一个非常深入的问题:锁定数据库引擎(在在线图书中)

So, in general

所以,一般来说

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.

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

#2

Use the ALLOW_PAGE_LOCKS clause of ALTER/CREATE INDEX:

使用ALTER/CREATE INDEX的ALLOW_PAGE_LOCKS子句:

ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = OFF);

#3

You can't really force the optimizer to do anything, but you can guide it.

你不能强迫优化器做任何事情，但你可以引导它。

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

See - Controlling SQL Server with Locking and Hints

使用锁定和提示控制SQL Server

#1