Nonstop Database, HiRDB Version 9 UAP Development Guide
Normally, when an index key is deleted, it is removed from the database before the deletion transaction is committed. If the deletion transaction rolls back, the contents of the index key are recovered but the index key is excluded from searches by other retrieval transactions that are executing concurrently. The following figure shows an example of a deleted index key that is excluded from searches.
Figure 3-13 Deleted index key that is excluded from searches
By locking uncommitted data until the deletion transaction is committed, you can prevent uncommitted data from being excluded from being a retrieval target by other retrieval transactions that are executing concurrently.
For the following types of jobs, it is recommended that you lock uncommitted data that is to be deleted:
You lock uncommitted data to be deleted by specifying WAIT in the pd_lock_uncommited_delete_data operand. For details about the pd_lock_uncommited_delete_data operand, see the manual HiRDB Version 9 System Definition.
Effects of locking uncommitted data to be deleted are as follows:
The following table describes the processing of retrieval transactions for each SQL execution condition when uncommitted data to be deleted is locked, and when it is not locked.
Table 3-24 Processing of retrieval transactions for each SQL execution condition
No. | SQL execution condition | Processing of retrieval transaction when rollback occurs on the UPDATE or DELETE statement | |||
---|---|---|---|---|---|
SQL statements executed concurrently | Nature of processing by UPDATE or DELETE statement | Nature of processing by SELECT statement | When data to be deleted is not locked | When data to be deleted is locked | |
1 | DELETE and SELECT statements are executed concurrently. | DELETE statement for deleting rows | The keys to be deleted by the DELETE statement are included in the range of search conditions, or deleted rows are referenced. | Skips the deleted rows and keys without waiting for settlement of the transaction that executed the DELETE statement | Waits for settlement of the transaction that executed the DELETE statement and references the deleted rows and keys |
2 | UPDATE and SELECT statements are executed concurrently on a table for which indexes are defined. | UPDATE statement for updating indexes | The index keys to be updated by the UPDATE statement are included in the range of search conditions. | Skips non-updated keys without waiting for settlement of the transaction that executed the UPDATE statement | Waits for settlement of the transaction that executed the UPDATE statement and references the non-updated keys |
3 | UPDATE and SELECT statements are executed concurrently on a table for which multicolumn indexes are defined. | UPDATE statement that specifies some of the columns composing the multicolumn index in the search condition, and that updates other columns composing the index | The multicolumn index keys to be updated by the UPDATE statement are included in the range of search conditions. | Skips non-updated keys without waiting for settlement of the transaction that executed the UPDATE statement | Waits for settlement of the transaction that executed the UPDATE statement and references the non-updated keys |
If uncommitted data to be deleted is locked, index key entries existing before deletion or update processing remain in the index. As a result, null-value unique index keys and entries of a non-unique index remain in indexes. If lock-release wait status occurs for such remaining entries, deadlock might result. If a large number of entries remains in indexes, retrieval performance might be affected adversely.
Basically, the number of remaining entries increases each time an index key is updated or deleted. To avoid lock-release waits or deadlocks for remaining entries, you should delete remaining entries with the database reorganization utility (pdrorg) or the free page release utility (pdreclaim) whenever a certain amount of update or deletion processing has occurred. You can use the database condition analysis utility (pddbst) to determine the total number of remaining entries.
Remaining entries are deleted in the following cases:
Reducing the frequency of index update processing also helps avoid remaining entries.
For areas storing table row data, entries also remain after rows are deleted. If uncommitted data to be deleted is locked, these remaining entries are also checked. This results in some overhead for skipping invalid data and locking.
Although unexpected locking does not occur on remaining entries for tables, unlike remaining entries for indexes, you should delete remaining entries by executing pdrorg or pdreclaim whenever a certain amount of update or deletion processing has occurred. You can use the database condition analysis utility (pddbst) to determine the total number of remaining entries.
Reducing the frequency of row deletion processing also helps avoid remaining entries.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.