为什么在“ with（rowlock）”时锁定整个表？用于更新语句 [英] why the entire table is locked while "with (rowlock)" is used in an update statement

问题描述

我使用WITH（ROWLOCK）更新表的一行，但是通过执行 sp_lock，我可以看到整个表都被锁定了。因此，在提交事务之前，其他事务无法更新表的其他行。为什么 WITH（ROWLOCK）不生效？

我在将以下查询与行锁一起使用：

 使用（ROWLOCK）从DefDatabaseSession删除，其中ProcessName ='test'; 
  

同时从任何其他事务中为同一表中的差异行运行相同的删除操作

[SQLServer JDBC驱动程序] [SQLServer]锁定请求超时时间已超出。嵌套的异常是java.sql.SQLException：[newscale] [SQLServer JDBC驱动程序] [SQLServer]超出了锁定请求超时期限。 SQL的未分类SQLException [从DefDatabaseSession WHERE ProcessName =吗？ SQL状态[HY000];错误代码[1222]； [newscale] [SQLServer JDBC驱动程序] [SQLServer]超过了锁定请求超时期限。嵌套的异常是java.sql.SQLException：[newscale] [SQLServer JDBC驱动程序] [SQLServer]锁定请求超时期限已超过。

解决方案

此处的原因是优化器忽略了行锁提示[WITH（ROWLOCK）为优化器提供了查询提示]。这种情况将在您遇到大量行的情况下发生，在这种情况下，优化器发现更可行的方法是在表上进行堆扫描并因此获得表锁。 p>有关详细讨论，您可以转到此链接： http://social.msdn.microsoft.com/Forums/sqlserver/ zh-cn / US / 60238304-04e8-4f98-84d1-3ddf1ed786a9 /为什么在更新语句中使用行锁锁定整个表

I update one row of a table,using WITH (ROWLOCK), but by executing "sp_lock" I can see that the entire table is locked. So, before the transaction is committed, other transactions can not update the other rows of the table. why "WITH (ROWLOCK)" doesn't take effect?

I am using the below query with rowlock:

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

in same time from any other transaction running the same delete operation for difference row in same table I am getting the exception

[SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.; nested exception is java.sql.SQLException: [newscale][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.: com.newscale.bfw.udkernel.kernel.UdKernelException: udconfig.defdbsession.delete; uncategorized SQLException for SQL [DELETE FROM DefDatabaseSession WHERE ProcessName = ?]; SQL state [HY000]; error code [1222]; [newscale][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.; nested exception is java.sql.SQLException: [newscale][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.

解决方案

The reason here is that optimizer is ignoring your row lock hint [WITH (ROWLOCK) provides a query hint to the optimizer]. This will happen in situations where you're hitting a very large number of rows , in such scenarios optimizer find it more feasible to heap scan on your table and hence obtain table lock.

For a detailed discussion you can go to this link: 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

这篇关于为什么在“ with（rowlock）”时锁定整个表？用于更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！