Concurrency and Multiversioning - Oracle Database Transactions and Locking Revealed - page 62

While this sounds good, you must remember that leaving the shared read locks behind on all data as it is read
will, of course, severely limit concurrent reads and modifications. So, while this isolation level in those databases
provides for lost update prevention, it does so by removing the ability to perform concurrent operations! You can't
always have your cake and eat it too.

SERIALIZABLE
This is generally considered the most restrictive level of transaction isolation, but it provides the highest degree of
isolation. A SERIALIZABLE transaction operates in an environment that makes it appear as if there are no other users
modifying data in the database. Any row we read is assured to be the same upon a reread, and any query we execute
is guaranteed to return the same results for the life of a transaction. For example, if we execute the following, the
answers returned from T would be the same, even though we just slept for 24 hours (or we might get an ORA-01555,
snapshot too old error, which is discussed in Chapter 8):

Select * from T;
Begin dbms_lock.sleep( 60*60*24 ); end;
Select * from T;

The isolation level SERIALIZABLE assures us these two queries will always return the same results. Side effects
(changes) made by other transactions are not visible to the query regardless of how long it has been running.
In Oracle, a SERIALIZABLE transaction is implemented so that the read consistency we normally get at the
statement level is extended to the transaction.

■ as noted earlier, there is also an isolation level in oracle denoted READ ONLY . It has all of the qualities of the
SERIALIZABLE isolation level, but it prohibits modifications. It should be noted that the SYS user (or users connected with
the SYSDBA privilege) can't have a READ ONLY or SERIALIZABLE transaction. SYS is special in this regard.

Note

Instead of results being consistent with respect to the start of a statement, they are preordained at the time you
begin the transaction. In other words, Oracle uses the undo segments to reconstruct the data as it existed when our
transaction began, instead of just when our statement began.
That's a pretty deep thought there: the database already knows the answer to any question you might ask it,
before you ask it.
This degree of isolation comes with a price, and that price is the following possible error:

ERROR at line 1:
ORA-08177: can't serialize access for this transaction

You will get this message whenever you attempt to update a row that has changed since your transaction began.

■ oracle attempts to do this purely at the row level, but you may receive an ORA-08177 error even when the row
you are interested in modifying has not been modified. The ORA-08177 error may happen due to some other row(s) being
modified on the block that contains your row.

Note

Oracle takes an optimistic approach to serialization—it gambles on the fact that the data your transaction wants
to update won't be updated by any other transaction. This is typically the way it happens, and usually the gamble
pays off, especially in quick-transaction, OLTP-type systems. If no one else updates your data during your transaction,