I can see how to turn off 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?
This question is related to
sql-server
sql-server-2005
sql-server-2008
rowlocking
You can use the ROWLOCK hint, but AFAIK SQL may decide to escalate it if it runs low on resources
ROWLOCK Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.
and
Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.
And finally this gives a pretty in-depth explanation about lock escalation in SQL Server 2005 which was changed in SQL Server 2008.
There is also, the very in depth: Locking in The Database Engine (in books online)
So, in general
UPDATE
Employees WITH (ROWLOCK)
SET Name='Mr Bean'
WHERE Age>93
Should be ok, but depending on the indexes and load on the server it may end up escalating to a page lock.
You can't really force the optimizer to do anything, but you can guide it.
UPDATE
Employees WITH (ROWLOCK)
SET Name='Mr Bean'
WHERE Age>93
Use the ALLOW_PAGE_LOCKS clause of ALTER/CREATE INDEX:
ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = OFF);
Source: Stackoverflow.com