使用“with (rowlock)”在更新语句中时为什么整个表格会被锁定？

Question

我使用WITH (ROWLOCK)更新表中的一行，但是通过执行"sp_lock"，我可以看到整个表被锁定。因此，在事务提交之前，其他事务无法更新表中的其他行。为什么"With (ROWLOCK)"没有起作用？

我正在使用以下带有rowlock的查询：

DELETE FROM DefDatabaseSession  WITH (ROWLOCK) WHERE ProcessName='test';

在同一张表中，同时从任何其他运行相同删除操作的事务中，我收到了异常。

[SQLServer JDBC Driver][SQLServer]锁请求超时。; 嵌套异常是java.sql.SQLException：[newscale] [SQLServer JDBC Driver] [SQLServer]锁请求超时。 ：com.newscale.bfw.udkernel.kernel.UdKernelException：udconfig.defdbsession.delete; SQL [DELETE FROM DefDatabaseSession WHERE ProcessName =？]; SQL状态[HY000]; 错误代码[1222]; [newscale] [SQLServer JDBC Driver] [SQLServer]锁请求超时。; 嵌套异常是java.sql.SQLException：[newscale] [SQLServer JDBC Driver] [SQLServer]锁请求超时。

- vani

Answer 1

在这里的原因是优化器忽略了您的行锁提示[WITH (ROWLOCK)为优化器提供了一个查询提示]。这种情况通常发生在你要处理大量行的时候，在这种情况下，优化器发现在扫描表时获取表锁更加可行。

如需详细讨论，请参阅此链接：http://social.msdn.microsoft.com/Forums/sqlserver/en-US/60238304-04e8-4f98-84d1-3ddf1ed786a9/why-the-entire-table-is-locked-while-with-rowlock-is-used-in-a-update-statement。

- Lokesh

Answer 2

我猜测你没有在ProcessName上建立索引，因此查询必须进行全表扫描，这样所有行都会被读取（并且可能成为删除的候选行），所以锁定整个表比锁定每一行更有效率。

尝试定义一个索引：

CREATE INDEX DefDatabaseSession_ProcessName ON DefDatabaseSession(ProcessName);

---

您可以通过执行 "explain" 命令来查找查询计划：

EXPLAIN DELETE FROM DefDatabaseSession  WITH (ROWLOCK) WHERE ProcessName='test';

- Bohemian

---

在 SQL Server 中不存在 "EXPLAIN"。请使用 "包括实际执行计划" 按钮获取执行计划。 - Alejandro

我有一个ProcessName列的索引。 - vani

但我只需要行锁，因为其他线程将同时删除其他行。所以我只需要锁定我的行而不是所有行。因此，我不能使用表锁。 - vani