SQL Server Table locks in long query - Solution: NoLock?

Question

a report in my application runs a query that needs between 5 - 15 seconds (constrained to count of rows that will be returned). The query has 8 joins to nearly all main-tables of my application (Customers, sales, units etc).

A little tool shows me, that in this time, all those 8 tables are locked with a shared table lock. That means, no update operation will be done in this time.

A solution from a friend is, to have every join in the query, which is not mandetory to have 100% correct data (dirty read), with a NoLock, so only 1 of this 8 tables will be locked completly. Is that a good solution? For a report in which 99% of data came from one table, unlock the less prio tables?

Answer 1

NOLOCK means placing no locks at all.

Your query may returns portions of data as of before UPDATE and portions as of after UPDATE in a single query.

Like, a debit without a credit and these kinds of stuff.

For instance, I just ran this query on a large table:

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18874367

All name's have length of 1.

Then I reran it and in the middle of the query updated the table:

UPDATE  master
SET     name = 'tt'
WHERE   id <= 10000

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18874944

As we can see, this query noticed 577 rows as updated (length 2), all other rows as not updated (length 1).

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18884367

And this query, run right after the previous one finished, sees all updates.

Answer 2

Try looking at READ COMMITTED SNAPSHOT rather than NOLOCK. This means the data could be "old" but will never be dirty.

Answer 3

It's okay, as long as you place a very strong emphasis on this phrase:

which is not mandetory to have 100% correct data (dirty read)

So you probably don't want to add a nolock hint to your sales table, but your customers table (which likely sees fewer changes) might be okay. Even there, you're probably not using that many customer records in a single query, but if one does change it could be a big problem. So you might want to specify the rowlock hint for that table.