6

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
1
  • Do you have an index on ID?
    – gbn
    Mar 2, 2012 at 14:36

2 Answers 2

6

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.

1
  • tks, the problem was I had no index created.
    – RagnaRock
    Mar 2, 2012 at 15:12
1

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.

2
  • how can I select a row, and modify it, without bloking the rest of the table?
    – RagnaRock
    Mar 2, 2012 at 13:59
  • Use transactions isolation, for example snapshot isolation. Mar 2, 2012 at 14:17

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.