RCSI & ETL processing compatibility

Question

The Situation:

I have a write-heavy, read-light ETL workload with the basic process of:

1. get chunk of raw data (generally 1000-5000 records) from an import landing table.
2. pass the chunk of data from step 1 to the requesting windows service for some heavy duty calculations & processing. Each record ingested in a chunk may result in 100+ downstream records in other tables.
3. Windows service calls a save SP which has 14 MemOpt table variable parameters containing the processed "chunk" of data (three of these table variables have upwards of 50k rows or more).

• The work is insert heavy and there is very little read ops going on in step 3, however, the "chunk" may contain data to be deleted and/or updated. There is minimal read contention between step 1 & 3.
• I've virtually eliminated deadlocks during step 3, although there is significant blocking during 3 with insert/update ops.

Question:

Would Snapshot or RCSI isolation help when performing the insert/update ops in Step 3? What are the risks of introducing update conflicts?

Answer 1

With Snapshot or RCSI isolation you get optimistic locking model where Readers don't block Writers and Writers don't block Readers. Beware that with optimistic locking query results for Readers may differ from pessimistic locking, since Readers don't wait for Writers to complete

Would Snapshot or RCSI isolation help when performing the insert/update ops in Step 3?

Writers still block Writers since Exclusive locks are held, but it of course depends which rows / pages / tables are locked. So no, I would say RSCI or Snapshot won't help for step 3

In fact, Snapshot isolation may potentially even make things worse, since it operates on transaction level while RCSI operates on statement level

What are the risks of introducing update conflicts?

According to the table below, you may get the update conflicts in Snapshot isolation (not RCSI)