Imagine: I have db with RCSI but for some select queries I need to read real data but not last committed record version because it is critical for the app.
Can I use in a statement or in a stored procedure RC isolation level?
Can you explain a little bit more why you also set the Turn ON ALLOW_SNAPSHOT_ISOLATION. in step 3 just before the SET READ_COMMITTED_SNAPSHOT ON the database level ?
And thanks for the very interesting post you always bring out !
As per my understanding, enabling READ_COMMITTED_SNAPSHOT (RCSI) itself allows SQL Server to read from versioned rows in tempdb and avoids blocking issues, so why do we need to enable ALLOW_SNAPSHOT_ISOLATION as well?
From my testing, RCSI seems to handle both things. Can you please clarify the difference between these two? Am I missing any hidden behavior here?
Thanks Haripriya. This helps me to trying understand it's negative implications if any
Thanx Haripriya for the useful article.
Imagine: I have db with RCSI but for some select queries I need to read real data but not last committed record version because it is critical for the app.
Can I use in a statement or in a stored procedure RC isolation level?
Can you explain a little bit more why you also set the Turn ON ALLOW_SNAPSHOT_ISOLATION. in step 3 just before the SET READ_COMMITTED_SNAPSHOT ON the database level ?
And thanks for the very interesting post you always bring out !
Enabling ALLOW_SNAPSHOT_ISOLATION will allow last committed rows to be stored in TempDB, before write operation runs on the table.
READ_COMMITTED_SNAPSHOT will allow select queries to read those last committed rows from TempDB concurrently while write operation runs on the table.
Thanks for the useful information! I have been exploring this topic from various resources, especially
https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/.
However, I still have one confusion:
As per my understanding, enabling READ_COMMITTED_SNAPSHOT (RCSI) itself allows SQL Server to read from versioned rows in tempdb and avoids blocking issues, so why do we need to enable ALLOW_SNAPSHOT_ISOLATION as well?
From my testing, RCSI seems to handle both things. Can you please clarify the difference between these two? Am I missing any hidden behavior here?