In MSSQL / Azure SQL, with Snapshots allowed and Read Committed Snapshot Isolation enabled, i have an application that does not use snapshot transactions explicitly (using the default RCSI everywhere). I have a delete statement that takes long time to run due to an suboptimal query plan that is selecting millions of rows to update about 3k rows in an indexed view.
When i try to run this delete with snapshot isolation in SSMS, queries start to pile up immediately as if i've placed table locks on all tables touched by the indexed view. From what i've read, snapshot transactions should not block other transactions but i couldn't find any indication what happens if you mix DML snapshot and RCSI transactions.
EDIT: sessions are waiting on these locks: