Is it possible to force row level locking in SQL Server?

by

You can use the ROWLOCK hint, but AFAIK SQL may decide to escalate it if it runs low on resources

From the doco:

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.

More Related Contents:

  1. SQL Server query to find all permissions/access for all users in a database
  2. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  3. Why use the INCLUDE clause when creating an index?
  4. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  5. Regular Expressions in SQL Server servers?
  6. SQL Server GUID sort algorithm. Why?
  7. What happens to the primary key Id when it goes over the limit?
  8. Drop primary key using script in SQL Server database [duplicate]
  9. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  10. SQL Server recursive query
  11. nvarchar(max) vs NText
  12. INFORMATION_SCHEMA vs sysobjects
  13. “Prevent saving changes that require the table to be re-created” negative effects
  14. What are row, page and table locks? And when they are acquired?
  15. Table Valued Function Killing My Query Performance
  16. What is the best way to create and populate a numbers table?
  17. How to avoid the “divide by zero” error in SQL?
  18. Infinite loop CTE with OPTION (maxrecursion 0)
  19. SQL Server IN vs. EXISTS Performance
  20. How Can I Log and Find the Most Expensive Queries?
  21. Real life example, when to use OUTER / CROSS APPLY in SQL
  22. PIVOT in sql 2005
  23. Atomic UPSERT in SQL Server 2005
  24. SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
  25. How to update primary key
  26. error when inserting into table having instead of trigger from entity data framework
  27. How to query values from xml nodes?
  28. Case sensitive variables in SQL Server
  29. How to query for Xml values and attributes from table in SQL Server?
  30. How to find the boundaries of groups of contiguous sequential numbers?

Leave a Comment