Transaction Isolation Levels

Transaction isolation enables each active transaction to operate as if there were no other transactions active in the system.

Isolation levels determine if row-level locks are acquired when performing read operations. When a statement is issued to update a table, locks are acquired to prevent other transactions from modifying the same data until the updating transaction commits or rolls back and then releases its locks.

The Isolation connection attribute sets the isolation level for a connection. The isolation level cannot be changed in the middle of a transaction.

TimesTen supports the following two transaction isolation levels:

ANSI Read Committed isolation: The read committed isolation level is the recommended mode of operation for most applications, and is the default mode. It enables transactions that are reading data to run concurrently with a transaction that is updating the same data. TimesTen makes multiple versions of data items to allow non-serializable read and write operations to proceed in parallel.

Read operations do not block write operations and write operations do not block read operations, even when they read and write the same data. Read operations do not acquire locks on scanned rows. Write operations acquire locks that are held until the transaction commits or rolls back. Readers share a committed copy of the data, whereas a writer has its own uncommitted version. Therefore, when a transaction reads an item that is being updated by another in-progress transaction, it sees the committed version of that item. It cannot see an uncommitted version of an in-progress transaction.

Read committed isolation level provides for better concurrency at the expense of decreased isolation because of the possibility of non-repeatable reads or phantom rows within a transaction. If an application runs the same query multiple times within the same transaction, the commit of an update from another transaction may cause the results from the second read operation to retrieve different results. A phantom row can appear (in modified form) in two different read operations within the same transaction, as the result of an early release of read locks during the transaction.

To set read committed isolation level, if previously modified since this is the default, do one of the following:
- Connect with the Isolation connection attribute set to 1. You can also modify this value with the ALTER SESSION SQL statement.
```
ALTER SESSION SET ISOLATION_LEVEL=READ COMMITTED;
```
  See Isolation in the Oracle TimesTen In-Memory Database Reference or ALTER SESSION in Oracle TimesTen In-Memory Database SQL Reference.
- When using ttIsql, run isolation 1 or isolation READ_COMMITTED.
- ODBC applications can run the SQLSetConnectOption ODBC function with the SQL_TXN_ISOLATION flag set to SQL_TXN_READ_COMMITTED. See Option Support for ODBC 2.5 SQLSetConnectOption and SQLGetConnectOption in the Oracle TimesTen In-Memory Database C Developer's Guide.
- JDBC applications can run the setTransactionIsolation JDBC method of the Connection object to TRANSACTION_READ_COMMITTED.
ANSI Serializable isolation: All locks acquired within a transaction by a read or write operation are held until the transaction commits or rolls back. Read operations block write operations, and write operations block read operations. But read operations do not block other read operations.
- If an initial transaction reads a row, a second transaction can also read the row.
- A row that has been read by an initial transaction cannot be updated or deleted by a second transaction until the initial transaction commits or rolls back. However, the second transaction can insert rows that are not in the same range that are locked by the first transaction. For example, if the first transaction is reading rows 1 through 10, then the second transaction cannot update rows 1 through 10. However, the second transaction can insert into rows greater than 10.
- A row that has been inserted, updated or deleted by an initial transaction cannot be accessed in any way by a second transaction until the initial transaction either commits or rolls back.
Serializable isolation level provides for repeatable reads and increased isolation at the expense of decreased concurrency. A transaction that runs the same query multiple times within the same transaction is guaranteed to see the same result set each time. Other transactions cannot update or delete any of the returned rows, nor can they insert a new row that satisfies the query predicate.

To set the isolation level to serializable isolation, do one of the following:
- Connect with the Isolation connection attribute set to 0. You can also modify this value with the ALTER SESSION SQL statement.
```
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE ;
```
  See Isolation in the Oracle TimesTen In-Memory Database Reference or ALTER SESSION in Oracle TimesTen In-Memory Database SQL Reference.
- When using ttIsql, run isolation 0 or isolation SERIALIZABLE.
- ODBC applications can run the SQLSetConnectOption ODBC function with the SQL_TXN_ISOLATION flag set to SQL_TXN_SERIALIZABLE.
- JDBC applications can run the setTransactionIsolation JDBC method of the Connection object to TRANSACTION_SERIALIZABLE.
To ensure that materialized views are always in a consistent state, all view maintenance operations are performed under serializable isolation, even when the transaction is in read committed isolation. This means that the transaction obtains read locks for any data items read during view maintenance. However, the transaction releases the read locks at the end of the INSERT, UPDATE or CREATE VIEW statement that triggered the view maintenance, instead of holding them until the end of the transaction.

Note:

The ttXactAdmin utility generates a report showing lock holds and lock waits for all outstanding transactions. It can be used to troubleshoot lock contention problems where operations are being blocked, or encountering lock timeout or deadlock errors. It can also be used to rollback a specified transaction.