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:

• HiRDB/Single Server is used.
• Indexes have been defined for t1.c1 and t2.c1.
• The index for t1.c1 is used to access t1.
• For each row of t1, the index for t2.c1 is used to perform nest-loop join.
• t1's rows 5, 2, 1, and 6 satisfy the condition of t1.c1 > 3, and the index is used to access the rows in this order.
• The rows in t2 that satisfy the rows in t1 are as follows:
  t2's row 6 satisfies t1's row 1.
  t2's row 2 satisfies t1's row 2.
  t2's rows 4 and 7 satisfy t1's row 5, and the index is used to access the rows in this order.
  t2's rows 3 and 8 satisfy t1's row 6, and the index is used to access the rows in this order.
  
• The snapshot method is not used.
  

  

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.
  

  

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:

• HiRDB/Parallel Server is used.
• Indexes have been defined for t1.c1 and t2.c1.
• The index for t1.c1 is used to access t1.
• For each row of t1, the index for t2.c1 is used to perform nest-loop join.
• t1's rows 5, 2, 1, and 6 satisfy the condition of t1.c1 > 3, and the index is used to access the rows in this order.
• The rows in t2 that satisfy the rows in t1 are as follows:
  t2's row 6 satisfies t1's row 1.
  t2's row 2 satisfies t1's row 2.
  t2's rows 4 and 7 satisfy t1's row 5, and the index is used to access the rows in this order.
  t2's rows 3 and 8 satisfy t1's row 6, and the index is used to access the rows in this order.
  
• The snapshot method is not used.
• The number of rows transferred between back-end servers is 2.
  

  

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:

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.

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:

Explanation:

The row 1s in tables T1 and T2 indicate that the values of columns specified in the join condition are the same.

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:

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.