6

I'm making a self project in C#.Net, by using a 3-tier app, I wan't to make my sql statements transactional, these statements are used in several stored procedures, I've been using the WITH(NOLOCK) approach in order to query those tables that have been used for inserts or updates during the transaction, and on the C# side I've been using TransactionScope, but I recently read that using WITH(NOLOCK) is not recommended because it can lead to phantom reads or using dirty and inconsistent data. My question is, in order to use data that has been inserted or updated during a transaction, when it comes to a select, What is the best approach in terms of transactional operations?, whether it is database side or business code side.

Daniel Kelley
  • 7,579
  • 6
  • 42
  • 50
jecarfor
  • 498
  • 8
  • 22
  • 1
    I think it's better to ask this on DBA community if you want comprehensive answer. http://dba.stackexchange.com/ – Evaldas Buinauskas Oct 05 '15 at 14:09
  • 5
    The best "alternative" is simply to remove it. Any data movement inside a transaction is still visible within that transaction. – Sean Lange Oct 05 '15 at 14:09
  • Consider using snapshot isolation if wait-free reads are critical to you; but it comes with its own bag of worms so be careful to research it first. – Cory Nelson Oct 05 '15 at 14:34

4 Answers4

4

Have you considered snapshot isolation? It provides perfect read consistency and does not take locks on data at all.

SI is standard on many RDBMSes and default on. Not sure why SQL Server people are so hesitant to use it. The drawbacks are mild, yet you need to research them.

usr
  • 168,620
  • 35
  • 240
  • 369
3

You simply don't need it. If you modify some data and later query it in the same transaction, you will get the modified data.

Your transaction will hold an exclusive lock on the modified data, so it can do anything with it - query, modify again etc.

If you specify WITH(NOLOCK), you allow your query to ignore exclusive locks from OTHER transactions. It will cause your queries to return incorrect data.

Jakub Lortz
  • 14,616
  • 3
  • 25
  • 39
3

Maybe you don't know what you want.

The WITH(NOLOCK) returns dirty and inconsistent data.

If you want clean data, you must remove this clause, and wait eventual concurrent updates on the table you want to read.

Sergio Internicola
  • 372
  • 1
  • 7
1

Link: https://learn.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-ver15#remarks

The following table shows the concurrency side effects allowed by the different isolation levels.

enter image description here

Read from the above link and understand the meaning of each of the column headers.

Most database have the default setting as Read committed. So when you fire the query with or without NOLOCK, and assuming your database is in the default Read committed mode, you are faced with the problems of non-repeatable read and Phantoms.

When you use NOLOCK, you additionally also face dirty reads problem. This means that you are able to read the modifications made by transactions even before they are committed or rolled back.

There is also the problem of missing rows (for example assuming your select query has read past the updated row), and duplicate rows (for example assuming the row that has already been read has been updated to a value that isn't yet read by the select query). NOLOCK results in an allocation order scan rather than an index order scan which can cause the missing or duplicate rows problem.

You need to think pragmatically about how the above issues affect you and decide whether to use the NOLOCK hint.

Here is an example: https://dba.stackexchange.com/questions/306994/what-are-the-problems-with-using-nolock-given-an-approach-to-handle-the-short-co

Following answer gives an example about reading certain rows twice: https://stackoverflow.com/a/2268078/1779091 (answer is pasted below)

NOLOCK hint causes dirty read anomallies, and one such anomaly is a duplicate read. Such reads are frequent if an update changes the position of the row in the index scanned by the query:

say you have 2 rows in the table, with an ID key, rows with key values 1 and 2 one request (T1) runs UPDATE table SET key=3 WHERE key=1;

second request (T2) runs SELECT ... FROM table WITH(NOLOCK);

T1 locks the row with key value 1

T2 ignores the lock T1 has and reads the row with key value 1

T2 continue and reads row with key value 2

T1 update the row, and the row is moved in the index int he new position for key value 3

T2 continues to scan and reads the row with key value 3

So the SELECT has read a row twice, once while it had the key value 1 and once while it had a key value 3. This is just a trivial example of what can happen. In reality more complicated queries can run complex plans and use other indexes, all subject to such anomalies.

In short: NOLOCK hint is evil. If you want to avoid contention, use snapshot isolation.

variable
  • 8,262
  • 9
  • 95
  • 215