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.