Transaction Isolation
Transaction isolation provides an application with the appearance that the system
processes one transaction at a time, even though there are concurrent connections to the
database. Applications can use the Isolation general connection attribute
to set the isolation level for a connection.
Concurrent connections can use different isolation levels.
Isolation level and concurrency are inversely related. A lower isolation level enables greater concurrency, but with greater risk of data inconsistencies. A higher isolation level provides a higher degree of data consistency, but at the expense of concurrency.
TimesTen supports two isolation levels:
Read Committed Isolation
Read committed isolation provides increased concurrency because readers do not block writers and writers do not block readers.
When an application uses read committed isolation, readers use a separate copy of the data from writers, so read locks are not needed. Read committed isolation is non-blocking for queries. Under read committed isolation, writers block only other writers and readers using serializable isolation; writers do not block readers using read committed isolation. Read committed isolation is the default isolation level.
TimesTen uses versioning to implement read committed isolation. TimesTen update operations create new copies of the rows they update to allow nonserializable read operations of those rows to proceed without waiting.
Figure 4-1 shows that some applications read a committed copy of the data while another application writes and reads on an uncommitted copy.
This isolation level is useful for applications that have long-running scans that may conflict with other operations needing access to a scanned row. However, the disadvantage when using this isolation level is that non-repeatable read operations are possible within a transaction or even a single statement (for example, the inner loop of a nested join).
When using this isolation level, DDL statements that operate on a table can block readers and writers of that table. For example, an application cannot read a row from a table if another application has an uncommitted DROP TABLE, CREATE INDEX, or ALTER TABLE operation on that table. In addition, blocking checkpoints block both readers and writers.
Read committed isolation does acquire read locks as needed during materialized view maintenance to ensure that views are consistent with their detail tables. These locks are not held until the end of the transaction but are instead released when materialized view maintenance has been completed.
Serializable Isolation
When an application uses serializable isolation, locks are acquired within a transaction and are held until the transaction commits or rolls back for both read and write operations.
As a result, a row that has been read by one transaction cannot be updated or deleted by any other transaction until the first transaction completes. Similarly, a row that has been inserted, updated, or deleted by a transaction cannot be accessed in any way by any other transaction until the first transaction completes.
This level of isolation provides for repeatable read operations and increased isolation within a transaction at the expense of decreased concurrency. Transactions use serializable isolation when database-level locking is chosen.
Figure 4-2 shows that locks are held until the transaction is committed.
Serializable isolation level is useful for transactions that require the strongest level of isolation. Concurrent applications that must modify the data that is read by another transaction may encounter lock timeouts because read locks are held until the transaction commits.