We have some strange issues appearing in our Production system and we think it comes down to a stored procedure (SQL Server 2019) that employs an INSERT INTO ... SELECT statement.

Here is a sample snippet of the code identical to what we have coded currently

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION
SAVE TRANSACTION Tran1

DELETE FROM Tab1 
WHERE CurrentDate = '01-Jan-2024'

INSERT INTO Tab1 (col1, col2, col3)
    SELECT cola, colb, colc
    FROM Tab2
    INNER JOIN Tab3 ON (Tab3.Cola = Tab2.Cola)
    WHERE NOT EXISTS (SELECT NULL
                      FROM Tab1 
                      WHERE Tab1.Col1 = Tab2.Cola
                        AND Tab1.CurrentDate = '01-Jan-2024')

Some points about the code snippet:

1. The DELETE ensures there will no data in Tab1 for the date being processed
2. I also understand that insert order is not guaranteed to be the same as result set order but that is not of concern in this scenario

Now for my questions:

1. My understanding has always been INSERT of rows does not begin until the dataset has been retrieved from the subquery. Is this correct?
2. If this is not correct, I am at least guaranteed that there is no chance of the subquery reading Tab1 “seeing” the rows being inserted into Tab1. This would make the final output totally unpredictable. Is this correct?

The reason I ask these questions, we are seeing totally different output each month in Tab1. We expect this to be constant but we are seeing rows being excluded in some months and the very same rows that excluded one month are added back the next month.

Another wrinkle here is that I am using the READ UNCOMMITTED isolation level. I use this sparingly and can guarantee that Tab1 sees no activity during the times we run this. Does this isolation change any of the responses to the questions above?