T-SQL : INSERT INTO SELECT Issues - READ UNCOMMITTED ISOLATION LEVEL

[DrDeadPan]

So we've have some strange issues appear in our Production system and we think it comes down to a stored procedure (SQL Server 2019) that employs an INSERT INTO SELECT clause.

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 not data in Tab1 for the date being processed.
2. I also understand that Insertion 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 INSERTION 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 atleast 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?

Thanks in advance.

[#2]

---

My understanding has always been INSERTION of rows does not begin until the dataset has been retrieved from the subquery. Is this correct?

---

@DrDeadPan , more or less.

A DML statement is always an "all-or-nothing", if during execution an error occurs, a transaction rollback comes in place.

And for the rest: I don't have your database to test anything.

[#3]

@olafhelper Thanks for the response. That was my undersatnding as well. I can test the rest myself but with the unpredicatability with what I am seeing, I am not sure it would yield consistent results each time. Thank you again.