Verify Lock and Isolation Levels

The manner in which multiple applications concurrently access the database can have a major impact on performance.

An application can acquire locks on the entire database, individual tables, and individual rows. Additionally, applications can set an isolation level that determines whether they hold read and update locks until their transactions commit or roll back.

Check the SYS.SYSTEMSTATS table, the SYS.MONITOR table, or use the ttXactAdmin utility to detect whether an application is spending time waiting for locks. See Check for Deadlocks and Timeouts and Using the ttXactAdmin Utility for information.

If lock contention is high, you may be able to improve the overall performance of your system by implementing the following:

• Set the LockLevel configuration attribute or use the ttLockLevel procedure to place locks on rows, rather than on the entire database. Row locking is the default.

• Use the ttOptSetFlag procedure to prevent the query optimizer from placing locks on tables. Table locks are sometimes the default, particularly for updates that affect many rows.

• Use read-committed isolation level (Isolation=1, the default) for those applications do not require serializable access to the transaction data.

If you see a lot of lock contention, but the above settings are all set to minimize contention, then the contention may be related to the application itself. For example, concurrent threads may be repeatedly accessing the same row. The ttXactAdmin utility can sometimes help you detect this sort of contention. Tracing can also be useful in this situation.

For more information about locks and isolation levels, see Concurrency Control Through Isolation and Locking in the Oracle TimesTen In-Memory Database Operations Guide.