7

It's well known that Read Committed isolation is prone to different anomalies. I read The Great Paul White's series on isolation levels. The post relevant to the discussion is this one:

It states (again, it's reasonably well known), that a statement running under read committed isolation:

Can encounter the same row multiple times;
Can miss some rows completely;

My question is about the 'missing rows' part. Examples that talk about missing rows usually demonstrate the problem using queries like:

select count(*) from table.

My question is can the rows be missed in a 'regular' select query? Meaning, can a query like

select * from table

or even

select * from table where id = @id

also miss rows that are committed before the start of that query? This question applies to Read committed with locking (not RCSI) only, as RCSI doesn't allow these types of anomalies.

1 Answer 1

11

A scenario where rows committed before the start of the SELECT query can be missed is when an index key value is updated. Consider this query:

SELECT * FROM dbo.YourTable;

The execution plan will likely perform an ordered clustered scan to return all columns. If a key value is updated and committed during the scan and the new value is less than the ordered scan point at the time of the update, the row will not be returned.

With this query:

SELECT * FROM dbo.YourTable WHERE id = @id;

The row will not be missed if id is the primary key and a singleton seek is used. But in the case of a seek/scan of non-unique index where the key value is updated and committed during the scan, the row may or may not be returned depending on the scan point that the time of the update.

2
  • Thanks, Dan! That's what I suspected, and that is actually quite scary - so, depending on the existing indices, a search can miss rows! (And thanks also for very useful blogs entries from your blog of about 10 years ago, on concurrent upsert/merge). Feb 15, 2021 at 15:25
  • 1
    @AlexanderShelemin, I've become a fan of SNAPSHOT isolation. Without an explicit transaction, SI behaves the same as RCSI except one can opt-in as needed instead of the entire DB.
    – Dan Guzman
    Feb 15, 2021 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.