Multiversion Read Consistency

The somewhat cumbersome name multiversion read consistency, henceforth referred to as MVRC, does
describe how the Oracle database works to provide its unique consistency. Whenever any transaction
changes data in the database, Oracle creates a new version of the changed row. Oracle uses its rollback
buffer to store earlier versions of rows. Simple enough, but the value it provides is impressive.
Each row version is marked with the system change number, or SCN, of the transaction that changed the
values. Every transaction gets a global SCN number assigned in the order that the transaction was
started. The SCN provides an absolute record of the serial order of the transaction.
When a transaction is retrieving rows to satisfy a query, it checks the SCN of the latest version of the
row. If the SCN is smaller than the SCN associated with the transaction, the row version is retrieved. If
the SCN of the row is larger than the transaction SCN, Oracle automatically knows that it cannot use
that version of the row. Oracle reaches back into the rollback buffers to retrieve an earlier version of the
row with an appropriate SCN. This process is shown in Figure 3-4.
If, for some reason, Oracle cannot find an appropriate version of the row in the rollback buffers, the
database will return an ORA-01555 error, which reads as “Snapshot too old.” You should size your
rollback buffers to be large enough to avoid this type of error. Starting with Oracle9i, you can choose to
allow the database to automatically handle UNDO, which should eliminate these errors.

MRVC gives each user a specific view of their data, at the point in time that their transaction began.
Because of this, there is no need for Oracle to use read locks in their locking system. No read locks
means less contention in two ways.
First, write operations do not block read operations. If another user changes data during the course of a
transaction, there is no need to protect the data, since the version of the data that has been read will be
stored as a version in the database.
Even more importantly, read operations do not block write operations. Since no read locks are used, a
transaction can grab an exclusive write lock whenever it needs to, regardless of whether anyone is read-
ing the data at the time or not.
For both of these reasons, you typically have less contention with an Oracle database, which not only
means better overall performance at run time but less time spent developing applications that run
against Oracle, since you don’t have to add logic to deal with either the possibility of contention or the
repercussions of that contention.
The Oracle database keeps track of the SCN of the oldest active transaction. When it needs to reclaim space
in the rollback buffer, it cleans out versions of data that are no longer required by any active transactions—
in other words, data rows that have an SCN number smaller than the SCN of the oldest active transaction.