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?
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