Menu
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?
846
If you don't need to omit any rows, you can use SQL Server's TOP clause to limit the rows returned. It is placed immediately after SELECT. The TOP keyword is followed by integer indicating the number of rows to return. In our example, we ordered by price and then limited the returned rows to 3.
ROW EXCLUSIVE is the same as ROW SHARE , but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting. See ROW SHARE . SHARE permits concurrent queries but prohibits updates to the locked table.
Row-level locking systems can lock entire tables if the WHERE clause of a statement cannot use an index. For example, UPDATES that cannot use an index lock the entire table. Row-level locking systems can lock entire tables if a high number of single-row locks would be less efficient than a single table-level lock.
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.
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
