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
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL