Forum Discussion
T-SQL : INSERT INTO SELECT Issues - READ UNCOMMITTED ISOLATION LEVEL
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.
- olafhelperBronze Contributor
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.
- DrDeadPanCopper Contributor
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.