Performance During Concurrency

When multiple users are performing selects, updates, inserts, and deletes on the same set of tables concurrently, consider the following when evaluating performance:

• If there are no users changing data in a set of tables, multiple, concurrent users reading data have no performance problems associated with concurrency. There are no deadlock problems, either.

  Once a writer mixes with the readers of a table, concurrent performance is affected, because the writer can acquire exclusive write locks on pages or tables. Deadlocks can occur, causing reduced performance for users who are "backed out" from the deadlock.

• Remember that locks acquired during a multiple query transaction are held until the commit statement is executed. This can affect concurrent performance. Query-By-Forms uses multiple query transactions.
• Whenever possible, users must work in their own tables or download into their own tables with create table as select statements. Doing so offloads tables where there is heavy concurrent activity.
• Nolock can be beneficial in certain situations.
• Use can be made of the Visual Forms Editor's form validations, rather than table-lookup validations that lock the reference table, because they are read only at form start-up time.

Approaches for Handling Heavy Concurrent Usage

In a heavy concurrent usage situation, there are two approaches:

• The "never-escalate-at-any-cost" approach

  Concurrent users are working in different regions of the table. Extreme care is taken by the person whose role it is to deal with concurrency problems (the system administrator or the DBA, or both), to ensure that nobody escalates to a table-level lock.

• The "table lock" approach

  This approach, which minimizes the occurrence of deadlock, applies when there is much concurrent activity on smaller tables or in one part of a larger table.

The Never Escalate Approach

The "never-escalate" approach is appropriate when the users are working in different parts of the table, they are running simple queries and updates, and making full use of primary and secondary indexes. The goal is to have users coexist as much as possible in the same tables, where no one impedes another user's performance by acquiring table locks.

Considerations of the "never escalate" approach include:

• A single-table keyed query starts with page locking, unless the set lockmode statement has been issued. Page locks are acquired until maxlocks is reached, at which point lock escalation occurs. By checking the tuple identifiers (tids) of rows visited, you can estimate the number of pages visited in a specific table.
• More complex queries can remove a table-level lock, if the query optimizer thinks that maxlocks pages are used.
• Make sure that you are using primary and secondary indexes effectively. Check how many pages are returned from a keyed, primary or secondary lookup to check that it is less than maxlocks for that table. The optimize database operation must be run at least on primary and secondary keys to help the optimizer make estimates.
• Monitor overflow levels in tables with ISAM and hash primary and secondary indexes.
• It is advisable to reduce fillfactors to lower than the default if tables with ISAM or hash storage structures are used, because this provides more room in the table after the modify.
• Make sure maxlocks is set to an appropriate figure, such as ten percent of table size.

When choosing storage structures while using the "never escalate" approach, the basic principle is that ISAM or hash structures with little or no overflow are better than small B-trees in a concurrent environment. The reason is that growing B-trees involve some locking when index pages split.

However, as the percentage of overflow builds up in the hash or ISAM structure, they become inferior to B-trees, because locks are held down overflow chains. In particular, if any overflow chain being visited is greater than maxlocks, escalation to table locks can occur. This increases the risk of deadlocks when there are multiple users in the same table.

At what point the trade-off occurs depends on the circumstances, such as how frequently modify statements can be performed. Experimentation is advised. Overflow buildup must be checked in secondary indexes as well as primaries.

Concurrent performance analysis is much more difficult to analyze than single user performance. Be prepared to experiment using the guidelines presented.

The Table Lock Approach

The "table lock" approach is used only when there are unsolvable bottlenecks. The philosophy behind the approach says that it is better to have users queue up in an orderly manner to get into a table, thereby avoiding the risk of deadlock, than have them waste time backing out of deadlock situations.

Important! Before using this approach, ensure that lock escalation and transaction size are minimized.

This approach is appropriate when extensive table scanning is needed, as with set functions such as max and min. In these cases it is advisable to keep an extra table around containing max and min values, or to search for max and min values directly in a secondary index without reference to the base table.

In multiple query transactions, table-level locks reduce the likelihood of deadlocks but do not eliminate them. The following statement reduces the likelihood of deadlock in a multiple query transaction:

set lockmode on tablename
where level = table;

This also applies to B-tree tables when they are small.

Under some circumstances setting readlock = exclusive is useful. For example, when running a select followed by an update statement.