Database Reference
In-Depth Information
Table 7-7.
SERIALIZABLE Transaction Example
Time
Session 1 Executes
Session 2 Executes
Alter session set isolation_level=serializable;
--
T1
Alter session set isolation_
level=serializable;
T2
--
Insert into a select count(*) from b;
T3
--
Insert into b select count(*) from a;
T4
--
Commit;
T5
--
Commit;
T6
--
Now, when this is all said and done, tables
A
and
B
will each have a row with the value 0 in it. If there were some
serial ordering of the transactions, we could not possibly have both tables containing the value 0 in them. If session
1 executed
in its entirety
before session 2, then table
B
would have a row with the value 1 in it. If session 2 executed
in its entirety
before session 1, then table
A
would have a row with the value 1 in it. As executed here, however, both
tables will have rows with a value of
0
. They just executed as if they were the only transaction in the database at that
point in time. No matter how many times session 1 queries table
B
and no matter the committed state of session 2, the
count will be the count that was committed in the database at time T1. Likewise, no matter how many times session 2
queries table
A
, the count will be the same as it was at time T2.
READ ONLY
READ ONLY
transactions are very similar to
SERIALIZABLE
transactions, the only difference being that they do not allow
modifications, so they are not susceptible to the
ORA-08177
error.
READ ONLY
transactions are intended to support
reporting needs where the contents of the report need to be consistent with respect to a single point in time. In other
systems, you would use
REPEATABLE READ
and suffer the associated effects of the shared read lock. In Oracle, you will
use the
READ ONLY
transaction. In this mode, the output you produce in a report that uses 50
SELECT
statements to
gather the data will be consistent with respect to a single point in time—the time the transaction began. You will be
able to do this without locking a single piece of data anywhere.
This aim is achieved by using the same multiversioning as used for individual statements. The data is
reconstructed as needed from the undo segments and presented to you as it existed when the report began.
READ
ONLY
transactions are not trouble-free, however. Whereas you might see an
ORA-08177
error in a
SERIALIZABLE
transaction, you expect to see an
ORA-01555 snapshot too old
error with
READ ONLY
transactions. This will happen
on a system where other people are actively modifying the information you are reading. The changes (undo) made
to this information are recorded in the undo segments. But undo segments are used in a circular fashion in much
the same manner as redo logs. The longer the report takes to run, the better the chance that some undo you need to
reconstruct your data won't be there anymore. The undo segment will have wrapped around, and the portion of it you
need would be reused by some other transaction. At this point, you will receive the
ORA-01555
error and have to start
over again.
The only solution to this sticky issue is to have the undo tablespace sized correctly for your system. Time and
time again, I see people trying to save a few megabytes of disk space by having the smallest possible undo tablespace
(“Why 'waste' space on something I don't really need?” is the thought). The problem is that the undo tablespace is a
key component of the way the database works, and unless it is sized correctly, you will hit this error. In many years of
using Oracle 6, 7, 8, 9, 10, 11, and 12, I can say I have never hit an
ORA-01555
error outside of a testing or development
system. In such a case, you know you have not sized the undo tablespace correctly and you fix it. We will revisit this
issue in Chapter 9.
