Effect of NoLock in 'Update...From' statement in SQL Server?
Recently, a friend argued with me that Update...From statements should not
have NoLock and wont have any effect, since Update statement must and will
lock the updated table.
e.g. Update abc Set e = h From abc inner join def on abc.d = def.g
But my understanding is, Update...From, internally, are two individual statements
(Update and Select). So, all the NoLock related issues referred to in a Select
statement will be applicable, for the above statement (ie. a shared lock
will be placed and removed for the tables in the from clause).
So, can someone explain the inner mechanics of Update...From statement and
the difference (in terms of locks) between the following statements. How
does the performance difference and efficiency compare between them?
1) Update abc Set e = h From abc inner join def on abc.d = def.g
2) Update abc Set e = h From abc inner join def (NoLock) on abc.d = def.g
3) Update abc Set e = h From abc (NoLock) inner join def (NoLock) on abc.d