What are the problems with using NOLOCK given an approach to handle the short comings?

Question

I am researching the harm of using NOLOCK SQL to load data from one database that is actively used into a reporting database. I understand that there are problems with using NOLOCK but I am thinking of ways to counter them using the strategy explained below.

I understand that there are better ways like log shipping, replication, mirroring, AG, clustering to have a replica database, but those are not the point of this question.

The target DB has a history table that holds the LoadDate. Every 10 minutes, the scheduler runs a SELECT query (with NOLOCK) with WHERE clause based on a timestamp to fetch the data, dump it into a staging table, remove duplicates if any (keep latest) and merge it into the target table.

The two tables that are used in the SELECT query may be concurrently modified when the SELECT query is running, but the join criteria's column values won't change.

Example pseudocode:

DECLARE @LASTLOADDATE=SELECT MAX(LOADDATE) FROM HISTORYTABLE;
DECLARE @CURRENTDATE=GETDATE();

SELECT C1,C2,C3
FROM TBL1 T1 WITH(NOLOCK)
JOIN TBL2 T2 WITH(NOLOCK) ON T1.ID=T2.T1_ID
WHERE T1_TIMESTAMP>@LASTLOADDATE AND T1_TIMESTAMP<=@CURRENTDATE
//There is no index on timestamp column but an index may be added in the future

INSERT THE ABOVE RECORDS INTO STAGING TABLE
REMOVE DUPLICATES
MERGE DATA FROM STAGING TABLE INTO TARGET TABLE
INSERT INTO HISTORY TABLE THE VALUE FROM @CURRENTDATE

1. NOLOCK results in dirty reads. But there are no transactions in my database/application so this is not an issue.

2. NOLOCK causes non-repeatable reads and phantom reads, which is also fine because my SELECT query is not running inside a transaction.

3. Returning duplicates (because of page splits and allocation order scan) is not a problem because I stage the data, use row_number partition by timestamp field to keep the latest record and then do a SQL MERGE with target table. So partitioning and picking latest record handles the duplicates issue. Alternatively, this record will have its timestamp updated (because it was updated after the SELECT is run), so the latest value will get picked up by the subsequent scheduler run anyways.

4. Missing records (because of allocation order scan) can happen when select has read past a point after which a record is inserted (or caused to get inserted due to an update) prior to that point. When the query runs, in this scenario it will miss the record. But since the records have a timestamp then it will get picked up in the next run.

5. Corruption issues happen only when NOLOCK is used with INSERT/UPDATE, whereas I am using only SELECT so this is not an issue.

Is any of my explanations incorrect and are there any other problems that I fail to see?

Answer 1

Whenever you are reading data that might be concurrently changed by other processes, you have to decide what level of transaction isolation you need.

Every isolation level below serializable comes with trade-offs. It is up to the implementer to decide which level is appropriate in the circumstances. This applies even if you are not using explicit transactions. Even a single SELECT statement is a transaction.

The question indicates you have given some thought to those issues, which is good.

Using the summary from my article on the read uncommitted isolation level, the main inconsistencies specific to read uncommitted are:

• Dirty reads (encountering data that has not yet, and might never be, committed)
• Rows containing a mixture of committed and uncommitted data
• Missed/duplicate rows due to allocation-ordered scans (see also this archived post)
• Mixed-state ("corrupt") individual (single-column) LOB values
• Error 601 – "could not continue scan with NOLOCK due to data movement"

This is in addition to inconsistencies possible under locking read committed:

• Missing previously committed rows
• Committed rows encountered multiple times
• Different committed versions of the same row encountered in a single statement/query plan
• Committed data from different points in time in the same row (but different columns)
• Committed data reads that appear to contradict enabled and checked constraints

The question does not contain enough detail to evaluate all of these. For example, we do not know if there are any large LOB data types returned by the query, or if any data might be stored off-row in ROW_OVERFLOW allocation units.

Still, based on what has been provided, it seems that READ UNCOMMITTED might be suitable for your needs. The possible inconsistencies seem to be tolerable given the de-duplication, reacquiring any previously missed data, and so on.

Dirty reads might be a problem for you if it is possible to add data that is then removed by a rollback (for example, an error condition), and never re-added—or if timing is important.

Only you can really determine whether any of these things are a concern for your application or not. Read uncommitted provides the fewest isolation guarantees of any level available in SQL Server, but that does not mean it is never the right choice. Bear in mind the future risk that someone fails to respect the assumptions making read uncommitted potentially acceptable to you now.

Row-versioning isolation levels like RCSI and SI are a popular alternative, if your system and workload can tolerate the overhead involved.

---

Addressing the numbered points in your question directly:

1. Every statement runs in a transaction.
2. Your select is running inside an auto-commit transaction.
3. This is fine if the two reads do in fact return exact duplicate rows in all possible cases.
4. Also ok, if it truly does not matter when a row is added, just that it is eventually.
5. Your select (obviously) won't 'corrupt' the table(s) you are reading from, but if you read 'corrupt' data and then store that somewhere, the effect is much the same?