Reputation: 561

What is the practical used of nolock in sql server

I was going around some document in msdn and it said that "No shared locks are issued to prevent other transactions from modifying data read by the current transaction".

So in lay man term(i.e mine) this would cause the problem of dirty read. Which is too dangerous, if so then why to used it?

Does anybody knows the practical scenario where it would be used.

Upvotes: 4

Answers (8)

WonderWorker

Reputation: 9062

You are clearly a genius. It should never be used.

NOLOCK is often exploited as a magic way to speed up database reads, but I try to avoid using it whever possible.

The result set can contain rows that have not yet been committed, that are often later rolled back.

An error or Result set can be empty, be missing rows or display the same row multiple times.

This is because other transactions are moving data at the same time you're reading it.

READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.

There are other side-effects too, which result in sacrificing the speed increase you were hoping to gain in the first place.

Now you know, never use it.

Upvotes: 0

David Manheim

Reputation: 2626

We have a table holding a queue that is only ever inserted to and updated; nothing is ever deleted. The rows have different flags that indicate what is occurring with the associated process. The production system only uses rowlocks, but dozens at a a time are held on various rows, so that different processes can occur at once.

We check how many items are still being processed, to avoid overloading the system. When a user launches a new process, it waits to spawn sub-processes until there are fewer than 10 items currently being processesd in the queue.

We need to prevent taking locks on processes that may need them to update their owns status, and we need to see the status of locked items. We use with (nolock) to prevent waiting to see what is going on in the queue, and to count how many items are not yet marked complete - which is guaranteed by the logic to only occur when the process completes.

Upvotes: 1

anon

Reputation:

In our case (previous job) we used this for getting ballpark numbers. So for example a table that holds millions of e-mails sent in a given day, if at 5:00 we want to see "where we are" we can say:

SELECT COUNT(*) FROM dbo.MessageTable WITH (NOLOCK)
  WHERE CampaignID = x AND CustomerID = y;

A lot of people will suggest using this for COUNT(*) with no WHERE clause. I would argue that if you're willing to accept some inaccuracy in COUNT(*) you may as well do:

SELECT SUM(rows) FROM sys.partitions
  WHERE [object_id] = OBJECT_ID('dbo.tablename')
  AND index_id IN (0,1);

This number is similarly inaccurate due to in-flight transactions, but doesn't actually have to scan the table, so it is far more efficient. For our case we could use this even for subsets: with a filtered index in place (for other purposes) we could similarly query sys.partitions but use the index_id of the filtered index.

In most cases, though, using NOLOCK may feel like a turbo button, but the inaccuracy it may cause is very rarely worth it. Unless your system is already heavily tempdb-bound, you should consider READ_COMMITTED_SNAPSHOT for your current NOLOCK scenarios instead. See Pros and cons of READ_COMMITTED_SNAPSHOT

Upvotes: 5

Arvo

Reputation: 10570

Quite easy for us - we fetch non-financial (and usally rarely changed) data (like customer address or goods description or various configuration options) with nolock hint, financial or quantitative data (like prices or balances) with "normal" locking.

Upvotes: 0

Josh Smeaton

Reputation: 48720

Not all data is created equal. Quite a lot of data isn't actually critical, and so it is ok, in some situations, to have dirty reads going on. For instance, a batch process could be bulk updating a number of different tables. You may want to read the the number of Read Messages for a user. You'd prefer if the number was out by 1 or 2 rather than the user having to wait seconds, minutes, or hours for the locks to become available.

In other words, you're increasing concurrency when the exact data isn't actually required, and it's ok to have some (potentially) invalid data.

Upvotes: 1

Greg

Reputation: 3522

we use it on tables that have lots of reads, but few writes. If the connections are just reading data then this is often no danger in doing dirty reads. This prevents blocking on the tables which increases performance.

Check out https://stackoverflow.com/a/1453000/1038940 for some more info about nolock and when it can be good/bad

Upvotes: 0