12.18.3 Manipulating shared tables

Organization of this subsection
(1) Searching
(2) Updating

(1) Searching

Because a shared table can be referenced by all back-end servers, HiRDB selects the back-end server that is most suitable for searching the shared table. When a shared table is updated, deadlock may occur between the search and update processing because all back-end servers apply lock. To avoid deadlock, we recommend that you search a shared table as follows:

If a LOCK statement with IN EXCLUSIVE MODE specified is executed on a shared table, the RDAREA containing the target shared table and shared index is locked. If the same RDAREA is accessed, this lock occurs even if the table to be searched is not the target of the LOCK statement. Therefore, if another transaction is executing a LOCK statement with IN EXCLUSIVE MODE specified, the shared table cannot be accessed even when WITHOUT LOCK NOWAIT is specified. This means that the shared table cannot be searched while the LOCK statement with IN EXCLUSIVE MODE specified is executing.

For details about the rules by which HiRDB allocates back-end servers, see 12.18.5 Rules used to allocate back-end servers that search shared tables.

(2) Updating

To update a shared table, you must specify IN EXCLUSIVE MODE in the LOCK statement to lock the shared RDAREAs of all back-end servers. In the case of an UPDATE statement that does not change index key values, there is no need to issue the LOCK statement. An update to the shared table and shared index is written to the disk when the COMMIT statement is issued.

If you are using a local buffer to update a shared table, make sure that you issue the LOCK statement. If the shared table is updated without issuance of the LOCK statement and the server process terminates abnormally, the abort code Phb3008 is output (the unit may terminate abnormally).

(a) Updating involving LOCK statement issuance

To update a shared table with issuance of a LOCK statement:

  1. Issue the LOCK statement with IN EXCLUSIVE MODE specified.
    The LOCK statement locks not only the specified shared table but also the shared RDAREAs that contain the shared table and shared index. The global buffer for the shared RDAREA is disabled at the reference-only back-end server.
  2. Execute the INSERT, UPDATE, or DELETE statement for the shared table.
    The updatable back-end server applies the update information to the file.
    Because the shared RDAREA is locked until the LOCK statement is released, all accesses to other shared tables in the same shared RDAREA are placed in wait status.
  3. Release the LOCK statement.
Notes
  • Issue the LOCK statement at the beginning of the UAP. If any local server process has an open cursor to a table in the related shared RDAREAs, the LOCK statement results in an error.
  • When you create a procedure and trigger to update a shared table, specify the LOCK statement. If you execute the LOCK statement from a procedure and trigger, locking does not take place at the point where the transaction starts. This may result in an error.
  • The shared table, the shared RDAREA containing the shared table, and the shared RDAREA containing the shared index are locked at all back-end servers. If any application accesses a table or index in the corresponding RDAREA, deadlock or server-to-server global deadlock may occur.
  • If the unit for an updatable back-end server terminates abnormally and does not restart before a shared table updating transaction is completed, and the following search is executed, a lock timeout error occurs (KFPA11770-I message is displayed):
    [Figure]A reference-only back-end server on another unit searches a table in the RDAREA that contains the shared table being updated or an index defined for that table.
(b) Updating without LOCK statement issuance

If you do not issue a LOCK statement, you can execute only an UPDATE statement that does not change index key values. Use this method only for minor changes.

To update a shared table without issuing a LOCK statement:

  1. To place all back-end servers in the same status, distribute the update information to all back-end servers.
  2. The updatable back-end server applies the update information to the database.
    The reference-only back-end server updates information in the global buffer and retains the update information without applying it to the file until the COMMIT statement is issued. If the transaction rolls back, the data is restored in the global buffer.
Notes
  • At a reference-only back-end server, if all global buffers are under update processing and there is no available page before the COMMIT statement is issued, the transaction rolls back. Therefore, when you are not issuing a LOCK statement, do not update a large amount of data.
  • The rows to be updated are locked at all back-end servers. If any application accesses the corresponding table at the same time, deadlock or server-to-server global deadlock may occur. To avoid deadlock, we recommend that you use the UPDATE statement to update only one row per transaction.
  • If the unit for an updatable back-end server terminates abnormally and does not restart before a shared table updating transaction is completed, and the following search is executed, a lock timeout error occurs (KFPA11770-I message is displayed):
    [Figure]A reference-only back-end server on another unit searches a table in the RDAREA that contains the shared table being updated or an index defined for that table.