Is it possible to force row level locking in SQL Server?
Sql ServerSql Server-2005Sql Server-2008RowlockingSql Server Problem Overview
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?
Sql Server Solutions
Solution 1 - Sql Server
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.
Solution 2 - Sql Server
Use the ALLOW_PAGE_LOCKS clause of ALTER/CREATE INDEX:
ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = OFF);
Solution 3 - Sql Server
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
See