Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Development Tools  Other Development Tools  with (NOLOCK) vs. SET TRANSACTION ISOLATION LEVEL

Author  Topic  adude28 Starting Member 1 Post Posted - 2005-08-23 : 21:58:24 I wondering which one of the following I should use to get the best performance. 1. "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" or 2. "WITH (NOLOCK)" I notice that when I use the #1 "SET TRANSACTION..." it sets a lock Mode type of "Sch-S" (Schema stability Lock) which described by SQL Books Online as "Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks" When I use #2 "WITH (NOLOCK)" it returns a lock type mode of "IS" (Intent shared) which is explained as: "[IS lock] Indicates the intention of a transaction to read some (but not all) resources lower in the hierarchy by placing S locks on those individual resources." it later explains an "S" lock: "No other transactions can modify the data while shared (S) locks exist on the resource." This is how I tested bot executed both: strSQL = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" strSQL = strSQL & "SELECT intNumVisitors FROM HitCounter;" set objRS = objConn.execute(strSQL) OR strSQL = "SELECT intNumVisitors FROM HitCounter WITH (NOLOCK);" set objRS = objConn.execute(strSQL) I thought that "WITH (NOLOCK)" was supposed to do exactly what "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" does but that doesn't seem to be the case? What's up??? I am confused! Can someone clarify why this appears to be the case. Thanks, Ed