Quiz: What’s So Weird About Read Committed?

Quiz: What's So Weird About Read Committed?

  • Check all that apply
  • Check all that apply

Interested to learn more about how to avoid inconsistent data? Join me for the upcoming SQL Seminar: Conquer Blocking & Isolation Levels.

4 thoughts on “Quiz: What’s So Weird About Read Committed?

  1. I’m having a hard time understanding why for question 3) that “Encounter the same row more than once” and “Miss a row entirely” are correct answers. Can you give me a clue?

    1. Sure! Imagine you have a nonclustered index, NC1 keyed on a column name AccountTotal. It has a million rows, just to pick a round number.

      Session 1 starts a select query doing SUM(AccountTotal). It gets a plan that scans NC1 from the lowest AccountTotal to the highest AccountTotal. Near the beginning of the scan, it reads a row for $500 and continues on.

      At this time, a BIG deposit is made and that row’s account total is modified by another session. It is now $500,000. This physically moves the row in the NC1, since it is keyed on AccountTotal and the rows for $500,000 are on different pages.

      The update is not blocked because Session 1 holds locks for a very brief time while it is reading.

      Session 1 continues scanning, and encounters the row for this account which has been updated to $500,000 and includes it in the sum.

      It has seen the same row twice in the same statement, and summed up both values. The sum of AccountTotal will be $500 higher than it would be if the statement were consistent with a single point in time.

      The situation can be replayed similarly with an update from a high to low value to have the scenario of missing the row entirely.

  2. When Kim Tripp did a demo on this I pretty much doubted my life up to that point.

    1. Yes, I LOVE demos of this! If you like this kind of thing, I get a lot of inspiration from Craig Freedman’s posts on isolation levels, he’s got some good demos in there, too.

Leave a Reply

Your email address will not be published. Required fields are marked *

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