community.borland.com

Article #28656: READ_COMMITTED tranactions can return inconsistent results.

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