Santa Ana (3/31/2009)
Does the WITH (NOLOCK) option work with sql server 2005 ?
Yes
I do not understand why and how can this happen? ( a select with nolock is not locking anything at all, how does it participate in a dead lock situation ? )
You're going to have to show us the code to be sure, but I have before seen deadlocks resulting from schema locks as a result if enabling and disabling triggers and even selects with a nolock have to respect a Sch-M lock.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability