Nonstop Database, HiRDB Version 9 UAP Development Guide
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.
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. | -- |
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:
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) |
|
Row and index key | Retrieval and updating in reverse order (shown in Figure 3-8) |
|
Index key and index key | UAP access sequence |
|
Page and page | Rows are stored in a page in an unpredictable sequence (shown in Figure 3-9) |
|
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:
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.
Investigate the RDAREA name from the RDAREA number.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.