Permanent row-level locking in SQL Server

Question

I'm getting very poor performance on a stored procedure that does well over a hundred (!) updates to a couple of very small tables and it appears that all the concurrent users are constantly blocking each other.

A full rewrite of the proc is scheduled for later this year, but in the meantime I wanted to see if we can alleviate the problem by forcing row-level locking on each of the affected tables.

On Sybase, you can (or at least could circa 2007) force row-level locking for a table using this statement:

alter table titles lock datarows

On SQL Server, it appears that the only way to get the same effect, is to use WITH (ROWLOCK) on every update or insert statement. And even then, it's only a hint that may be ignored.

Is there a way in SQL Server to force (or strongly favour) row-level locking for all updates to a given table?

Answer 1

First and foremost make sure the updates are not table scans. In other words you do have the proper indexes (UPDATE needs indexes too...). After you ensure that, upon careful consideration, disable page locks on the index used:

ALTER INDEX ... WITH (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);

Give this operation the same care you would give in joggling a barrel of TNT and a bottle of Napalm...

---

Additional information (from the comments below):

• You can disable page locks on the clustered index, but you cannot disable page-locks on heaps (since they're physically structured and would not be possible to do proper lock hierarchy w/o locking the page).

• Lock escalation is a (related, but) different subject. Locks escalation only comes into picture if the statement chooses row-level lock granularity and it decides to escalate during execution to rowset granularity level. Is true that the OP may be in fact victim of escalation, from how I read the OP I think the more likely cause is just lack of an index (ie. a high lock granularity is chosen upfront because of the scan, no escalation is triggered).

• The initial granularity level is the result of the engine estimation of the task at hand. If the estimate indicates a large number of rows will have to be locked then it may opt for page granularity instead, since acquiring a large number of row locks is usually problematic. Lack of an index will trigger a scan, which will usually choose page granularity.

• Also escalation is from row/page to rowset (object) granularity. Going through the intermediate page level first is riddled with concurrency problems so the 'big-hammer' is used.