ROWLOCK on sql server

Question

I have a table for updating without 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 ROWLOCK function, then? I used it in a select statement, but still blocked the entire table ... so annoying!

Answer 1

If you select too many rows, Sql Server will increase the lock to the table level. Instead, you can click on the cap with the possible number of locks or memory

OR

You use the SNAPSHOT isolation level, then you can only use the ROWLOCK hint with the HOLDLOCK or UPDLOCK hints

Update:

You can create your own operator using

 DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS; Here is your statement DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS; 

and see - what locks are performed, held and released during the run

UPDATED

DBCC TRACEON (-1, 3604, 1200)

BEGIN TRAN

UPDATE [Order]

with (ROWLOCK)

SET ProductId = 3

WHERE CustomerId = 1

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

Process 54 receiving IX lock in OBJECT: 16: 229575856: 0 (bit class2000000 ref1): OK

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

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

Process 54 receiving IX lock on PAGE: 16: 1: 196 (bit class 2000000 ref0): OK

Process 54 receiving an X lock on RID: 16: 1: 196: 0 (bit class 2000000 ref0): OK

Process 54: Release RID: 16: 1: 196: 0 Link

Process 54 that locks U to RID: 16: 1: 196: 1 (bit0 class ref1): OK

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

Process 54 releases the lock link on PAGE: 16: 1: 196

(1 row (s) affected)