When a database is accessed by multiple transactions, there must be a way to coordinate concurrent changes to data with reads of the same data in the database. TimesTen and TimesTen Cache use transaction isolation and locks to coordinate concurrent access to data.
This chapter includes the following topics:
Transaction isolation provides an application with the appearance that the system performs 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:
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 nonblocking for queries and can work with Serializable isolation or read committed isolation. 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 and TimesTen Cache use versioning to implement read committed isolation. TimesTen and TimesTen Cache update operations create new copies of the rows they update to allow nonserializable reads 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.
Read committed isolation provides increased concurrency because readers do not block writers and writers do not block readers. 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 reads 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
CREATE INDEX, or
ALTER TABLE operation on that table. In addition, blocking checkpoints blocks 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 maintenance has been completed.
When an application uses serializable isolation, locks are acquired within a transaction and are held until the transaction commits or rolls back for both reads and writes. As a result, a row that has been read by one transaction cannot be updated or deleted by another transaction until the original transaction completes. Similarly, a row that has been inserted, updated, or deleted by a transaction cannot be accessed in any way by another transaction until the original transaction completes.
This level of isolation provides for repeatable reads 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 a transaction may encounter lock timeouts because read locks are held until the transaction commits.
Locks are used to serialize access to resources to prevent one user from changing an element that is being read or changed by another user. TimesTen and TimesTen Cache automatically perform locking for all database accesses.
A share lock enables the associated resource to be shared, depending on the operations involved (that is, you should not alter or drop a row, table, or view if it is being shared). Several transactions can acquire share locks on the same resource.
An exclusive lock ensures that there is no more than one active transaction in the database at any given time. This lock prevents the resource from being shared and is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released when the transaction completes.
Serializable transactions acquire share locks on the items they read and exclusive locks on the items they write. These locks are held until the transaction commits or rolls back. Read-committed transactions acquire exclusive locks on the items they write and hold these locks until the transactions are committed. Read-committed transactions do not acquire locks on the items they read. Committing or rolling back a transaction closes all cursors and releases all locks held by the transaction.
TimesTen and TimesTen Cache perform deadlock detection to report and eliminate deadlock situations. If an application is denied a lock because of a deadlock error, it should roll back the entire transaction and retry it.
Applications can select from three lock levels:
Locking at the database level locks an entire database when it is accessed by a transaction. All database-level locks are exclusive. A transaction that requires a database-level lock cannot start until there are no active transactions on the database. After a transaction has obtained a database-level lock, all other transactions are blocked until the transaction commits or rolls back.
Database-level locking restricts concurrency more than table-level locking and is useful only for initialization operations such as bulkloading, when no concurrency is necessary. Database-level locking has better response time than row-level or table-level locking at the cost of diminished concurrency and diminished throughput.
Different transactions can coexist with different levels of locking, but the presence of even one transaction that uses database-level locking leads to reduced concurrency.
LockLevel general connection attribute or the
ttLockLevel built-in procedure to implement database-level locking. See "ttLockLevel" in the Oracle TimesTen In-Memory Database Reference for more information.
Table-level locking locks a table when it is accessed by a transaction. It is useful when a statement accesses most of the rows in a table. Applications can call the
ttOptSetFlag built-in procedure to request that the optimizer use table locks. The optimizer determines when a table lock should be used.
Note:See "ttOptSetFlag" in the Oracle TimesTen In-Memory Database Reference for more information.
Table locks can reduce throughput, so they should be used only when a substantial portion of the table must be locked or when high concurrency is not needed. For example, tables can be locked for operations such as bulk updates. In read-committed isolation, TimesTen and TimesTen Cache do not use table-level locking for read operations unless it is explicitly requested by the application.
Row-level locking locks only the rows that are accessed by a transaction. It provides the best concurrency by allowing concurrent transactions to access rows in the same table. Row-level locking is preferable when there are many concurrent transactions, each operating on different rows of the same tables.
Applications can use the
LockLevel general connection attribute, the
ttLockLevel built-in procedure and the
ttOptSetFlag built-in procedure to manage row-level locking.
For more information about locks and transaction isolation, see "Transaction Management" in Oracle TimesTen In-Memory Database Operations Guide.