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.

Section 15.1, "Locking"
Section 15.2, "What Are the Transaction Isolation Levels?"
Section 15.3, "Configuring the Isolation Level"
Section 15.4, "Supported Combinations of Isolation Levels and Cursor Types"

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. Note: If you are retrieving a large object, such as a BLOB, within a READ COMMITTED transaction, see the "Select Statement Behavior When Retrieving BLOBs in a READ COMMMITTED transaction" section in the Oracle Database Lite SQL Reference.
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.

Note:

The ODBC.INI file is available in Windows under %WINDIR% and in Linux under $OLITE_HOME/bin. For the Linux platform, you must have write permissions on the directory where this is located to be able to modify them.

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

SET TRANSACTION ISOLATION LEVEL <ISOLATION_LEVEL>;

where ISOLATION_LEVEL is READ COMMITTED, REPEATABLE READ, SERIALIZABLE, or SINGLE USER.

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

Forward only cursors allow you to only move forward through the returned result set. You cannot go backwards, nor can you view any additional modifications. To return to a row, you would have to close the cursor, reopen it and then move to the row you wanted to see. However, it is the fastest cursor for moving through a result set.
Scrollable cursors are the most flexible as they allow you to go forward and backward through the returned result set, but are also expensive. The other advantage of using a scrollable cursor is you can see modifications directly after they occur.

The three supported types of scrollable cursors are as follows:

Static—The result set appears to be static; that is, it does not detect modifications made to the membership, order, or values of the result set after the cursor is opened. This cursor can detect its own modifications, just not the modifications of others.
Dynamic—Any modifications to the result set can be detected and viewed when the row is re-fetched.
Keyset Driven—The abilities of this cursor is between the static and dynamic. It can detect modifications to the values in the rows of the result set; however, it cannot detect changes to the membership and order of the result set.

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