Quiz on Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation

Q1. To use Read Committed Snapshot Isolation (RCSI), you must run SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT

  •  True
  •  False
False. RCSI is controlled by a database setting which sets which variation of red committed (default or RCSI) is the default isolation level for that database.

Q2. How do NOLOCK hints behave if you are using Read Committed Snapshot (RCSI)?

  •  They are honored
  •  They are ignored
  •  There’s no such thing as a NOLOCK hint
  •  They cause an error
They are honored. NOLOCK hints are still honored — which means that if you’ve got them in lots of places in your code, users will still get incorrect results (reading uncommitted data).

Q3. In an explicit transaction run under Snapshot Isolation, each statement will see data consistent with…

  •  The last time the user played, “If I Could Turn Back Time”
  •  The time that statement began
  •  The first time you accessed data in the transaction
  •  The instance start time
The first time you accessed data in the transaction. Data will be consistent with the first point at which you read it in the transaction, whether it was 10 milliseconds ago or 10 days ago (assuming you’ve been connected with an open transaction that long).

Q4. In an explicit transaction run under Read Committed Snapshot Isolation (RCSI), each statement will see data consistent with…

  •  The first time you accessed data in the transaction
  •  The instance start time
  •  The time that statement began
  •  The oldest open transaction
The time that statement began. RCSI provides statement-level consistency.

Q5. If you enable both RCSI and Snapshot Isolation, it produces double the amount of versions in the version store.

  •  True
  •  False
False. They use the same versioning process. The main impact is that version store cleanup may be impacted / delayed if you have long running transactions using snapshot (instead of simply the duration of statements under RCSI).

Q6. Fill in the blank

The version store is maintained in the  _____ database.

tempdb

Q7. You may see activity in the version store even if you have not enabled RCSI or Snapshot isolation on any databases.

  •  True
  •  False
True. Some built-in features in SQL Server use the version store, whether or not you have enabled it on a database. One example: online index rebuilds.

Q8. If you enable Snapshot Isolation on a database, the versioning process (timestamps and row versions) begins…

  •  After the instance is restarted
  •  As soon as a session runs SET TRANSACTION ISOLATION SNAPSHOT
  •  As soon as modifications occur
  •  After you create a database snapshot
As soon as modifications occur. The versioning process needs to begin right away, because a query could set its transaction level to Snapshot at any time, and the versions need to be ready for that.

Q9. Under Read Committed Snapshot (RCSI) and Snapshot Isolation, readers are not blocked by writers.

  •  True
  •  False
This is true. If a modification is ongoing, readers use row based timestamps and the version store instead of being blocked.

Q10. Under Read Committed Snapshot (RCSI) writers ARE blocked by other writers.

  •  True
  •  False
This is true – writers still block writers.

Back to: Read Committed Snapshot and Snapshot Isolation (46 minutes) > Quiz

Share a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu