我需要行级锁定。

Question

这是如何在SQL Server中强制进行行级锁定的扩展。以下是使用案例：

我有一个账户表，其中包含账号、余额等信息。该表被许多应用程序使用。当我修改一个账户时，很可能有其他人正在修改另一个账户。因此，期望的行为是我将锁定我的账户（行），而另一个用户将锁定他的（另一行）。

但是，SQL Server 2008 R2会将此锁升级为页/表级别，第二个用户会出现超时异常。我尝试了参考问题中提到的所有解决方案，但没有任何作用。

如何强制SQL Server仅对行级别进行锁定，或者如何修改此模型以使其与页/表级锁定一起使用？

编辑：更新通过其PK针对单个记录，并且已进行索引，因此仅更新/锁定一个行，并且该过程不需要超过一分钟。

现在看起来似乎出现了一些奇怪的情况。我正在使用一个ORM库来进行数据访问层，它打开了多个连接，我已经向他们的支持团队提出了问题。但是，为了测试目的，我在查询工具上打开了两个会话，然后进行了以下操作

Session # 1
begin tran
UPDATE myTable SET COL_1 = COL_1 WHERE COL_1 = 101;

Session # 2
SELECT COL_1 FROM myTable WHERE COL_1 = 101;

Session #2的查询超时了!!! 对于COL_1的其他值的查询工作正常。现在看起来，如果同一记录在另一个会话中处于编辑模式，则SELECT会话被阻止。尽管Oracle支持选择正在被其他会话修改的行（使用默认参数/无关键字），但SQL Server不支持（使用默认参数/无关键字），因此问题似乎出在库中。

- bjan

---

现在我正在测试两个用户，记录数为13000。我的测试甚至在单个更新时都失败了，但我将锁定7条记录。 - bjan

如果您只是使用一个更新锁定了单行，那么第二个也只针对单行的更新应该可以正常工作。在您的SQL Server中可能发生了其他事情，或者您可能存在某种错误配置... - marc_s

重新编辑2：您在第一个会话中有未提交的事务，当然第二个会话会超时。 - Arvo

@Arvo 这是有意为之的，保持记录锁定状态，第二个会话只是选择记录而不是修改它，因此应该被允许。 - bjan

不应该。SQL服务器不知道你是否要提交或回滚事务，因此它不会为此记录返回任何值。就像我之前说过的，SQL锁定（和事务）并不是为了阻止用户输入任何数据 - 它们是为了保持数据库一致性。 - Arvo

Answer 1

SQL Server 默认使用行级锁定......所以你究竟需要什么呢？

如果您锁定的行数超过一定数量（大约为5000），那么SQL Server将使用锁升级（锁定整个表而不是单独锁定超过5000行）来优化性能和资源的利用-但这是件好事！ :-)

有方法可以完全关闭锁升级-但不建议这样做！因为您正在干扰SQL Server存储引擎内部的一个非常基本的机制。

请参阅：

• MSDN关于锁升级的文档
• Adam Machanic关于锁升级的博客
• SQL Server中锁定介绍 - 还具有可视化脚本，以说明哪个进程锁定了什么（行、页、表、锁类型等）

- marc_s

---

我正在执行一个虚拟更新以锁定记录，这个虚拟更新使用主键字段。当我尝试在另一个会话中更新另一条记录时，它一直在等待...这是行级锁定吗！！！ - bjan

如果您锁定的行数超过一定数量（大约5000行），那么SQL Server将执行锁升级（锁定整个表而不是单独的5000行）。因此，一旦任务完成，锁定将自动释放还是需要手动释放？ - Nilish

当事务完成（或回滚）时，SQL Server将释放涉及的锁定。 - marc_s

如果我正在从Excel导入到SQL Server，并且Excel中的记录超过50,000条。是否有可能由另一个用户向同一表中插入单个记录？ - Nilish

@Nilish：你需要将导入分批处理，每个批次少于5,000行 - 这样就不会遇到锁升级问题了。 - marc_s

提到的解决方案来关闭锁升级并不起作用。 - bjan

Answer 2

想象一下您的系统是一个客户端-服务器应用程序，其中客户端和服务器通过非常慢的线路连接（例如蜗牛邮件），用户修改其记录的时间非常长（例如一周）。然后考虑一下，何时需要锁定行/数据以及何时允许更改行/数据等 - 显然，将SQL服务器内部锁定几天不再是一个好主意。

如果没有两个用户需要更改同一条记录的情况，那么在更改数据时根本不需要锁定。您只需要在记录在数据库中更改时的短暂时刻进行锁定 - 换句话说，当用户提交更改时进行锁定。（这是乐观锁定方案。）当然，如果两个用户更改相同的数据，则最新更改将覆盖早期的更改。

如果您绝对需要两个用户永远不修改相同的数据（悲观锁定），那么可能最通用的方法是使用某些应用程序定义的锁定表或数据表中的特定字段。当一个用户签出某个记录（开始编辑或类似操作）时，您需要检查该记录是否已经在使用（锁定），如果没有，则将此记录标记为已锁定。当然，您还需要一些功能来删除过期的锁定。

或者使用SQL服务器内部的特定功能来处理这种情况。请参阅这里：MSDN中的sp_getapplock函数，这样您就不必担心记录被永久锁定等问题。

- Arvo

---

如果您单独锁定了超过5000行，则完整表将被锁定。 - Nilish

我建议不要在这种情况下使用SQL锁定，而是创建自定义逻辑。我们已经为我们的财务应用程序做到了这一点 - 用户经常开始编辑文档，然后在按Ctrl + S之前去吃午饭 :) - Arvo

我只锁定了一行，时间不到一分钟，而另一个用户正在锁定另一行。无论我需要花费一分钟还是十分钟，其他用户都不应该等待，因为他们正在锁定另一条记录。 - bjan

为什么你要锁定行呢？通常只有在写入更改到数据库时才需要锁定，这只需要几毫秒的时间（除非执行了一些缓慢的触发器代码）。如果你想阻止用户同时更改记录，那么 SQL Server 内部锁定并不适用于此。 - Arvo

软件定义的行锁定方法是一个好主意（即只需拥有自己的锁字段和相关逻辑），只要使用数据库的每个应用程序都尊重它，这在原始问题中并不是这样。 - Alan B