Why is the whole table locked and "with (rowlock)" is used in the update statement

Question

I am updating one row of the table using WITH (ROWLOCK), but by executing "sp_lock" I see that the whole table is locked. Thus, until a transaction is completed, other transactions cannot update other rows of the table. why does "WITH (ROWLOCK)" not take effect?

I use the following query with rowlock:

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

at the same time, from any other transaction performing the same delete operation for the difference row in the same table, I get an exception

[SQLServer JDBC driver] [SQLServer] The timeout period for request blocking has been exceeded; The nested exception is java.sql.SQLException: [newscale] [SQLServer JDBC Driver] [SQLServer] Lock request timed out: com.newscale.bfw.udkernel.kernel.UdKernelException: udconfig.defdbsession.delete; unclassified SQLException for SQL [DELETE FROM DefDatabaseSession WHERE ProcessName =?]; SQL State [HY000]; error code [1222]; [newscale] [SQLServer JDBC Driver] [SQLServer] Request lockout timeout exceeded; The nested exception is java.sql.SQLException: [newscale] [SQLServer JDBC Driver] [SQLServer] The request lock timeout period has been exceeded.

Answer 1

The reason is that the optimizer ignores your hint of line locking [WITH (ROWLOCK) provides a query hint to the optimizer]. This will happen in situations when you get into a very large number of rows, in such scenarios the optimizer finds it more possible to scan on your table and, therefore, gets a table lock.

For a detailed discussion, you can follow 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

Answer 2

I assume that you do not have an index on ProcessName , so the query should perform a full table scan, therefore all rows are read (and are possible candidates for deletion), therefore it is more efficient to lock the whole table than to lock each row.

Try to determine the index:

 CREATE INDEX DefDatabaseSession_ProcessName ON DefDatabaseSession(ProcessName); 

---

You can find out the query plan by following the explanation:

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