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.

The DELETE ensures there will not data in Tab1 for the date being processed.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:

My understanding has always been INSERTION of rows does not begin until the dataset has been retrieved from the subquery. Is this correct?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.

​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 currentlySET 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.The DELETE ensures there will not data in Tab1 for the date being processed.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 scenarioNow for my questions:My understanding has always been INSERTION of rows does not begin until the dataset has been retrieved from the subquery. Is this correct?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.  Read More

​