For the following types of jobs, it is recommended that you lock uncommitted data that is to be deleted:

• A job in which the nature of one transaction's processing depends on the results of a preceding transaction's processing
• A job that will not re-execute in the event of rollback

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:

• If a retrieval transaction detects uncommitted data to be deleted during retrieval processing, it waits until the deletion transaction is committed or rolled back. As a result, the uncommitted data to be deleted is not skipped by the retrieval transaction even when the deletion transaction rolls back.
• The uniqueness constraint is guaranteed in the same manner as with locking of index key values. This prevents detection of a uniqueness error in the event the deletion transaction rolls back.

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

Example of No. 1:

This example executes DELETE and SELECT statements concurrently on the data COL1=1 in the table TBL for which an index is defined for column COL1.

Example of No. 2:

This example executes UPDATE and SELECT statements concurrently on the data COL1=1 in the table TBL for which an index is defined for column COL1.

Example of No. 3:

This example executes UPDATE and SELECT statements concurrently on the data COL1=1 and COL2=1 in the table TBL for which a multicolumn index is defined for columns COL1 and COL2.

(a) Remaining entries for indexes

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:

• When an attempt is made to add indexes to a page containing remaining entries and the index page is split due to a shortage of free space in the page
• After a deletion transaction has been settled, the pdreclaim command with the -x option specified is executed for the corresponding index.
• After a deletion transaction has been settled, pdrorg is executed on the corresponding index or the table corresponding to the index.
• PURGE TABLE is executed on the table corresponding to a deleted index.
• An RDAREA containing a deleted index or a table corresponding to such an index is re-initialized.

Reducing the frequency of index update processing also helps avoid remaining entries.

(b) Remaining entries for tables

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.