Isolation levels in database transactions in Oracle and DB2

The SQL92 standard defines four levels of transaction isolation with differing degrees of impact on transaction processing. These isolation levels are defined in terms of the following three read types:

Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
Fuzzy reads: A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
Phantom reads: A transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

SQL92 defines four levels of isolation based on the read permissions above (nonrepeatable read is same as a fuzzy read):

Now, Oracle offers only the read committed and serializable isolation levels plus a read-only mode that is not part of SQL92. Read committed is the default. Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements. Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.

Here is how to set in Oracle the isolation level of a transaction, remember to issue the statement at the very beginning:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

DB2 supports all four isolation levels. The isolation levels are called:

RR: Repeatable read
RS: Read stability
CS: Cursor stability
UR: Uncommitted read

Have a look at this table to see the DB2 UDB equivalent to the ANSI SQL Isolation Level:

In DB2, the isolation level can be specified in several different ways. It depends on if we use static or dynamic SQL. At precompile or bind time we can also use the PREP or BIND APIs to specify the isolation level. On database servers that support REXX: REXX and the command line processor connect to a database using a default isolation level of cursor stability. At the statement level use the WITH clause. From CLI or ODBC at runtime use the CHANGE ISOLATION LEVEL command. For dynamic SQL within the current session use the SET CURRENT ISOLATION statement to set the isolation level for dynamic SQL issued within a session.

DB2/LUW introduces a new isolation level semantic called “CURRENTLY COMMITTED” that allows DB2 locking to generally work as Oracle does and this means that your applications can work with both databases without different logic. There is also a new interface called “CLP Plus” that is useful for running scripts written for another vendor’s interface. It mimics SQL*PLus in Oracle.DB2/LUW introduces a new isolation level semantic called “CURRENTLY COMMITTED” that allows DB2 locking to generally work as Oracle does and this means that your applications can work with both databases without different logic. There is also a new interface called “CLP Plus” that is useful for running scripts written for another vendor’s interface. It mimics SQL*PLus in Oracle.

Finally, here is comparison of the most important database brands:

P.S. To determine the isolation level of a package in DB2, execute the following query: SELECT ISOLATION FROM SYSCAT.PACKAGES WHERE PKGNAME = ‘X’ AND PKGSCHEMA = ‘Y’ .

P.P.S. OLE DB, Teradata, and nonrelational data sources do not have a concept similar to isolation levels.