With robust, complex database systems, there has to be a way of
managing potential conflicts that could arise when attempting to
process multiple transactions on a database at the same time.
In Oracle, users can specify transaction isolation levels to
indicate the level of care to be exercised when resolving potential
conflicts.
The higher the transaction isolation level is, the
more careful the system is about avoiding conflicts. On the
other hand, there generally is a price for this avoidance of
conflict. The higher the transaction isolation level, the
locking overhead can increase while user concurrency can decrease.
Developers and DBA must take these factors into account when setting
Oracle transaction isolation levels.
Currently, there are three transaction isolation levels supported
by the Oracle database. There is a fourth transition isolation
level,
read
uncommitted, that is not supported by Oracle.
These transaction isolation levels are:
Read Committed Transaction Isolation Level
The read committed transaction isolation level is the Oracle
default. With this setting, each query can see only data
committed before the query, not the transaction, began. Oracle
queries do not read
dirty, or uncommitted, data; however, it does
not prevent other transaction from modifying data read by a query.
Thus, it is possible that other transactions can change data between
executions of the query. Any transaction that executes a given
query more than once can experience non-repeatable reads or
phantoms.
If necessary, the read committed transaction isolation level can
be set using the following:
- Transaction Level:
SET
TRANSACTION ISOLATION LEVEL READ COMMITTED;
- Session Level:
ALTER SESSION
SET ISOLATION_LEVEL READ COMMITTED;
Serializable Transaction Isolation Level
The serializable transaction isolation level is not supported
with distributed transactions. With the serializable
transaction isolation level, only data that was committed at the
start of the transaction plus those made by the transaction itself
through INSERTs, UPDATEs and DELETEs can be accessed by a query.
Unlike the read committed level transactions, serializable isolation
level transactions will not experience non-repeatable reads or
phantoms.
The idea is to prevent dirty reads. As I understand it, it's
possible in a distributed transaction to get a non-repeatable read
because the read consistency does not work across databases.
In the serializable transaction isolation level
non-repeatable and phantom reads are not possible.
The Serializable transaction isolation level can be set
using the following:
- Transaction level:
SET
TRANSACTION ISOLATION LEVEL SERIALIZABLE;
- Session level:
ALTER SESSION
SET ISOLATION_LEVEL SERIALIZABLE;
Read Only Transaction Isolation Level
With the Read Only transaction isolation level, only data that
was committed at the start of the transaction can be accessed by a
query. No modification to the data is allowed.
The Read Only transaction isolation level can be set using the
following:
- Transaction level:
SET
TRANSACTION ISOLATION LEVEL READONLY;
- Session level:
ALTER SESSION
SET ISOLATION_LEVEL READONLY;
As shown, transaction isolation levels can be set at either the
transaction level, which applies to that transaction only, or the
session level, which applies to each transaction subsequent to the
ALTER SESSION command. The use of the ALTER SESSION command to
set the transaction isolation level for the session can save some
networking and processing costs that could be associated with having
SET TRANSACTION at the start of each statement.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
