1

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.

Improve this question
4
  • "losing data" - you're going to have to be a lot more specific than that.
    – Aaron Bertrand
    Jul 8, 2019 at 17:59
  • I added what I mean by "losing data", also info about what I tried and what happened. Does it help in any way?
    – Jędrzej Dudkiewicz
    Jul 8, 2019 at 18:22
  • 1
    Can instances of the load data process into the staging run a second time before the first run ends? For example, assume an instance of process A starts at 10 to load data in stage, then data is loaded into target at 10:05 & delete runs at 10:06. If the load data process kick off at 10:01 again (note the previous instance has not finished) then the delete could remove data inserted by both instances & you would loose data. The easier to solve it is to add a unique identifier in staging table to denote the particular instance run that is getting processed and only delete the relevant records
    – camba1
    Jul 8, 2019 at 18:26
  • @camba1 This scenario is common - I believe snapshot isolation was added exactly to support such loads, i.e. use single staging table for 3 or 4 processes using it in parallel. It is perfectly possible for process A to start loading at 10:00, processing at 10:02 and delete and 10:04, and process B to start loading at 10:01, processing at 10:02 (less data than process A) and delete at 10:03. Of course in our case loading/processing/deleting takes a few milliseconds, but basic idea still holds true.
    – Jędrzej Dudkiewicz
    Jul 8, 2019 at 18:33

1 Answer 1

Reset to default
0

I found out what was the problem long time ago but only now found out that I haven't provided an answer to this question. The problem was:

  1. We were using snapshot isolation
  2. We were using query exactly as provided in the question. Solution was:
  3. Leave snapshot isolation unchanged, as it considerably speeds up data loading.
  4. Change query to more or less the following:
DELETE FROM STAGING_TABLE
OUTPUT DELETED.*
INTO ...

Reason for data loss was trivial - we were inserting some rows into temporary table and then deleting contents of the whole table. As data is constantly loaded into staging table some records were added between insert and delete statement - these

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.