Skip Headers
Oracle® Database Lite Developer's Guide
10g (10.2.0)
Part No. B15920-01
  Go To Table Of Contents
Go To Index


15 Oracle Database Lite Transaction Support

When an application connects to the local client database—Oracle Database Lite—it begins a transaction with the database. There can be a maximum of 64 connections to Oracle Database Lite. Each connection to Oracle Database Lite maintains a separate transaction, which conform to ACID requirements.

A transaction can include a sequence of database operations, such as SELECT, UPDATE, DELETE, and INSERT. All operations either succeed and are committed or are rolled back. Oracle Database Lite only updates the database file when the commit is executed. If an event, such as a power outage, interrupts the commit, then the database is restored during the next connection.

15.1 Locking

Oracle Database Lite supports row-level locking. Whenever a row is read, it is read locked. Whenever a row is modified, it is write locked. If a row is read locked, then different transactions can still read the same row. However, a transaction cannot access a row if it is a write locked row by another transaction.

15.2 What Are the Transaction Isolation Levels?

Each transaction is isolated from another. Even though many transactions run concurrently, transaction updates are concealed from other transactions until the transaction commits. You can specify what level of isolation is used within the transaction, as listed in Table 15-1:

Table 15-1 Isolation Levels

Isolation Level Description
Read Committed In Oracle Database Lite, a READ COMMITTED transaction first acquires a temporary database level read lock, places the result of the query into a temporary table, and then releases the database lock. During this time, no other transaction can perform a commit operation. No data objects are locked. All other transactions are free to perform any DML operation—except commit—during this time. Since a commit operation locks the database in intent exclusive mode, a read committed transaction, while gathering the query result, will block another transaction that is trying to commit or vice versa. A READ COMMITTED transaction provides the highest level of concurrency, as it does not acquire any data locks and does not block any other transaction from performing any DML operations. In addition, the re-execution of the same query (SELECT statement) may return more or less rows based on other transactions made to the data in the result set of the query.

Note: A SELECT statement containing the FOR UPDATE clause is always executed as if it is running in a REPEATABLE READ isolation level.

A SELECT statement can execute Java stored procedures. If the transaction executing the Java stored procedure is in the READ COMMITTED isolation level and the Java stored procedure updates the database, then the SELECT statement that executes the Java stored procedure must have a FOR UPDATE clause. Otherwise, Oracle Database Lite issues an error.

Repeatable Read In this isolation level, a query acquires read locks on all of the returned rows. More rows may be read locked because of the complexity of the query itself, the indexes defined on its tables, or the execution plan chosen by the query optimizer. The REPEATABLE READ isolation level provides less concurrency than a READ COMITTED isolation level, transaction because the locks are held until the end of the transaction.

A "phantom" read is possible in this isolation level, which can occur when another transaction inserts rows that meet the search criteria of the current query and the transaction re-executes the query.

If a FOR UPDATE clause is used in a query, a short-term update lock is acquired on the current row(s) being selected. If a row is updated, the lock is converted into an exclusive lock. An exclusive lock prevents any other transaction running in an isolation level other than READ COMMITTED to access this row. If the row is not updated but the next row is fetched, the update lock is downgraded to a read lock, permitting other transactions to read the row.

Serializable This isolation level acquires shared locks on all tables participating in the query. The same set of rows is returned for the repeated execution of the query in the same transaction. Any other transaction attempting to update any rows in the tables in the query is blocked.
SingleUser In this isolation level only one connection is permitted to the database. The transaction has no locks and consumes less memory.

Refer to the documentation for ODBC for more information on isolation levels.

15.3 Configuring the Isolation Level

The default isolation level is READ COMMITTED. You can modify the isolation level for a data source name (DSN) by using the ODBC Administrator—which you can bring up by executing odbcad32—or by manually editing the ODBC.INI file. We recommend that you use the odbcad32 tool, as it will inform you if you have an incorrect combination of isolation level and cursor type. See Section 15.4, "Supported Combinations of Isolation Levels and Cursor Types" for more information.

When you bring up the ODBC Administrator, under the User DSN tab, double-click the Oracle Lite 40 ODBC driver for which you want to modify the isolation level. Select the default cursor type from the pull-down list.

If you decide to edit the ODBC.INI file by hand, then set the isolation level as follows:

IsolationLevel = XX

where the value for XX is Read Committed, Repeatable Read, Serializable, or Single User.

Alternatively, you can define the isolation level of a transaction by using the following SQL statement:



See Section 15.4, "Supported Combinations of Isolation Levels and Cursor Types", for information on how certain isolation levels and scrollable cursors sometimes cannot be used in combination.

15.4 Supported Combinations of Isolation Levels and Cursor Types

If you use the ODBC Administrator—which you can bring up by executing odbcad32—then this tool informs you if you are using an incorrect combination of isolation level and cursor type.

We support these types of cursors

The three supported types of scrollable cursors are as follows:

Refer to the documentation for ODBC for more information on cursor types.

For some cursors, you cannot combine them with certain isolation levels. Table 15-2 shows the supported combinations of isolation levels and cursor types. Unsupported combinations generate error messages.

Table 15-2 Supported Combinations

Forward Only Cursor Scrollable Static Cursor Scrollable Keyset Driven Cursor Scrollable Dynamic Cursor
Isolation Level

Read Committed Supported Supported Unsupported Unsupported
Repeatable Read Supported Unsupported Supported Supported
Serializable Supported Unsupported Supported Supported
Single User Supported Supported Supported Supported