GSquared (3/31/2009)
On the question about nolock selects being deadlocked, first, they can be deadlocked by something else that's locking resources they need, second, if the select is part of an update/delete/insert, nolock won't actually do what you expect.
Thank you so much for the detailed reply GSquared. Your answers cleared many of my doubts but one last one 🙂
I was little confused over this, when i say select with nolock, i was really believing that this select statement WILL NEVER BE a participant of a DEADLOCK because the rows that are selected by this transaction are not locked by this select transaction.
I was assuming that a nolock select actually means :- select my data regardless of any type of lock** that is imposed on it(data).
** = Lock for this select/lock for any other update if happening at the same time
but from your reply, i infer
nolock on select = SQL server will not impose locks that it will usually do on selects, period. (a nolock select doesn't mean that the other locks-update/insert/delete will be ignored too)
Is the above true ?
Thanks again!!