Wow, what a response!
I read some strange things in the previous reply.
When dealing with a shared transactual database with many differant user connections executing the same code that accesses the same data there is a hint that needs to be in every select and/or update statement.
WITH (NOLOCK) or WITH(ROWLOCK) or another locking hit that matches the requirements better.
I have developed many DB's where speed is important, and I use almost never hints, because my experience (20+ years) told me that start using those hints always trigger some other problems and to solve these, oh yes, hints again.
There are indeed special cases when you want to elevate the lock.
In this case, you can use hints on the UPDATE statement but NOT NOLOCK or READUNCOMMITTED, that isn't allowed, this means that updating a row always use a writer lock, and the lifetime of a writer lock ends with the end of the enlisted transaction.
If you want that readers aren't blocked by the writers use the read-committed snapshot mode introduced in the yukon engine. Then you still can read all the values without blocking (for the ones that are updating and not committed, you will see the latest committed version)
PS
And using NOLOCK can lead to rare conditions:
* If you have a page-split when reading in this mode, then you can read the same record twice (what will happen then with your bussiness logic?)
* I don't know anymore the precise error, but an error can be thrown in this mode
* And what about ACID, you can read uncommitted records, what will happen if you read records that are rollbacked?
These are described in the great books of Kalen Delaney
My onion is that nolock, readuncomitted should be deprecated, there are a lot of other techniques that are ACID compliant.
Danny