ROWLOCK in sql server

I have a table to update with no indexing in the where clause so I use ROWLOCK in the update script, hoping to get row lock instead of table lock but no luck.. so what is the function of the ROWLOCK then? I was using it in the select statement but still locking the entire table... so annoying!


DBCC TRACEON (-1, 3604, 1200)


BEGIN TRAN


UPDATE [Order]


with (ROWLOCK)


SET ProductId = 3


WHERE CustomerId = 1

TRACE OUTPUT:


DBCC execution completed. If DBCC printed error messages, contact your
system administrator.


Process 54 acquiring IX lock on OBJECT: 16:229575856:0 (class
bit2000000 ref1) result: OK


Process 54 acquiring IU lock on PAGE: 16:1:196 (class bit0 ref1)
result: OK


Process 54 acquiring U lock on RID: 16:1:196:0 (class bit0 ref1)
result: OK


Process 54 acquiring IX lock on PAGE: 16:1:196 (class bit2000000 ref0)
result: OK


Process 54 acquiring X lock on RID: 16:1:196:0 (class bit2000000 ref0)
result: OK


Process 54 releasing lock reference on RID: 16:1:196:0


Process 54 acquiring U lock on RID: 16:1:196:1 (class bit0 ref1)
result: OK


Process 54 releasing lock on RID: 16:1:196:1


Process 54 releasing lock reference on PAGE: 16:1:196


(1 row(s) affected)

You ARE getting row locks!

See Understanding Locking in SQL Server to understand why a ROWLOCK update must acquire hierarchical intent locks, for an explanation of the Process 54 acquiring IU lock on PAGE: 16:1:196 (class bit0 ref1) result: OK.

StackExchange Database Administrators Q#10923, answer score: 8