CODEWITHSUNDEEP
.netsqlsql-servermultithreadingt-sql
fbhdev
fbhdev

Reputation: 526

NOLOCK with Multithreading

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.

Upvotes: 5

Views: 673

Answers (4)

paparazzo
paparazzo

Reputation: 45096

Be careful how you use this. Potential data inconsistencies.

If one query is set colA = "newColANewvalue" from tableA with (nolock) where colA = "colAOldvalue"

And another query is set colC = "colCnewValue" from tableA with (nolock) where colA = "colAOldvalue"

You can end up with with colC = "colCnewValue" and colA = "newColANewvalue" on the same record. If that is not a problem then OK.

What is driving this? Are you getting performance issues with rowlock or pagelock?

Upvotes: 0

Chris Shain
Chris Shain

Reputation: 51329

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.

Upvotes: 4

Oleg Dok
Oleg Dok

Reputation: 21766

You cannot change the same record simultaneously, even if using NOLOCK hint.

BUT

You can bring the data into inconsistent state.

AFAIK - it is not possible to apply this hint to target table of update.

And - this hint allows you to read uncommitted data, not overwrite them.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

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.

Upvotes: 2

Related Questions