When two transactions attempt to access multiple resources but are waiting for the other to initiate a move, processing can become stuck; this is called deadlock.

Deadlock occurs most often between a referencing transaction and an updating (including deleting) transaction. It is, therefore, possible to reduce the frequency of deadlocks by changing the UAP access sequence.

The figure below shows an example of deadlock, in which two transactions simultaneously access a row with the same key. The figure also shows the relationship between the order in which locking is applied and deadlock.

Figure 3-8 Example of deadlock

With page-locking, there are situations in which deadlock cannot be prevented even though UAP access procedures are standardized.

The following figure shows an example of deadlock occurring in page-locking.

Figure 3-9 Example of deadlock in page-locking

In the example shown in Figure 3-9, the order in which rows are stored in a page cannot be standardized unless a cluster key is specified. Therefore, the sequence of UAP accesses cannot be standardized at the page level. In this case, ALTER TABLE must be used to change page locking to row locking in order to prevent deadlock from occurring.

Deadlock can occur between servers as well as within a single server. In a HiRDB/Parallel Server, deadlock between servers is called global deadlock.

Global deadlock occurs between a referencing transaction and an updating transaction in the same way that deadlock occurs within a single server, as shown in Figure 3-9. The frequency of these deadlocks can be reduced by changing the UAP access sequence.

The figure below shows an example of global deadlock, in which two transactions attempt to execute retrieval and updating in reverse order on tables stored on two separate servers. The figure also shows the relationship between the order in which locking is applied and deadlock.

Figure 3-10 Example of global deadlock

In this example, locking occurs between UAP1 and UAP2 within each back-end server; however, deadlock occurs at the front-end server because of lock-release wait.

Deadlock in a unit is detected by the unit's locking mechanism. In HiRDB, for a resource locked by the same server within the unit, deadlocks among multiple transactions on that server are detected. However, deadlocks on resources among multiple servers in the same unit cannot be detected other than by HiRDB based on occurrence of a lock timeout. Deadlocks on resources among multiple units are detected based on a timeout in the same manner as with deadlocks among multiple servers in the same unit.

(a) Deadlock detection method and timing

The deadlock detection method and timing depend on whether lock processing is distributed. For details about distribution of lock processing, see the HiRDB Version 9 System Operation Guide. The following table describes the deadlock detection method and timing.

Table 3-22 Deadlock detection method and timing

pd_lck_deadlock_check value	pd_lck_pool_partition value#	Deadlock detection method	Deadlock detection timing
Y	2 or greater	A deadlock monitoring process is applied periodically to detect deadlock (interval monitoring method).	Deadlocks are detected at the interval specified in the pd_lck_deadlock_check_interval operand. In such a case, there is a delay between occurrence of a deadlock and its detection.
	1	Deadlock is detected when a server process is placed in lock-release wait status (immediate detection method).	Deadlocks are detected as soon as they occur.
N	--	Deadlock is not detected. A UAP is placed in lock-release wait status for the amount of time specified in the pd_lck_wait_timeout operand and then a lock timeout error is issued to the UAP.	--

Legend:

--: Not applicable

#

pd_fes_lck_pool_partition operand value for a front-end server.

(b) Disabling deadlock detection

You can disable deadlock detection by specifying N in the pd_lck_deadlock_check operand.

If you increase the number of pool partitions for locking when you are using the interval monitoring method for deadlock detection, locking performance will likely be affected adversely each time a deadlock is detected. Therefore, in an application system in which deadlocks do not occur, you might be able to improve SQL execution performance by disabling deadlock detection. If you are able to configure a deadlock-free application system, you can disable deadlock detection.

For an application system in which deadlocks might occur, you should not disable deadlock detection. If a deadlock occurs after deadlock detection has been disabled, SQL statements will not terminate with an error until the time specified in the pd_lck_wait_timeout operand elapses and a timeout occurs. Additionally, HiRDB will not output deadlock information, which will make it difficult to determine the cause of a deadlock.

The two major causes of deadlock are:

• UAP access sequence (order in which lock is applied)
• Retrieving and updating in reverse order

There are other types of deadlock in addition to those shown in Figures 3-8, 3-9, and 3-10. The following table shows the major types of deadlocks and the countermeasures for each type.

Table 3-23 Deadlocks and their countermeasures

Deadlocked resources	Cause	Countermeasures
Row and row	UAP access sequence (shown in Figure 3-10)	Standardize UAP access sequence. Use LOCK TABLE to lock the table. Re-execute UAP after deadlock occurs.
Row and index key	Retrieval and updating in reverse order (shown in Figure 3-8)	Do not update the retrieved row. Do not update the values in a column to the same value. Minimize index definition. Use LOCK TABLE to lock the table. Perform NOWAIT retrieval. Re-execute UAP after deadlock occurs. Apply non-locking of index key values
Index key and index key	UAP access sequence	Standardize UAP access sequence. Minimize index definition. Use LOCK TABLE to lock the table. Perform NOWAIT retrieval. Re-execute UAP after deadlock occurs. Apply non-locking of index key values
Page and page	Rows are stored in a page in an unpredictable sequence (shown in Figure 3-9)	Use ALTER TABLE to change page locking to row locking.

