Oracle in a Nutshell

Oracle and Concurrent User Access

Oracle’s concurrency scheme, multiversion read consistency (MVRC), guarantees that a user sees a consistent view of the data he requests. If another user changes the underlying data during the query execution, Oracle maintains a version of the data as it existed at the time the query began. If transactions were underway but uncommitted at the time the query began, Oracle will ensure that the query does not see the changes made by those transactions. The data returned to the query will reflect all committed transactions at the time the query started.

This feature has several effects on the way that queries impact the database and its performance:

• Oracle does not place any locks on data for read operations. This means that a read operation will never block a write operation. Even if a database places a single lock on a single row as part of a read operation, it can still cause contention in the database, especially because most database tables tend to concentrate update operations around a few hot spots of active data.

• A write operation will never block a read operation, because MVRC will simply provide a version of the data that existed before the write operation began.

• A user gets a complete snapshot view of the data, accurate at the point in time that the query began. A row that is retrieved at the end of a result set may have been changed since the time the result set retrieval began. But because Oracle keeps a version of the row as it existed at ...