Nonstop Database, HiRDB Version 9 UAP Development Guide
This subsection describes the internal processing for making changes to an index (index maintenance that follows data change), and provides guidelines for UAP design with respect to index searches.
Indexes are used as an efficient means of narrowing down the rows that satisfy specified search conditions, as well as providing high-speed data access and return of search results.
HiRDB achieves a high-response, high-throughput system by enabling multiple transactions to concurrently perform index searches and index changes. However, if searches using indexes are performed while the indexes are being changed, the search results might be affected. One method for preventing inconsistent results is to lock the target table during a search (by executing the LOCK TABLE statement). However, this method cannot always be used due to system performance requirements.
If you run applications that execute multiple transactions concurrently and that require a precise sequencing of events, you should apply the UAP design guidelines discussed in this subsection when you develop your applications.
In addition, you should perform index searches with caution when you update columns that compose a multicolumn index (such as a status column).
An index change resulting from a change to a value in an index configuration column (UPDATE statement) is achieved by two processes, which are the index entry change processing for the pre-update column value and the index entry change processing for the post-update column value.
Index entry change processing for the pre-update column value means deletion of the index key when a row's data item contains the corresponding index key. The index entry change processing for the post-update column value means addition of an index key when a row's data item contains the corresponding index key. Deletion of an index key and addition of an index key are performed in this order in order to prevent an increase in the size of the index during index change processing.
The following figure shows an example of the internal processing for index change.
Figure 4-1 Example of internal processing for index change
If you perform an index search while index change processing is underway, the search results might differ. The following are two such cases:
This subsection discusses the case where the row being updated is excluded from the search target.
If you use the UPDATE statement to update an index key from the range for which an index search is currently underway to a range for which an index search has been completed, that index key will be excluded from the search target.
The following figure shows an example of a case where the row being updated is excluded from the search target.
Figure 4-2 Example of case where the row being updated is excluded from the search target (1)
If you execute the INSERT statement before you perform an index search, but add an index key to the range in which the index search has been completed, that index key is excluded from the search target.
The following figure shows an example of a case where the row being updated is excluded from the search target.
Figure 4-3 Example of case where the row being updated is excluded from the search target (2)
If, during index change processing, an index search is performed after the index key has been deleted, the corresponding row is excluded from the search target.
The following figure shows an example of a case where the row being updated is excluded from the search target.
Figure 4-4 Example of case where the row being updated is excluded from the search target (3)
Figure 4-5 Example of case where rows are no longer searched due to a change made to the index keys by the UPDATE statement
This subsection discusses the case where the row to be updated appears more than once in the search results.
If WITHOUT LOCK WAIT is specified as the lock option, the row to be updated might appear more than once in the search results because the search results are not guaranteed until transactions are completed. The same applies to the WITHOUT LOCK NOWAIT lock option. Specifically, if you use the UPDATE statement to update an index key from a range for which the index search has been completed to a range for which the index search has not been completed, that index key will be searched again.
The following figure shows an example of a case where the row to be updated appears more than once in the search results.
Figure 4-6 Example of case where the row to be updated appears more than once in the search results
The following table shows the possibility of obtaining different search results when an index is searched while the INSERT, UPDATE, and DELETE statements are executing.
Table 4-1 Possibility of obtaining different search results
Subsequent processing (search using index) | Prior processing | ||
---|---|---|---|
INSERT statement | UPDATE statement | DELETE statement | |
SELECT statement | MC | MC# | NC |
UPDATE statement | MC | MC | NC |
DELETE statement | MC | MC | NC |
If the subsequent processing is the INSERT statement, there is no possibility of getting different search results because the INSERT statement does not use indexes for search processing.
Whether a row to be updated appears more than once in the search results depends on how the index key values before and after update processing are changed. If the direction of the index key value change that takes place when the update transaction updates the index is the same as the direction of the index search by the retrieval transaction, the update target row might appear more than once in the search results, as shown in the following table:
Direction of index search | Direction of index key value change by the UPDATE statement | ||
---|---|---|---|
Index key value becomes larger | Index key value becomes smaller | Index key value remains the same (same-value update) | |
Ascending order (from smaller key value to larger key value) |
MC | NC | NC |
Descending order (from larger key value to smaller key value) |
NC | MC | NC |
If you execute multiple transactions concurrently and develop an application that requires a precise sequence of events, you must lock the applicable table for the UAP and serialize index changes and index search processes. If this affects performance, evaluate the following measures:
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.