Deadlock priority values can be used to control the transaction that is to be terminated with an error when a deadlock occurs. When deadlock priority control is specified in the pd_deadlock_priority_use operand in the system common definition, and deadlock priority values are specified in the PDDLKPRIO operand in the client environment definition, HiRDB determines the deadlock priority order of the transactions based on these specified values. Specification of a low value means a higher-processing priority; specification of a high value means that an error and rollback are more likely to occur. If two transactions have the same deadlock priority value, the error occurs for the transaction that was started later (this transaction is rolled back). If specification of the PDDLKPRIO operand is omitted, HiRDB triggers the error for the transaction that caused the deadlock (and rolls back this transaction), based on the type of UAP, the utility, and the operation command. For the default that is assumed when specification of the PDDLKPRIO operand is omitted, see 6.6.4 Environment definition information. Unless the transaction is terminated by a ROLLBACK or DISCONNECT statement, a UAP that is rolled back implicitly because of deadlock results in an error, even if an SQL statement is issued. When an X/Open-compliant UAP is used as a client in an OLTP environment, a transaction must be terminated, even if deadlock occurs in the UAP.

To enable output of deadlock information if a deadlock occurs, specify Y in the pd_lck_deadlock_info operand of the system definitions. For details about the pd_lck_deadlock_info operand, see the HiRDB Version 9 System Definition manual.

Although the frequency of deadlock occurrences can be reduced by enlarging the lock range, the concurrent execution capability of transactions declines. Conversely, while narrowing the lock range improves the concurrent execution capability, incorrect referencing and updating occurs, resulting in an increase in the deadlock frequency. To avoid deadlocks while maintaining concurrent execution capability, consider the measures listed as follows:

• Do not assign indexes to columns that are updated frequently
• Do not update retrieval conditions columns
• Specify the FOR UPDATE OF clause in the cursor definition only for columns to be updated
• Do not update values in a column (a unique index column in particular) to the same values (use the SET clause to specify only the columns that it is certain are updated to a different value)
• Specify the FOR UPDATE clause in the cursor specification to update or delete a row retrieved by the cursor
• Specify WITH EXCLUSIVE LOCK the columns that are updated after retrieval
• When assigning conditions to multiple columns, consider the use of a multicolumn index (to avoid expanding the retrieval range of single-column indexes)
• Consider the use of retrievals that use WITHOUT LOCK NOWAIT
• When accessing multiple tables, standardize the access sequence (if A is accessed before B, do not access A again; instead, save the value of A)
• Specify LOCK TABLE
• If a row must be updated immediately after being inserted with the INSERT statement, try to perform both steps in the same transaction
• To allow multiple UAPs to use multiple indexes with AND and update the same table simultaneously, specify 1 in the pd_work_table_option operand of the system definitions. For details about how to use multiple indexes with AND, see the explanation for PDSQLOPTLVL in 6.6.4 Environment definition information. For details about the pd_work_table_option operand, see the HiRDB Version 9 System Definition manual.
• Apply non-locking of index key values.
• If you employ the snapshot method, create a UAP that can retry operations in the event of deadlock, because unexpected deadlocks might occur.

As explained above, both the lock range and the lock sequence must be evaluated to avoid deadlock. The lock sequence depends on the SQL statement and index types. For details, see Section 3.4.9 Lock sequence based on SQL statement and index types.

If a plug-in uses logical files, use the EX mode to lock the files in logical file units for update manipulation, and use the PR mode for retrieval manipulation.

A logical file becomes locked while it is manipulated, regardless of the data value. Consequently, if an update transaction accesses a column that has a plug-in definition for using a logical file, logical file contention can occur between that transaction, and all other transactions that manipulate that column. To prevent contention, avoid executing any other programs while a program that updates columns with plug-in definitions for using logical files is being executed.

• Deadlock avoidance measure 1
  Specify LOCK TABLE.
  
• Deadlock avoidance measure 2
  If a logical file becomes a deadlocked resource, check whether the logical file is used for a data-type plug-in or for an index-type plug-in, and see Table 3-10 through Table 3-15. For details about the deadlock information that is output if a deadlock occurs, see the HiRDB Version 9 System Operation Guide.
  

  Lock information:

  Type 000e -> logical file

  First four bytes of lock information -> RDAREA number

Investigate the RDAREA name from the RDAREA number.

If the RDAREA is used for storing abstract data LOB attributes, the data is treated as a row.

If the RDAREA is used for a plug-in index, the data is treated as an index key.

Notes

• For a plug-in index retrieval, the logical file is locked in PR mode even if NOWAIT is specified.
• Even if a LOCK TABLE lock is applied, the logical file is locked in EX or PR mode during data manipulation.
• If multiple columns with plug-in definitions that use logical files have been defined, deadlock avoidance measure 2 cannot prevent deadlocks. Instead, use deadlock avoidance measure 1.