Missing rows under read committed isolation

Question

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:

• The Read Committed Isolation Level

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.

Answer 1

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.