2.19.2 Nodes on which transactions and commands are executed

(1) Node on which a transaction is executed

The node on which a transaction is executed is determined by the specifications of the transaction access mode and transaction isolation level. A transaction that meets all of the following conditions is executed on a slave node:

• The transaction access mode is read-only mode.

• The transaction isolation level is READ COMMITTED.

However, depending on the situation such as the number of idle threads on the slave nodes, the transaction might be executed on the master node even when the preceding conditions are met. The HADB server on the master node selects the least loaded node, and the transaction is executed on that node.

Important

If you execute an application program that performs only search, do so in the following manners in order to effectively use the resources of the slave nodes:

• Set the transaction access mode to read-only mode.

• Set the transaction isolation level to READ COMMITTED.

For details about transaction access modes, see 2.9.5 Transaction access modes.

For details about transaction isolation levels, see 2.9.2 Transaction isolation levels supported by HADB.

Note that while a transaction in read/write mode is being executed on the master node, all transactions are executed on the master node (no transactions are executed on slave nodes). Transactions whose transaction access mode is read-only mode and transaction isolation level is READ COMMITTED are not exceptions. Therefore, to effectively use the resources of the slave nodes, make sure that a transaction in read/write mode is committed as frequently as possible.

Important

If the multi-node function is used, a transaction that meets all of the following conditions automatically terminates normally when the cursor is closed:

• The transaction access mode is read-only mode.

• The transaction isolation level is READ COMMITTED.

If cursors are concurrently opened with multiple statement handles, the transaction automatically terminates normally when all open cursors are closed.

To Page Top

(2) Nodes on which SQL statements are executed

The following table shows the nodes on which SQL statements are executed.

Table 2‒22: Nodes on which SQL statements are executed

No.	SQL statement type			Node on which SQL statements are executed
1	Definition SQL statements			Master node
2	Data manipulation SQL statements	Update SQL statements	INSERT statement	Master node
3			UPDATE statement
4			DELETE statement
5			TRUNCATE TABLE statement
6			PURGE CHUNK statement
7		Retrieval SQL statements	SELECT statement	Master node or slave node

■ Notes on executing a definition SQL statement, the TRUNCATE TABLE statement, or the PURGE CHUNK statement

While transactions are being executed on slave nodes, a definition SQL statement, the TRUNCATE TABLE statement, or the PURGE CHUNK statement is not executed immediately (and is placed in WAIT status). The definition SQL statement, TRUNCATE TABLE statement, or PURGE CHUNK statement is executed after all transactions running on the slave nodes terminate.

Also, while a definition SQL statement, the TRUNCATE TABLE statement, or the PURGE CHUNK statement is being executed, all transactions are executed on the master node (no transactions are executed on slave nodes).

▪ Notes on executing the INSERT, UPDATE, or DELETE statement

If either of the following conditions is met, the INSERT, UPDATE, or DELETE statement is not executed immediately (and is placed in WAIT status):

• A transaction that accesses the DB area that contains the table to be processed by the INSERT, UPDATE, or DELETE statement is being executed on a slave node.

• A transaction that accesses the DB area that contains the indexes of the table to be processed by the INSERT, UPDATE, or DELETE statement is being executed on a slave node.

After the transaction that meets either of the preceding conditions terminates, the INSERT, UPDATE, or DELETE statement is executed.

When the INSERT, UPDATE, or DELETE statement is executed, the processing of an SQL statement that is running on a slave node and meets either of the following conditions is switched to the master node:

• An SQL statement that accesses the DB area that contains the table to be processed by the INSERT, UPDATE, or DELETE statement

• An SQL statement that accesses the DB area that contains the indexes of the table to be processed by the INSERT, UPDATE, or DELETE statement

▪ Cases in which retrieval SQL statements result in an error

If the multi-node function is used, retrieval SQL statements that meet all of the following conditions cannot be executed:

1. An attempt is made to concurrently perform retrieval SQL statements by using multiple statement handles in the same transaction.

2. One of the retrieval SQL statements is accessing the data DB area.

3. A retrieval SQL statement (that is not the one in condition 2) attempts to access the data DB area that is being accessed by an update SQL statement (the retrieval SQL statement results in an error).

Example:

Explanation

Assume that Transaction A, which is a read/write transaction, and Transaction B, which is a read-only transaction, are being executed concurrently.

In Transaction B, an attempt is made to simultaneously perform retrieval SQL statements by using two statement handles (1 and 3 in the figure).

1. The preprocessing of the retrieval SQL statement for table T2 is being executed by using the statement handle 1.

2. In Transaction A, the INSERT statement is being executed for table T1.

3. After the INSERT statement in 2 is executed (Transaction A is not committed), Transaction B uses statement handle 2 (that is not statement handle 1) to execute the preprocessing of the retrieval SQL statement for table T3.

When the processing in 1 and the processing in 3 are executed concurrently, the retrieval SQL statement in 3 results in an error. At this time, the KFAA31898-E message is output. Take action as instructed in the KFAA31898-E message.

To Page Top

(3) Nodes on which commands can be executed

The nodes on which commands can be executed differ depending on the commands. For details about the nodes on which different commands can be executed, see Nodes on which commands can be executed when the multi-node function is used in the manual HADB Command Reference.

To Page Top

(4) Restrictions on simultaneously executing commands with transactions

There are some commands that cannot be simultaneously executed with transactions. The following table shows whether the command can be executed simultaneously with transactions.

Table 2‒23: Whether the command can be executed simultaneously with transactions

No.	Command name		Whether simultaneous execution is possible
1	adbarchivechunk		B
2	adbchgchunkcomment		B
3	adbchgchunkstatus		B
4	adbgetcst		B
5	adbidxrebuild		B
6	adbimport	background import	A
7		Other types of import	B
8	adbinit		C
9	adbmergechunk		B
10	adbmodarea		C
11	adbreorgsystemdata		B
12	adbsql		A#
13	adbunarchivechunk		B
14	Other commands		A

Legend:

A: Command that can be executed simultaneously with transactions

B: Command that can be executed simultaneously with transactions conditionally. For details, see ▪ Commands that can be executed simultaneously with transactions conditionally described later in this manual.

C: Command that cannot be executed simultaneously with transactions

#

If READ_ONLY is specified for the adb_clt_trn_access_mode operand in the client definition, the command can be executed simultaneously with transactions that are being executed on slave nodes.

▪ Commands that can be executed simultaneously with transactions conditionally

For commands with B in the Whether simultaneous execution is possible column in Table 2‒23: Whether the command can be executed simultaneously with transactions, the following restrictions apply:

• While a B-type command is being executed, all transactions are executed on the master node (no transactions are executed on slave nodes). Transactions whose transaction access mode is read-only mode and transaction isolation level is READ COMMITTED are not exceptions.

• While transactions are being executed on slave nodes, a B-type command is not executed immediately (and is placed in WAIT status). The B-type command is executed after all transactions that are being executed on slave nodes terminate.

To Page Top