Scalable Database Server, HiRDB Version 8 UAP Development Guide
![[Contents]](FIGURE/CONTENT.GIF)
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
![[Next]](FIGURE/AFTER.GIF)
Although locking is controlled automatically by the HiRDB system, using the UAP to change the unit of locking sometimes reduces the locking overhead, resulting in better processing efficiency. Consider the items listed below when you design UAP:
- Organization of this subsection
- (1) Search
- (2) Update
- (3) Deletion
- (4) Notes
(1) Search
- If retrieval results will be referenced only once and the data need not be locked until COMMIT occurs, specify WITHOUT LOCK in the SELECT statement.
When WITHOUT LOCK is specified, lock is released without waiting for transaction termination, thus resulting in better concurrent execution capability of transactions.
Even if WITHOUT LOCK NOWAIT is specified, a table undergoing data processing by the database load utility (except when nowait=yes is specified in the option statement) or the database reorganization utility (except when -k unld is specified) cannot be searched.
- In cases other than the one above, lock the target table in the PR mode with the LOCK statement with SHARE specified.
When a table is locked in advance with the LOCK statement, the overhead is reduced significantly because locking on a row or table basis does not occur. A lock buffer shortage can also be prevented.
- When you search a shared table, we recommend that you specify WITHOUT LOCK or WITH ROLLBACK.
(2) Update
- Before updating, lock the target table in the EX mode with the LOCK statement with EXCLUSIVE specified.
When a table is locked in advance with the LOCK statement, the overhead is reduced significantly because locking on a row basis does not occur. A lock buffer shortage can also be prevented.
- When updating a shared table (including addition and deletion) where the key value of an index is changed, or when updating a large section of a shared table, always lock the shared table with an EXCLUSIVE-specified LOCK statement. Note that when a shared table is locked with an EXCLUSIVE-specified LOCK statement, the RDAREA for indexes (shared RDAREA), which stores indexes defined for the shared table, is also locked.
(3) Deletion
- When dropping a table or an index or when deleting all rows, lock in the EX mode all segments being used for the target table.
If many segments are being used for the table, all of those segments must be locked in the EX mode. To do this, have the transaction occupy all of the segments until the COMMIT statement is executed. Note that in this case, a large table for managing locked resources is required in the lock buffer. Care must be taken especially when one of the following statements is used to delete a table, its schema, its indexes, or all its rows:
- DROP TABLE statement
- DROP SCHEMA statement
- DROP INDEX statement
- PURGE TABLE statement
- To delete a schema that applies to multiple tables, the individual tables should be deleted before the schema is deleted.
This method uses less memory.
- When you lock a table with a LOCK statement, avoid simultaneous execution of other online transactions because those transactions remain in the wait status for a long time. However, no wait is involved for NOWAIT searches of unshared tables.
- When a NOWAIT search is performed on a shared table, the shared table cannot be accessed if another user has executed an EXCLUSIVE-specified LOCK statement on that table.
All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.