Oracle8i SQLJ Developer's Guide and Reference
Release 8.1.5

A64684-01

Library

Product

Contents

Index

Prev  Chap Top Next

Advanced Transaction Control

SQLJ supports the SQL SET TRANSACTION statement to specify the access mode and isolation level of any given transaction. Supported settings for access mode are READ ONLY and READ WRITE. 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 and information about SQLJ support for more basic transaction control functions, such as 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 use SET TRANSACTION it must be the first statement in your 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 a 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 ONLY };

#sql { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE };

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

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

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 have the following functionality:

Isolation Level Settings

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

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 java.sql.Connection instance of your connection context instance.

Note that this is not recommended. SQLJ code using these JDBC methods is not portable.

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




Prev

Top

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index