Locking Granularities

TimesTen supports row-level locks, table-level locks and database-level locks.

Different connections can coexist with different levels of locking, but the presence of even one connection using database-level locking leads to reduced concurrency. For performance information, see Choose the Best Method of Locking.

Note:

When multiple locks have been obtained within the same transaction, the locks are released sequentially when the transaction ends.

  • Row-level locking: Transactions usually obtain locks on the individual rows that they access. Row-level locking is the recommended mode of operation because it provides the finest granularity of concurrency control. It allows concurrent transactions to update different rows of the same table. However, row-level locking requires space in the database's temporary memory region to store lock information.

    Row-level locking is the default. However, if it has been modified to another type of locking and you want to re-enable row-level locking, do one of the following:

    • Set the LockLevel connection attribute to 0.

    • In TimesTen, call the ttLockLevel built-in procedure with the lockLevel parameter set to Row. This procedure changes the lock level between row-level and database-level locking on the next transaction and for all subsequent transactions for this connection.

    • Run the ttOptSetFlag built-in procedure to set the RowLock parameter to 1, which enables the optimizer to consider using row locks.

    Note:

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

  • Table-level locking: Table-level locking is recommended when concurrent transactions access different tables or a transaction accesses most of the rows of a particular table. Table-level locking provides better concurrency than database-level locking. Row-level locking provides better concurrency than table-level locking. Table-level locking requires only a small amount of space in the temporary memory region to store lock information.

    Table-level locking provides the best performance for the following:

    • Queries that access a significant number of rows of a table

    • When there are very few concurrent transactions that access a table

    • When temporary space is inadequate to contain all row locks that an operation, such as a large insert or a large delete, might acquire

    To enable table-level locking, run the ttOptSetFlag procedure to set the TblLock parameter to 1, which enables the optimizer to consider using table locks. In addition, set RowLock to 0 so that the optimizer does not consider row-level locks.

    If both table-level and row-level locking are disabled, TimesTen defaults to row-level locking. If both table-level and row-level locking are enabled, TimesTen chooses the locking scheme that is more likely to have better performance. Even though table-level locking provides better performance than row-level locking because of reduced locking overhead, the optimizer often chooses row-level locking for better concurrency. For more information, see ttOptSetFlag in the Oracle TimesTen In-Memory Database Reference.

  • Database-level locking: Database-level locking serializes all transactions, which effectively allows no concurrency on the database. When a transaction is started, it acquires an exclusive lock on the database, which ensures that there is no more than one active transaction in the database at any given time. It releases the lock when the transaction is completed.

    Database-level locking can provide better performance than row-level locking, due to reduced locking overhead. In addition, it provides higher throughput than row-level locking when running a single stream of transactions such as a bulk load operation. However, its applicability is limited to applications that never run multiple concurrent transactions. With database-level locking, every transaction effectively runs in ANSI Serializable isolation, since concurrent transactions are disallowed.

    To enable database-level locking, do one of the following:

    • Set the LockLevel connection attribute to 1.

    • In TimesTen, call the ttLockLevel built-in procedure with the lockLevel parameter set to DS. This procedure changes the lock level between row-level and database-level locking on the next transaction and for all subsequent transactions for this connection.