在SQL Server中，是否可能强制进行行级锁定？

Question

我知道如何关闭SQL Server中的行级锁定和页级锁定，但我找不到强制SQL Server使用行级锁定的方法。有没有办法强制SQL Server使用行级锁定而不是页级锁定？

- Elan

---

主要问题是：你到底为什么要这样做呢？ - marc_s

我有两个SQL语句遇到了死锁，这出乎意料。链接如下： https://dev59.com/aE7Sa4cB1Zd3GeqP5ac4 - Elan

@marc_s 我有同样的需求。你知道原因吗？我有一个需要在并行中运行的存储过程，其中有多个表的代码需要修改大量行。不知道为什么这不被视为有效和自然的需求。问题是它会导致SQL Server死锁。 - Mashrur

我有同样的需求。对于某些算法的正确功能来说，取出行级锁是绝对必要的功能。为了避免死锁，在以一种明确定义的全局顺序(例如，如果行id是整数，则按升序或降序锁定集合)的方式下，需要将锁集合排序并进行取出。当数据库引擎随意将行锁升级为页锁时，页锁无意中破坏了锁定顺序，因为它们最终会锁定与该页上恰好相同的未关联id的行。 - Triynko

Answer 1

你可以使用ROWLOCK提示，但据我所知，如果SQL资源不足，它可能会决定升级锁。来自文档：

ROWLOCK指定在通常获取页或表锁时获取行锁。在使用SNAPSHOT隔离级别的事务中指定时，只有在将ROWLOCK与其他需要锁定的表提示（例如UPDLOCK和HOLDLOCK）组合使用时才会获取行锁。

并且

获取行级锁的锁提示ROWLOCK、UPDLOCK和XLOCK可能会对索引键而不是实际数据行放置锁。例如，如果一个表具有非聚集索引，并且使用锁提示的SELECT语句由覆盖索引处理，则在覆盖索引中获取索引键上的锁，而不是在基表中的数据行上获取锁。 最后,这提供了关于SQL Server 2005中锁升级的相当深入的解释，这在SQL Server 2008中已更改。

还有，非常详细的：Database Engine中的锁定（在线书籍）。

所以，总的来说

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

应该没问题，但根据索引和服务器负载情况，可能会升级为页面锁定。

- Sam Saffron

---

补充一点，在 SQL Server 2008 及以上版本中，你可以使用 ALTER TABLE tableName SET (LOCK_ESCALATION=DISABLE) 命令有效地禁用锁升级。 - Thiago Dantas

根据BOL的说法，这是不可能的：“数据库引擎不会将行或键范围锁升级为页面锁”，因此它可能最终会升级为页面锁。 - influent

也许快照隔离级别可以解决您的问题，无论它是什么。https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ - Nick.McDermaid

我不认为这是解决方案，我有两个打开的连接，在其中一个连接中使用rowlock更新id = 1，然后在另一个连接中尝试选择*，将完全被阻止。 - Yogurtu

在之前的公司，我们使用Oracle，在几乎所有的事务中都有行级锁定。我们从未在数据库层面上遇到死锁问题。你可以讨厌Larry Ellison，但Oracle是一个非常棒的数据库 :-) - Jose

Answer 2

使用ALTER/CREATE INDEX的ALLOW_PAGE_LOCKS子句：

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

- Remus Rusanu

---

重要提示：如果资源不足，此操作可能会升级为表锁。 - Sam Saffron

@Sam: 细节 ;) 广告真相：我绝对不会在我的数据库中关闭这个功能。解决方法始终是正确地设计模式和查询，使扫描（导致升级的罪魁祸首）根本就不会发生... - Remus Rusanu

完全同意...这种 hack 很少需要，而且最终可能会反噬你。 - Sam Saffron

@RemusRusanu 我不确定是设计还是查询的问题 - 因为当我编写业务逻辑时，我为什么要担心这个呢？至少我没有尝试修改其他行，这会导致问题。这是SQL Server不能高效管理其资源（与Oracle不同），并迫使您更改代码的原因。如果我必须并行运行某些内容，那么我们就会陷入困境，没有任何好的理由。真是可惜！ - Mashrur

@Mashrur 完全同意。 - Umesh Aawte

Answer 3

您不能强制优化器做任何事情，但您可以指导它。

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

请参见 - 使用锁定和提示控制SQL Server

- mdma