Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle and Concurrent User Access

Oracle solves the problems created by concurrent access through a technology called multiversion read consistency. Multiversion read consistency guarantees that a user sees a consistent view of the data she 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 there were transactions underway but uncommitted at the time the query began, Oracle will ensure that the query ignores 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 two dramatic effects on the way queries impact the database. First, Oracle doesn’t 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 since most database tables tend to concentrate update operations around a few “hot spots” of active data.

Second, a user gets a complete “snapshot” view of the data, accurate at the point in time that the query began. Other databases may reduce the amount of contention in the database by locking an individual row only while it’s being read, rather than over the complete duration of the row’s transaction. A row that’s retrieved at the end of a result set may have been changed ...