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

Grab a pen and notepad, and jot down your answers as you go, then check your answers at the key at the bottom of the page.

Questions

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

1.  True
2.  False

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

1.  They are honored
2.  They are ignored
3.  There’s no such thing as a NOLOCK hint
4.  They cause an error

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

1.  The last time the user played, “If I Could Turn Back Time”
2.  The time that statement began
3.  The first time you accessed data in the transaction
4.  The instance start time

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

1.  The first time you accessed data in the transaction
2.  The instance start time
3.  The time that statement began
4.  The oldest open transaction

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

1.  True
2.  False

Q6. Fill in the blank

The version store is maintained in the  _____ database.

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

1.  True
2.  False

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

1.  After the instance is restarted
2.  As soon as a session runs SET TRANSACTION ISOLATION SNAPSHOT
3.  As soon as modifications occur
4.  After you create a database snapshot

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

1.  True
2.  False

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

1.  True
2.  False

Scroll down for the answer key :point_down:

Answer Key

• A1. 2. 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.
• A2. 1. They are honored. NOLOCK hints are still honored -1. which means that if you’ve got them in lots of places in your code, users will still get incorrect results (reading uncommitted data).
• A3. 3. 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).
• A4. 3. The time that statement began. RCSI provides statement-level consistency.
• A. 2. 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).
• A6. tempdb
• A7. 1. 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.
• A8. 3. 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.
• A9. 1. This is true. If a modification is ongoing, readers use row based timestamps and the version store instead of being blocked.
• A10. 1. This is true. writers still block writers.