On 13/12/11 18:18, Ann Harrison wrote:
> ... The method used by
> Oracle (and MySQL's InnoDB) keeps old versions in log files which are
> purged periodically. A long running transaction can get inconsistent
> data if the log file that contained the record version appropriate for
> it has been purged.

If I may be so bold ....

Oracle uses log files for REDO and has ROLLBACK_SEGMENTS or UNDO
Segments (depending on Oracle version) for UNDO. It never uses log files
for UNDO - and UNDO is what provides Read Consistency/MVCC in an Oracle
database.

Changes are written to the LOG_BUFFER (n memory) and periodically - on
commit, every 3 seconds max, or when the buffer is 33% full - flushed to
the REDO logs. These REDO logs might be archived to disc when they fill
up. That Depends on the database archive log mode though.

These logs are used when a database is restored and rolled forward
(using the RECOVER DATABASE command, for example).

In order to roll back changes and to ensure read consistency, UNDO is
used. These do live on disc - as tablespace files - but remain in memory
in the buffer cache alongside data blocks etc.

When a SELECT is started, the data returned are the data from the data
blocks. Each row in a block has an indicator that tells when it was last
updated. If a pending update is taking place (currently uncommitted) or
if a commit has taken place since this SELECT started then the data read
from that data block has changed - and is not consistent with the start
time of this SELECT transaction.

When this is detected, Oracle "rolls back" the changes to the start time
of the SELECT taking place by looking for the UNDO block(s) associated
with the transaction that made the changes. If that results in the
correct (consistent) data, that's what you get.

If it turns out that there were other transactions that also changed the
data, they too will be detected and undone.

In this way you only ever see data that was consistent at the start of
your own transaction.

As long as the DBA correctly sizes the UNDO tablespace and correctly
sets the UNDO_RETENTION parameter to a decent enough value, data changes
are able to be rolled back happily all the time.

If the DBA failed miserably in his/her duties, the ORA-01555 Snapshot
too old" errors are the result. And are most irritating. Long running
SELECTS - batch reports for example - tend to show up this error mostly.

This mechanism is also used to carry out a CONSISTENT=YES export of the
data - any changes made since the start of the export are "rolled back"
to get the data as it were when the export started.

UNDO segments are in three states:

Active - currently in use by a transaction.

Unexpired - A transaction has committed, but the UNDO_RETENTION period
has not yet expired. This segment should not be used.

Expired - this segment is free for use.

When a transaction needs a new UNDO segment, it attempts to use the
expired ones, if there are none, it will try to create a new one in the
free space, if any, of the undo tablespace. If it cannot, it will then
re-use an unexpired segment. This renders the data in that segment
unavailable and is the cause of the above snapshot too old errors.

Note to self: next time, be brief!


Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767