why the entire table is locked while “with (rowloc

问题:

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.

回答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

回答2:

My guess is that you don't have an index on ProcessName, so the query must do a full table scan, thus all rows are read (and are possible candidates for deletion), so it's more efficient to lock the whole table than lock every row.

Try defining an index:

CREATE INDEX DefDatabaseSession_ProcessName ON DefDatabaseSession(ProcessName);

---

You can find out the query plan by doing an explain:

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