How to lock a Row while I'm using it

Question

I want to be able to Lock a row, select it, increment its value, and then release the lock. (without lockin the other rows, so that other connections can work with the rest of the table)

I've found this

BEGIN TRAN SELECT * FROM tablename WITH (HOLDLOCK, ROWLOCK)
WHERE ID = 1

My problem is I cant do

UPDATE tablename
SET columnName = -1
WHERE ID = 2

until I commit my previous transaction, why is rowlock locking the whole table?

edit:

Does this code guarantees that the data of the selected row is not update during this update command?

UPDATE [tablename] WITH (ROWLOCK)
SET columnName = columnName + 5
WHERE ID = 1

Answer 1

Based on your edit, all writes lock exclusively.

No other process can read the row(s) being written unless

• you've been silly and are using NOLOCK
• you have snapshot isolation where the last committed row is read

There is usually no need to add lock hints in day to day code.

As to why you (apparently) have a table lock, this can be caused by no index on ID. The table is being locked because all rows have to be looked at.

Answer 2

The ROWLOCK isn't locking the table, but your TRANSACTION is.

You should be able to do the update from within the same transaction. If you are trying to do the UPDATE from another session it won't work.