Oracle® Database Lite Developer's Guide
Part No. B15920-01
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
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.
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.
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
|Read Committed||In Oracle Database Lite, a
|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
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.
|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.
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:
SET TRANSACTION ISOLATION LEVEL <ISOLATION_LEVEL>;
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.
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.
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|