Quiz on repeatable read & serializable isolation levels

Questions

Q1. Which isolation level allows both non-repeatable reads AND phantom reads to occur?

1.  Read committed
2.  Serializable
3.  Snapshot
4.  Repeatable read

Q2. Which isolation level allows phantom reads, but NOT non-repeatable reads to occur?

1.  Read committed
2.  Repeatable read
3.  Serializable
4.  Snapshot

Q3. For disk-based tables, which type of lock protects against phantoms?

1.  Intent Shared Locks
2.  Update Locks
3.  Key Range Locks
4.  Deadlocks

Q4. Which best describes the locking pattern for a scan in a SELECT query run under the read committed isolation level?

1.  Shared locks are generally held just long enough to process each row
2.  Shared locks are generally held for the duration of the entire transaction
3.  Key range locks are generally held just long enough to process each row
4.  Key range locks are generally held for the duration of the entire transaction

Q5. Which best describes the locking pattern for a scan in a SELECT query run under the repeatable read isolation level?

1.  Shared locks are generally held for the duration of the entire transaction
2.  Key range locks are generally held just long enough to process each row
3.  Key range locks are generally held for the duration of the entire transaction
4.  Shared locks are generally held just long enough to process each row

Q6. Fill in the blank with the most logical word

If you raise your isolation level to repeatable read or serializable and are using disk-based tables, you risk increased  _________ ing and deadlocks.

Q7. Fill in the blank with the correct isolation level

An “optimistic” alternative to the serializable isolation level which uses row versioning and timestamps to provide a transaction data consistent with the first time a statement accesses data is _______   isolation.

Q8. A query referencing disk-based tables can use more than one isolation level.

1.  True
2.  False

Q9. The HOLDLOCK table hint is equivalent to

1.  Read committed
2.  Repeatable read
3.  Serializable
4.  XLOCK

Q10. The demo in this course showing a non-repeatable read used which pattern in its execution plan?

1.  Bookmark lookup
2.  Lock escalation
3.  Lazy spool
4.  Index intersection

Scroll down for the answer key :point_down:

Answer Key

• A1. 1. Read committed
• A2. 2. Repeatable read
• A3. 3. Key Range Locks
• A4. 1. Shared locks are generally held just long enough to process each row
• A5. 3. Shared locks are generally held for the duration of the entire transaction
• A6. block
• A7. snapshot
• A8. 1. True
• A9. 3. Serializable
• A10. 4. Index Intersection