Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

2 Answers 2

up vote 8 down vote accepted

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.

share|improve this answer
    
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." –  Nick Chammas Jan 19 '12 at 4:13

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

share|improve this answer
    
if it is by design to lock on page/table then why even bother to do a row lock? –  ronald_yoh Jan 24 '12 at 4:51
    
@ronald_yoh Please, read the BOL about intent locking –  Oleg Dok Jan 24 '12 at 4:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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