NOLOCK with Multithreading

Question

I am working on a multithreaded application (C#), and 2 threads are updating a table using NOLOCK at the same time, is that a problem? To be more specific, they are both updating the same records.

Answer 1

The answer is "it depends".

NOLOCK allows 'dirty reads'. This means that within a transaction, you may see uncommitted data from another transaction. If you have multiple threads updating the same row in the same table, you may see the modified value of data that the other thread touched, before that thread commits it's transaction.

For example, take the table account_balances, defined as (account_num int, balance decimal(12,2)). Let's assume the following happens:

// precondition, account #1 has a balance of 10.00

1. Thread #1 starts a transaction, decrements account #1 by 10
2. Thread #2 starts a transaction, attempts to read the balance of account #1. It reads a balance of 0.
3. Thread #2 decrements the account by $5, and issues an overdraft to the customer (their balance is -5)
4. Thread #1 rolls back it's transaction
5. Thread #2 commits it's transaction

// The account balance is now -5, even though it should be 5.

What you won't see is some form of inconsistent data within a field- the nolock hint isn't like running multi-threaded code without a lock- individual writes are still atomic.

Answer 2

It means that you can get records that are in an 'erroneous' state.

For example...

• Process1 is deleting a block of data
• Process2 is reading an overlapping block of data, WITH NOLOCK

In an ideal situation, either all the records being deleted by Process1 are either present or deleted. Because Process2 is using NOLOCK, it may read some of the records Process1 is deleting, but not others because they've already gone.

The same goes for INSERTS and UPDATES. You may simply read records that are only Part of the way through being altered in some way.

Whether this is a problem depends on your data, your design, etc.

A search engine won't care if this happens. A Bank dealing with financial transcations will.