2.10.7 Examples of locks

(1) Locks performed when the CREATE TABLE statement is executed (to define a base table)

When the CREATE TABLE statement is executed, the following resources are locked:

• Dictionary (exclusive mode (EX))

• Pre-processing table (exclusive mode (EX))

• DB area

  • Data DB area (exclusive mode (EX))

  • Dictionary DB area (shared retrieval mode (SR))

  • System-table DB area (shared retrieval mode (SR))

• Table

  • Processing-target table (exclusive mode (EX))

  • Dictionary table (exclusive mode (EX))

  • System table (cost information) (shared retrieval mode (SR))

  • System table (chunk information) (shared retrieval mode (SR))

Dictionaries are locked in exclusive mode (EX). Therefore, a definition SQL statement that is executed subsequently to a CREATE TABLE statement is placed in a wait state until execution of the CREATE TABLE statement finishes. Also, dictionary tables are locked in exclusive mode (EX). Therefore, a retrieval or update SQL statement that is executed subsequently to a CREATE TABLE statement will be placed in a wait state until execution of the CREATE TABLE statement finishes.

The following shows examples of cases where a retrieval SQL statement (SELECT statement) that is executed subsequently to a CREATE TABLE statement is placed in a wait state:

Example 1: When the SELECT statement is executed for a base table that is being defined

CREATE TABLE T1("ID" INT, "NAME" VARCHAR(100)) in ADBUTBL01   ... 1
SELECT * FROM T1                                              ... 2

The SELECT statement on line 2 is placed in a wait state until execution of the CREATE TABLE statement on line 1 finishes.

Example 2: When the SELECT statement is executed for a base table that is not a base table being defined

CREATE TABLE T1("ID" INT, "NAME" VARCHAR(100)) in ADBUTBL01   ... 1
SELECT * FROM T2                                              ... 2

Note: In this example, base table T2 is defined in a data DB area in which base table T1 is not defined.

The SELECT statement on line 2 is placed in a wait state until execution of the CREATE TABLE statement on line 1 finishes.

In addition to retrieval SQL statements, there are some operations which, if executed subsequently to a CREATE TABLE statement, are placed in a wait state until execution of the CREATE TABLE statement finishes. The following shows examples of operations that are placed in a wait state (other than retrieval SQL statements) when they are executed subsequently:

• INSERT statement

• UPDATE statement

• DELETE statement

• TRUNCATE TABLE statement

• PURGE CHUNK statement

• adbimport command

• adbdbstatus command

• adbexport command

• adbmergechunk command

To Page Top

(2) Locks performed when the adbimport command is executed (to import data)

The processing of locks performed when the adbimport command is executed differs depending on whether the background-import facility to be used.

To Page Top

(3) Locks performed when the adbexport command is executed (to export data)

When the adbexport command is executed, the following resources are locked in shared retrieval mode (SR):

• Single connection

• Pre-processing table

• Table

  • Processing-target table

  • Dictionary table

  • System table (cost information)

  • System table (chunk information)

  • System table (synonym dictionary information)

Therefore, you can execute any operations (other than those that lock the preceding resources) in exclusive mode (EX) simultaneously with the adbexport command. Operations that lock the preceding resources in exclusive mode (EX) will result in an error when attempting to lock a resource or will be placed in a wait state until the necessary resource is unlocked.

■ Operations that will result in an error when they are executed subsequently for the export-target table

The following shows examples of operations that will result in an error when they are executed subsequently to the adbexport command for the export-target table:

• Definition SQL statements

• TRUNCATE TABLE statement

• PURGE CHUNK statement

Note

The preceding operations can start without resulting in an error when they are executed for a table other than the export-target table (these operations are not affected by a lock on the data DB area).

■ Operations that will be placed in a wait state when they are executed subsequently for the export-target table

The following shows examples of operations that are placed in a wait state when they are executed subsequently to the adbexport command for the export-target table:

• adbimport command for which the background-import facility is disabled

• adbmergechunk command (in which the --purge-chunk option is omitted or in which WAIT is specified for the --purge-chunk option)

• adbreorgsystemdata command (if the export target is a system table)

Note

The preceding operations can start without waiting when they are executed for a table other than the export-target table (these operations are not affected by a lock on the data DB area).

To Page Top

(4) Locks performed when the adbmergechunk command is executed (to merge chunks)

When the adbmergechunk command is executed, the following resources are locked:

• Single connection (shared retrieval mode (SR))

• Pre-processing table (shared retrieval mode (SR))

• DB area

  • Data DB area (exclusive mode (EX))

  • System-table DB area (shared retrieval mode (SR))

• Table

  • Processing-target table (shared update mode (SU))

  • Dictionary table (shared retrieval mode (SR))

  • System table (cost information) (shared retrieval mode (SR))

  • System table (chunk information) (shared retrieval mode (SR))

Therefore, the following operations will result in an error when they are executed subsequently and attempt to reserve already locked resources:

• Subsequently executing an operation that locks a table stored in the same DB area as the merge chunk target table

• Subsequently executing an operation that locks the merge chunk target table in protected update mode (PU) or exclusive mode (EX)

• Subsequently executing an operation that locks a single connection, pre-processing table, dictionary table, or system table in exclusive mode (EX)

The following shows examples of operations^# that result in an error when they are executed subsequently to the adbmergechunk command:

• Definition SQL statements

• INSERT statement

• UPDATE statement

• DELETE statement

• TRUNCATE TABLE statement

• PURGE CHUNK statement

• adbimport command

• adbdbstatus command

  Note that the adbdbstatus command can start without waiting in the following cases:

  • When summary information for DB areas is output

  • When information is output by specifying the --shared-lock option

  • When information about the need for reorganization is output

#

Operations that meet any of the following conditions:

• The operation-target table of the SQL statement or command is the same as the merge chunk target table.

• The operation-target table of the SQL statement or command is stored in the same data DB area as the merge chunk target table.

• The operation-target table of the SQL statement or command is stored in the same data DB area as the index defined for the merge chunk target table.

• The index defined for the operation-target table of the SQL statement or command is stored in the same data DB area as the merge chunk target table.

To Page Top

(5) Locks performed when the adbdbstatus command is executed (to analyze the database status)

The processing of locks performed when the adbdbstatus command is executed differs depending on the information that will be output.

To Page Top

(6) Locks performed when the adbmodarea command is executed (to add or change a DB area)

When the adbmodarea command is executed, a single connection is locked in exclusive mode (EX). Therefore, all operations that lock a single connection (operations for connecting to the HADB server) will result in an error during execution of the adbmodarea command.

The following shows examples of operations that will result in an error when they are executed subsequently to the adbmodarea command:

• Definition SQL statements^#

• Retrieval SQL statements^#

• INSERT statement^#

• UPDATE statement^#

• DELETE statement^#

• TRUNCATE TABLE statement^#

• PURGE CHUNK statement^#

• adbimport command

• adbdbstatus command

• adbexport command

• adbmergechunk command

#

The SQL statement cannot be executed because an error occurs when the application program connects to the HADB server.

To Page Top

(7) Locks performed when the adbls command is executed (to display the status of the HADB server)

The adbls command does not lock any resources. Therefore, this command can be executed simultaneously with operations that lock resources.

To Page Top