Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

7.4 Oracle Concurrency Features

Three features are used by Oracle to implement
multiversion read consistency:

Rollback segments

Rollback
segments are structures in the Oracle database that store
"undo" information for transactions
in case of rollback. This information restores database rows to the
state they were in before the transaction in question started. When a
transaction starts changing some data in a block, it first writes the
old image of the data to a rollback segment. The information stored
in a rollback segment provides the information necessary to roll back
a transaction and supports multiversion read consistency.

A rollback segment is different from a redo log. The redo log is used
to log all transactions to the database and recover the database in
the event of a system failure, while the rollback segment provides
rollback for transactions and read consistency.

Blocks of rollback segments are cached in the System Global Area just
like blocks of tables and indexes. If rollback segment blocks are
unused for a period of time, they may be aged out of the cache and
written to disk.

System Change Number (SCN)

To preserve the integrity of the data in
the database, it's critical to keep track of the
order in which actions were performed. Oracle must preserve the
ordering of transactions with respect to time. The mechanism Oracle
uses is the System Change Number.

The SCN is a logical timestamp that tracks the order in which events
occurred. Oracle uses the SCN information in the redo log to
reproduce transactions in the original and correct order when
applying redo. Oracle also uses the SCN to determine when to clean up
information in rollback segments that's no longer
needed, as you will see in the following sections.

In Oracle Database 10g, there is now a new pseudocolumn on each row that contains the SCN, ORA_ROWSCN. You can quickly determine if a row has been updated since it was retrieved by comparing the value read from this pseudocolumn at the start of a transaction with the value read from this pseudocolumn at the end of the transaction.

Locks in data blocks

A database must have a way of determining
if a particular row is locked. Most databases keep a list of locks in
memory, which are managed by a lock manager process. Oracle keeps
locks with an area of the actual block in which the row is stored. A
data block is the smallest amount of data that can be read from disk
for an Oracle database, so whenever the row is requested, the block
is read, and the lock is available within the block. Although the
lock indicators are kept within a block, each lock affects only an
individual row within the block.

In addition to the above features, which directly pertain to
multiversion read consistency, another implementation feature in
Oracle provides a greater level of concurrency in large user
populations:

Nonescalating row locks

To reduce the overhead of the
lock-management process, other databases will sometimes
escalate locks to a higher level of granularity
within the database. For example, if a certain percentage of rows in
a table are locked, the database will escalate the lock to a table
lock, which locks all the rows in a table including rows that
aren't specifically used by the SQL statement in
question. Although lock escalation reduces the number of locks the
lock manager process has to handle, it causes unaffected rows to be
locked. Because each row's lock is kept within its
data block, there is never any need for Oracle to escalate a lock.

A lock manager called the Distributed Lock Manager (DLM) has
historically been used with Oracle Parallel Server to track locks
across multiple instances of Oracle. This is a completely different
and separate locking scheme that doesn't affect the
way Oracle handles row locks. The technology used in Oracle Parallel
Server has been improved and renamed in
Oracle9i as
Real Application Clusters, and the technology used in the DLM has
been integrated into the core of the product. Real Application
Clusters are described in more detail in Chapter 8.