TSQL Snapshot Isolation

Question

Using SQL2k5, I have a staging table that contains columns that will populate numerous other tables. For instance, a statement like this:

 INSERT INTO [appTable1] ([colA], [colB])
 SELECT [appTable1_colA], [appTable1_colB]
 FROM [stageTable]

A trigger on [appTable1] will then populate the identity column values of the newly inserted rows back into [stageTable]; for this example, we'll say it's [stageTable].[appTable1_ID] which are then inserted into other tables as a FK. More similar statements follow like:

 INSERT INTO [appTable2] ([colA], [colB], [colC], [appTable1_FK])
 SELECT [appTable2_colA], [appTable2_colB], [appTable2_colC], [appTable1_ID]
 FROM [stageTable]

This process continues through numerous tables like this. As you can see, I'm not including a WHERE clause on the SELECTs from the staging table as this table gets truncated at the end of the process. However, this leaves the possibility of another process adding records to this staging table in the middle of this transaction and those records would not contain the FKs previously populated. Would I want to issue this statement to prevent this?:

 SET TRANSACTION ISOLATION LEVEL SNAPSHOT

If this is the best solution, what are the downsides of doing it this way?

Answer 1

All Isolation levels, including snapshot, affect only reads. SELECTs from stageTable will not see uncommited inserts, nor it will block. I'm not sure that solves your problem of throwing everything into the stageTable without any regard for ownership. What happens when the transaction finally commits, the stageTable is left with all the intermediate results ready to be read by the next transaction? Perhaps you should use a temporary #stageTable that will ensure a natural isolation between concurent threads.

To understand the cost of using Snapshot isolation, read Row Versioning Resource Usage:

• extra space consumed in tempdb
• extra space consumed in each row in the data table
• extra space consumed in BLOB storage for large fields

Answer 2

Can you add a batch id to your staging table, so that you can use it in where clauses to ensure that you are only working on the original batch of records? Any process that adds records to the staging table would have to use a new, unique batch id. This would be more efficient (and more robust) than depending on snapshot isolation, I think.