Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
tks, the problem was I had no index created. –  RagnaRock Mar 2 '12 at 15:12

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.

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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