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.
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
NOLOCK results in dirty reads. But there are no transactions in my database/application so this is not an issue.
NOLOCK causes non-repeatable reads and phantom reads, which is also fine because my SELECT query is not running inside a transaction.
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.
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.
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?