为什么在更新语句中使用“with（rowlock）”的同时整个表被锁定

Question

我使用WITH（ROWLOCK）更新表的一行，但通过执行“sp_lock”我可以看到整个表是锁定。因此，在事务提交之前，其他事务不能更新表的其他行。为什么“WITH（ROWLOCK）”不起作用？

我使用下面的查询与ROWLOCK：

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

在同一时间从任何其他交易运行在同一表格差分行的相同删除操作我得到异常

[ SQLServer JDBC驱动程序] [SQLServer]超出锁定请求超时时间。嵌套异常是java.sql.SQLException：[newscale] [SQLServer JDBC Driver] [SQLServer]超出锁定请求超时期限：com.newscale.bfw.udkernel.kernel.UdKernelException：udconfig.defdbsession.delete;未归类SQL的SQLException [DELETE FROM DefDatabaseSession WHERE ProcessName =？]; SQL状态[HY000];错误代码[1222]; [newscale] [SQLServer JDBC驱动程序] [SQLServer]超出锁定请求超时时间。嵌套异常是java.sql.SQLException：[newscale] [SQLServer JDBC驱动程序] [SQLServer]超出锁定请求超时期限。

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

Answer 2

我的猜测是，你不必对ProcessName索引，所以查询必须执行全表扫描，因此，所有的行被读出（并且可能是删除的候选对象），所以锁定整个表比锁定每一行更有效。

尝试定义的索引：

CREATE INDEX DefDatabaseSession_ProcessName ON DefDatabaseSession(ProcessName); 

---

你可以做一个解释找出查询计划：

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