I work on a small system that receives quite a lot of data from multiple sources (hundreds of them). Depending on few factors I can get anywhere from few rows to a couple of thousands (in fact smallest message contains single row and largest contains at most five thousands, but if more data should be loaded then I get this data in few batches). Currently data is processed by few identical services. Each service load data into one of five staging tables and executes procedure that does the right thing - each staging table has different procedure associated with it, but it always boils down to changing unique identifiers (e.g. UUIDs) to proper database identifiers, inserting data into destination table and removing data from staging table. All procedures have this form:
INSERT INTO TARGET (A, B, C)
SELECT T1.ID, B, C FROM STAGING_TABLE
JOIN T1 ON A = T1.ID
DELETE FROM STAGING_TABLE
It is possible for all services to work on single staging (and destination) table in parallel. Currently this is done using snapshot isolation, but it is painfully obvious that for one reason or another we are losing data. What I mean by it is that there are messages that are properly processed by the services but all information from them is lost - we don't see records in the database. I can't prove that snapshot isolation is in responsible, but such incidents started to happen after snapshot isolation was introduced - which in turn was introduced after services loading data in parallel were introduced. Databases are currently far from my main field of expertise and I don't know why it happens, but it seems that snapshot isolation is the main culprit.
My question is: what is the lowest isolation level that can support this scenario? Is there better way to do it? I'm not fully aware what transaction isolation level was used earlier (when data was loaded by single service), but we never observed data loss. I tried (blindly) using "serializable" and "repeatable read", but "serializable" results in dropped messages due to deadlocks and "repeatable read" while seems to do the right thing (no data loss) also degrades performance to the level of serial writing.
EDIT: Is it viable to load data using snapshot isolation, insert into temporary table (or table variable), THEN switch to some very permissive isolation level, insert data from temporary table into target table, revert to snapshot and delete data from staging table? If I read this correctly:
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms173763(v=sql.90)
It should be possible, but I don't understand yet if any of this would have any effect in discussed case - target table is not read, only written in this scenario, and I think this means that write won't be any "more parallel" than under snapshot isolation. But maybe I'm wrong?
Note that we can't wait and for example load data from multiple sources into single staging table and then move it into target table. We're not aiming for real time, but we need data insert ASAP.