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

| improve this answer | |
  • tks, the problem was I had no index created. – RagnaRock Mar 2 '12 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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