Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## Advanced Transaction Control

SQLJ supports the SQL `SET TRANSACTION` statement to specify the access mode and isolation level of any given transaction. Standard SQLJ supports `READ ONLY` and `READ WRITE` access mode settings, but Oracle JDBC does not support `READ ONLY`. (You can set permissions to have the same effect, however.) Supported settings for isolation level are `SERIALIZABLE`, `READ COMMITTED`, `READ UNCOMMITTED`, and `REPEATABLE READ`. Oracle SQL, however, does not support `READ UNCOMMITTED` or `REPEATABLE READ`.

`READ WRITE` is the default access mode in both standard SQL and Oracle SQL.

`READ COMMITTED` is the default isolation level in Oracle SQL; `SERIALIZABLE` is the default in standard SQL.

Access modes and isolation levels are briefly described below. For more information, see the Oracle8i SQL Reference. You might also consult any guide to standard SQL for additional conceptual information.

For an overview of transactions, including SQLJ support for the basic transaction control operations `COMMIT` and `ROLLBACK`, see "Basic Transaction Control".

### SET TRANSACTION Syntax

In SQLJ, the `SET TRANSACTION` statement has the following syntax:

    #sql { SET TRANSACTION <access_mode>, <ISOLATION LEVEL isolation_level> };

If you do not specify a connection context instance, then the statement applies to the default connection.

If you use `SET TRANSACTION`, it must be the first statement in a transaction (in other words, the first statement since your connection to the database or your most recent `COMMIT` or `ROLLBACK`), preceding any DML statements.

In standard SQLJ, any access mode or isolation level you set will remain in effect across transactions until you explicitly reset it at the beginning of a subsequent transaction.

In a standard SQLJ `SET TRANSACTION` statement, you can optionally specify the isolation level first, or specify only the access mode, or only the isolation level. Following are some examples:

    #sql { SET TRANSACTION READ WRITE };

    #sql { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE };

    #sql { SET TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE };

    #sql { SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ WRITE };

You can also specify a particular connection context instance for a `SET TRANSACTION` statement, as opposed to having it apply to the default connection:

    #sql [myCtxt] { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE };

Note that in SQLJ, both the access mode and the isolation level can be set in a single `SET TRANSACTION` statement. This is not true in other Oracle SQL tools such as `Server Manager` or `SQL*Plus`, where a single statement can set one or the other, but not both.

### Access Mode Settings

The `READ WRITE` and `READ ONLY` access mode settings (where supported) have the following functionality:

- `READ WRITE` (default)--In a `READ WRITE` transaction, the user is allowed to update the database. `SELECT`, `INSERT`, `UPDATE`, and `DELETE` are all legal.

- `READ ONLY` (not supported by Oracle JDBC)--In a `READ ONLY` transaction, the user is not allowed to update the database. `SELECT` is legal, but `INSERT`, `UPDATE`, `DELETE`, and `SELECT FOR UPDATE` are not.

### Isolation Level Settings

The `READ COMMITTED`, `SERIALIZABLE`, `READ UNCOMMITTED`, and `REPEATABLE READ` isolation level settings (where supported) have the following functionality:

- `READ UNCOMMITTED` (not supported by Oracle8i)--Dirty reads, non-repeatable reads, and phantom reads are all allowed. (See below for definitions of the italicized terms.)

- `READ COMMITTED` (default for Oracle8i)--Dirty reads are prevented; non-repeatable reads and phantom reads are allowed. If the transaction contains DML statements that require row locks held by other transactions, then any of the statements will block until the row lock it needs is released by the other transaction.

- `REPEATABLE READ` (not supported by Oracle8i)--Dirty reads and non-repeatable reads are prevented; phantom reads are allowed.

- `SERIALIZABLE`--Dirty reads, non-repeatable reads, and phantom reads are all prevented. Any DML statements in the transaction cannot update any resource that might have had changes committed after the transaction began. Such DML statements will fail.

A dirty read occurs when transaction B accesses a row that was updated by transaction A, but transaction A later rolls back the updates. As a result, transaction B sees data that was never actually committed to the database.

A non-repeatable read occurs when transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data.

A phantom read occurs when transaction A retrieves a set of rows satisfying a given condition, transaction B subsequently inserts or updates a row such that the row now meets the condition in transaction A, and transaction A later repeats the conditional retrieval. Transaction A now sees an additional row; this row is referred to as a "phantom".

You can think of the four isolation level settings being in a progression:

    SERIALIZABLE > REPEATABLE READ > READ COMMITTED > READ UNCOMMITTED

If a desired setting is unavailable to you--such as `REPEATABLE READ` or `READ UNCOMMITTED` if you use an Oracle database--use a "greater" setting (one further to the left) to ensure having at least the level of isolation that you want.

### Using JDBC Connection Class Methods

You can optionally access and set the access mode and isolation level of a transaction, using methods of the underlying JDBC connection instance of your connection context instance. SQLJ code using these JDBC methods is not portable, however.

Following are the `Connection` class methods for access mode and isolation level settings:

- `public abstract int getTransactionIsolation()`--Returns the current transaction isolation level as one of the following constant values:
  `TRANSACTION_NONE
  TRANSACTION_READ_COMMITTED
  TRANSACTION_SERIALIZABLE
  TRANSACTION_READ_UNCOMMITTED
  TRANSACTION_REPEATABLE_READ`

- `public abstract void setTransactionIsolation(int)`--Sets the transaction isolation level, taking as input one of the preceding constant values.

- `public abstract boolean isReadOnly()`--Returns `true` if the transaction is `READ ONLY`; returns `false` if the transaction is `READ WRITE`.

- `public abstract void setReadOnly(boolean)`--Sets the transaction access mode to `READ ONLY` if `true` is input; sets the access mode to `READ WRITE` if `false` is input.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index