Is row level forced locking possible in SQL Server?

Question

I see how to disable row level and page level locking in SQL Server, but I cannot find a way to force SQL Server to use row level locking. Is there a way to force SQL Server to use row-level locking and NOT use page-level locking?

Answer 1

You can use the ROWLOCK hint, but AFAIK SQL can solve its escalation if it runs on resources

From doco :

ROWLOCK Indicates that row locks when page or table locks are generally accepted. When specified in a transaction acting on the SNAPSHOT isolation level, row locks fail unless ROWLOCK is combined with other table hints that require locks such as UPDLOCK and HOLDLOCK.

and

Block hints ROWLOCK, UPDLOCK and XLOCK which may take locks at the level of lock lines on index keys, and not on actual data lines. For example, if a table has a non-clustered index, and a SELECT call using the lock hint is processed by the coverage index, the lock acquired on the index key into the enclosing index, and not the data row in the base table.

And finally, this gives a fairly detailed explanation of the escalation lock in SQL Server 2005, which was changed in SQL Server 2008.

There is also the deepest: Lock in a database block (in books online)

So, generally speaking,

UPDATE Employees WITH (ROWLOCK) SET Name='Mr Bean' WHERE Age>93 

This should be fine, but depending on the indexes and load on the server, escalation may end before the page is locked.

Answer 2

Use the ALLOW_PAGE_LOCKS ALTER / CREATE INDEX clause:

 ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = OFF); 

Answer 3

You cannot force the optimizer to do anything, but you can do it.

 UPDATE Employees WITH (ROWLOCK) SET Name='Mr Bean' WHERE Age>93 

See - Managing SQL Server with Lock and Tips