READ_COMMITTED transactions can return inconsistent results.
For example:
1) Start READ_COMMITTED transaction T1
2) In T1 start query Q1: "select count(*) from MYTABLE"
3) Start transaction T2
4) In T2 insert some records into MYTABLE
5) Commit T2
6) Query Q1 finishes
If the table is large enough, and the query Q1 takes long enough, the query Q1 may or may not return all of the records inserted in transaction T2.
Why?
SQL statements executed in a read committed transaction use the live Transaction
Information Page cache to check the state of record versions.
This means that a SELECT running in transaction A could look at a
version of record 1 that was changed by transaction B, see that transaction
B is still active, and ignore that record version. Transaction A could then
look at a record version for another record modified by transaction B and
discover that transaction B has committed and use the record version. This
means that if B commits while the SELECT in A is running the SELECT in A may
see some of the changes made by B and not others. If you want a consistent
view of the data you have to use snapshot transaction isolation.
Last Modified: 01-MAY-02