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.

Figure 3-8 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.

Figure 3-9 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.

Figure 3-10 shows an example of global deadlock, in which two transactions try to execute retrieval and updating in reverse order on tables stored in 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.

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. Table 3-22 shows the major types of deadlocks and their countermeasures.

Table 3-22 Deadlocks and their countermeasures

Deadlocked resources	Cause	Countermeasure
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 8 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 8 System Definition manual.
• Apply non-locking of index key values.

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.8 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 8 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.