Nonstop Database, HiRDB Version 9 UAP Development Guide
![[Contents]](FIGURE/CONTENT.GIF)
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
3.4.10 Lock sequence for rows
HiRDB accesses data on the basis of the access path determined by SQL optimization. Normally, this access path determines the lock sequence for rows. However, the lock sequence for rows can vary depending on conditions during SQL execution. The following are examples of cases where the lock sequence for rows depends on conditions during SQL execution:
- A parallel server is used together with multiple back-end servers to access data
- A snapshot method is used
This subsection discusses the lock sequence for rows based on the access path and the cases where the lock sequence for rows varies.
- Organization of this subsection
- (1) Lock sequence for rows based on the access path
- (2) Case where the lock sequence for rows varies
- (3) How to handle deadlocks
(1) Lock sequence for rows based on the access path
HiRDB accesses data on the basis of the path determined by SQL optimization. The lock sequence for rows is determined by this access path.
The following example explains the basic sequence of data access.
- Example
- SQL statement to be executed:
select * from t1,t2 where t1.c1=t2.c1 and t1.c1>3
- Conditions:
(2) Case where the lock sequence for rows varies
This subsection discusses the case where the lock sequence for rows varies according to SQL execution conditions.
(a) HiRDB/Parallel Server
When tables are searched or updated in the parallel mode in order to improve performance, HiRDB pre-reads data and performs parallel data read operations. Therefore, the sequence of data accesses among joined tables might differ from what it would be on a single server, which also means that the lock sequence for rows would be different as well. As a result, deadlocks might occur.
Partitioned-table search
- The following example explains the sequence of data accesses in a partitioned-table search.
- Example
- SQL statement to be executed:
select * from t1 where t1.c1>3
- Conditions:
- HiRDB/Parallel Server is used.
- For the table t1, rows 1 through 8 are stored in BES1, and rows 9 through 16 are stored in BES2.
- Table T1's rows 1, 2, 5, 6, 10, 11, 14, and 15 satisfy the condition t1.c1 > 3. In BES1, the index is used to access rows 5, 2, 1, and 6 in this order. In BES2, the index is used to access rows 10, 14, 11, and 15 in this order.
- The snapshot method is not used.
![[Figure]](FIGURE/ZU030425.GIF)
- Explanation:
- The access sequence within each back-end server can be determined, but the access sequence for BES1 and BES2 cannot be determined because data can be pre-read or it can be read in the parallel mode. Also, the access sequence for the rows in BES1 and BES2 cannot be determined.
Join search
- The following example explains the sequence of data accesses in a join search.
- Example
- SQL statement to be executed:
select * from t1,t2 where t1.c1=t2.c1 and t1.c1>3
- Conditions:
- Explanation:
- In the figure, the access sequence for A through D is as follows:
- Within each of the groups A through D, rows are accessed in the order indicated by the circled numbers.
- B and C are accessed after A has been accessed.
- The access sequence for B and C cannot be determined because data can be pre-read or it can be read in the parallel mode.
- The access sequence for rows in B and C cannot be determined because data can be pre-read or it can be read in the parallel mode.
(b) When using the snapshot method
When the snapshot method is used, the lock sequence for rows changes each time an SQL statement is executed.
This subsection explains the sequence of locking for rows in the following three cases:
- When the snapshot method is used (only one transaction is executed at a time)
- When the snapshot method is used (multiple transactions are executed concurrently)
- When the snapshot method is not used
The following SQL statement is used for the example.
- Example
select * from t1,t2 where t1.c1=t2.c1 and t1.c1>3
When the snapshot method is used (only one transaction is executed at a time)
- If the snapshot method is used and the page to be referenced contains a row that is being updated (including rows retrieved by the WITH EXCLUSIVE LOCK lock option), all rows on the page are locked.
- The following shows the sequence of data accesses during SQL execution:
![[Figure]](FIGURE/ZU030426.GIF)
- Explanation:
- The row 1s in tables T1 and T2 indicate that the values of columns specified in the join condition are the same.
- All rows in tables T1 and T2 are stored on the same page.
When the snapshot method is used (multiple transactions are executed concurrently)
- If the snapshot method is used and the page to be referenced contains a row that is being updated (including rows retrieved by the WITH EXCLUSIVE LOCK lock option), all rows up to that row are locked. After that, the lock processing is canceled and the rows that have been locked so far are referenced. The rows that were not locked will be locked when they are actually referenced.
- The following shows the sequence of data accesses during SQL execution.
![[Figure]](FIGURE/ZU030427.GIF)
- Explanation:
- The row 1s in tables T1 and T2 indicate that the values of columns specified in the join condition are the same.
- All rows in tables T1 and T2 are stored on the same page.
- Table T1's row 3 is locked in the EX mode by another transaction.
- If the snapshot method is used to perform a join search and there is another UAP that updates the tables or indexes, or that uses the WITH EXCLUSIVE LOCK lock option to perform a search, the lock sequence for rows will vary even for the same SQL statement.
- Because the snapshot method locks all pages to be referenced, the lock sequence for rows varies when index page splitting occurs.
When the snapshot method is not used
- When the snapshot method is not used, one row is locked at a time.
- The following shows the sequence of data accesses during SQL execution:
![[Figure]](FIGURE/ZU030428.GIF)
- Explanation:
- The row 1s in tables T1 and T2 indicate that the values of columns specified in the join condition are the same.
(3) How to handle deadlocks
If the lock sequence for rows varies and a UAP that locks rows in the EX mode is added, deadlock might result even from concurrent execution of multiple identical SQL statements. The following shows an example of such deadlock:
![[Figure]](FIGURE/ZU030440.GIF)
- Explanation:
- UAP1 and UAP2 lock rows in the PR mode.
- UAP1 and UAP2 execute the same SQL statement, but the lock sequence for rows varies as follows for the reason discussed in (2) above:
UAP1: Locks rows 1, 2, and 3 in this order.
UAP2: Locks rows 1, 3, and 2 in this order.
- UAP3 and UAP4 locks rows in the EX mode.
You can take the following actions to handle deadlocks:
- Perform the search with the WITHOUT LOCK NOWAIT or the WITHOUT LOCK WAIT lock option specified.
- Execute the LOCK TABLE statement in IN EXCLUSIVE MODE before performing the search.
- Re-execute the transaction.
For details about how to handle deadlocks, see 3.4.4 Deadlocks and corrective measures.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.