why the entire table is locked while "with (rowlock)" is used in an update statement

Question

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.

Answer 1

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