-
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
= def.g
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|