I'm curious if you were in your first example to do the select query to return a larger set instead of one row through the cursor while one row in the set was being updated (but not all), does it still do the same where part of the returned rows come from the table and one row is retrieved from the store?
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE;
--adding rollback immediate is the trick here, so it rolls back all running sessions. For RCSI to be enabled or disabled, there should not be any active transactions and it needs exclusive access on the database.
When you're doing this on production, make sure this is communicated to product teams, or do it in off hours.
Great post indeed! Thanks Haripriya for your valuable insights!
A small suggestion for a follow up post: a deep comparison between RCSI and Snapshot Isolation, in particular about MVCC handling.
Thanks Haripriya, good that you took your time to investigate and post about this.
Outstanding Hari. rcsi always a better option to si
I'm curious if you were in your first example to do the select query to return a larger set instead of one row through the cursor while one row in the set was being updated (but not all), does it still do the same where part of the returned rows come from the table and one row is retrieved from the store?
Hari, I've had difficulty switching off RCSI. It seems to be an easy target for sessions already using rcsi
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE;
--adding rollback immediate is the trick here, so it rolls back all running sessions. For RCSI to be enabled or disabled, there should not be any active transactions and it needs exclusive access on the database.
When you're doing this on production, make sure this is communicated to product teams, or do it in off hours.
Thanks