7

It's well known that Read Committed isolation is prone to different anomalies. I read a great Paul White's series on isolation levels (the one relevant to the discussion is this one: https://sqlperformance.com/2014/04/t-sql-queries/the-read-committed-isolation-level). It states (again, it's reasonably well known), that a statement running under locking 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.

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.

4
  • 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). – Alexander Shelemin Feb 15 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 at 15:42
  • Yes, SNAPSHOT is great. – Alexander Shelemin Feb 15 at 17:26
  • I think of readcommited as for report generation and reports only need to be good enough to result in the correct management actions. – Ian Ringrose Feb 15 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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