4

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)

0

2 Answers 2

8

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.

1
  • From the linked page: "An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy." Jan 19, 2012 at 4:13
0

Its all OK - only ROWLOCK was taken over updated rows, and as I said in your previous post - Intent locks over PAGE and TABLE also held - its by design

2
  • if it is by design to lock on page/table then why even bother to do a row lock?
    – ronald_yoh
    Jan 24, 2012 at 4:51
  • @ronald_yoh Please, read the BOL about intent locking
    – Oleg Dok
    Jan 24, 2012 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.