aviadavi (3/3/2013)
OK.chance that NOLOCK will be ignored is other thing than NOLOCK to be harmful. so I can keep this NOLOCK anyway, what do I have to lose here?
In this specific case, maybe nothing. But if you are using here and are as nonchalant about using it as you seem to be then if you do not change your approach towards NOLOCK you have a lot to lose, e.g. the confidence of your users, someones life if you're working in a medical system, a ton of money of you're working in a financial system, all of which could lead to the loss of your job or worse.
How do you suggest to update rows with a mode of "I don't care if few rows where not updated" - something like "readpast" .
do you suggest to use temporary table for that that will have the data by using select with (READPAST, ROWLOCK, UPDLOCK) and than update it?
I also do not condone the use of READPAST; I place it in the same category as NOLOCK. If you're this bent on allowing dirty reads or skipping rows then look into enabling the READ_COMMITTED_SNAPSHOT isolation mode.
Using a temp table to select [potentially dirty] rows using NOLOCK and READPAST and then joining that temp table back to the base table to do the update may help you avoid some issues but writers will always block writers. So, like I said, even if your logical filters are mutually exclusive, if you incur any page splits on update you could still encounter some blocking during concurrent updates even with a ROWLOCK hint.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato