Justin,

I have found that using the UPDLOCK and ROWLOCK hints together never works. The smallest level of granularity I can achieve is (UPDLOCK, PAGLOCK). It appears that UPDLOCK just ignores ROWLOCK completely and locks the whole table.


SQL Server have automatic lock escalation,
out of developer control, this is one serious lack.

However, because SQL Server do not have flat tables,
but only index tables,
then, i think, SQL Server have only index lock, page lock and table lock.

Index lock is applied only when SQL can lock a index row:

* session 1
USE Northwind
BEGIN TRAN
SELECT * FROM dbo.Shippers (UPDLOCK,ROWLOCK) WHERE ShipperID=1

* session 2
USE Northwind
BEGIN TRAN
SELECT * FROM dbo.Shippers (UPDLOCK,ROWLOCK) WHERE ShipperID=2

* session 3
USE Northwind
BEGIN TRAN
SELECT * FROM dbo.Shippers (UPDLOCK,ROWLOCK) WHERE ShipperID=3

Fabio