Locking Levels

Locks are used to serialize access to resources to prevent one user from changing a resource that is being read or changed by another user.

TimesTen automatically performs locking for all database accesses.

• A shared lock enables the associated resource to be shared, depending on the operations involved (that is, you cannot alter or drop a row, table, or view if it is being shared). Several transactions can acquire shared 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 shared 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 performs 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:

• Row-Level Locking

• Table-Level Locking

• Database-Level Locking

Row-Level Locking

Row-level locking locks only the rows that are accessed by a transaction. It provides the best concurrency by enabling 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 or the ttOptSetFlag built-in procedure to manage row-level locking.

See ttLockLevel and ttOptSetFlag in the Oracle TimesTen In-Memory Database Reference.

Table-Level Locking

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.

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 does not use table-level locking for read operations unless it is explicitly requested by the application.

See ttOptSetFlag in the Oracle TimesTen In-Memory Database Reference.

Database-Level Locking

Locking at the database level locks an entire database when it is accessed by a transaction. All database-level locks are exclusive. Use the LockLevel general connection attribute or the ttLockLevel built-in procedure to implement database-level locking.

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 bulk loading, 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.

See ttLockLevel in the Oracle TimesTen In-Memory Database Reference.