Using WITH (NOLOCK) on some tables in a JOIN query

Question

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.

Personally, I would not use NOLOCK to improve performance. Index and query tuning is most often the best way to improve performance. — Dan Guzman
– Dan Guzman, Commented Nov 3, 2017 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
– AntDC, Commented Nov 3, 2017 at 10:37

Stanislav Kundii · Accepted Answer · 2017-11-03 10:16:00Z

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

edited Nov 3, 2017 at 10:16

answered Nov 3, 2017 at 9:51

Stanislav Kundii

2,9041 gold badge15 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AntDC Over a year ago

Nice article Tapakah, but am I correct in what I said about not specifying the hint on the Stock table?

Stanislav Kundii Over a year ago

Yep, only table with hint return uncommitted data

AntDC Over a year ago

Little steps. I think I can make my changes quite safely at a sproc level without impacting anything else. Regarding snapshot isolation level, that could be a meeting with the DBAs. Thanks for the info & have a great weekend.

Dan Guzman Over a year ago

I think you mean Sch-S (schema stability) lock. A Schema modification lock is effectively an exclusive lock on the database object.

Stanislav Kundii Over a year ago

Ops, sure Sch-S :)

Collectives™ on Stack Overflow

Using WITH (NOLOCK) on some tables in a JOIN query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related