0

I've read about this and transaction isolation level and was just wondering.....

I have some queries that query against products, categories, locations & stock. Now the products, categories and locations data rarely changes, but as you can imagine, stock does. Is it reasonable to specify WITH (NOLOCK) on products, categories, locations but not specify it on Stock? I've done some timings and it does seem to improve performance so I'm cautiously happy.

My understanding is that if I don't specify WITH (NOLOCK) on Stock, that should only get committed transactions and not have dirty reads. Whilst, as the other tables contents rarely change, it's safe to specify WITH (NOLOCK).

The specific proc I am working on doesn't write to any of the mentioned tables....it puts some results into a #table and returns rows from that.

Your thoughts appreciated.

T.S.
  • 18,195
  • 11
  • 58
  • 78
AntDC
  • 1,807
  • 14
  • 23
  • 1
    Personally, I would not use NOLOCK to improve performance. Index and query tuning is most often the best way to improve performance. – Dan Guzman Nov 03 '17 at 10:17
  • I hear you Dan, but sometimes we can only alter what we can control ;) I get the pros and cons...in the case mentioned, it's quick, easy and safe given the usage of the tabkles involved. Long term, you are correct, but for now... – AntDC Nov 03 '17 at 10:37

1 Answers1

1

With NOLOCK put only lock Sch-S (schema stability)

Without NOLOCK S lock + Sch-S But NOLOCK give READ UNCOMMITTED data (dirty reads) which will give you incorrect data.

https://sqlstudies.com/2015/03/18/why-not-nolock/

Use RSCI (Read Committed Snapshot Isolation) row-versioning implementation of the read committed isolation level https://sqlperformance.com/2014/05/t-sql-queries/read-committed-snapshot-isolation

Stanislav Kundii
  • 2,874
  • 1
  • 11
  • 17