WITH ROWLOCK Hint and VIEWS

External Since: Oct 15, 2010 Posts: 1

We use the WITH ROWLOCK hint when we are updating a single row in a
table. We use this for both tables and views when a single row is
being updated. We are running into issues where the WITH ROWLOCK on a
VIEW is escalating to a page lock, which is the exact behavior that
WITH ROWLOCK is supposed to prevent.

One of the DBA's is speculating that the issue is that SQL Server is
somehow messing up the ROWLOCK hint on a view. Does that seem at all
reasonable? I've never heard anything like that before.

External Since: May 30, 2004 Posts: 1649

Steve ( ) writes:
> We use the WITH ROWLOCK hint when we are updating a single row in a
> table. We use this for both tables and views when a single row is
> being updated. We are running into issues where the WITH ROWLOCK on a
> VIEW is escalating to a page lock, which is the exact behavior that
> WITH ROWLOCK is supposed to prevent.

If you update a single row, and you get anything else but a row-level lock,
you are missing an index.

> One of the DBA's is speculating that the issue is that SQL Server is
> somehow messing up the ROWLOCK hint on a view. Does that seem at all
> reasonable? I've never heard anything like that before.

Without knowledge of views, table and indexes, it is impossible to tell.
What I do know is that if you use hints with objects on linked server, the
hints are silently ignored.

--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx