Nonstop Database, HiRDB Version 9 UAP Development Guide
You can apply five lock modes to a resource, as explained as follows:
Locking is applied first to the highest-level resource and then to lower-level resources. If a transaction cannot be executed simultaneously with other transactions that have locking in effect for the same resource, that transaction goes onto wait status. When the PR or EX mode is encountered while locking is being applied from a higher-level resource to a lower-level resource, a resource that is located below the resource to which the mode has been applied is not locked.
When two users attempt to perform identical processing on the same resource, the difference in the combination of the lock modes may prevent simultaneous execution. The following table shows when two users can perform execution simultaneously based on lock modes.
Table 3-8 Simultaneous execution by two users based on lock modes
Mode | SR | PR | SU | PU | EX |
---|---|---|---|---|---|
SR | A | A | A | A | NA |
PR | A | A | NA | NA | NA |
SU | A | NA | A | NA | NA |
PU | A | NA | NA | NA | NA |
EX | NA | NA | NA | NA | NA |
When two users cannot perform execution simultaneously, the system usually waits for the other transaction to be committed (updated at the synchronization point). WITH ROLLBACK, or NOWAIT can be specified in the SQL statement to cause an error return without waiting for the other transaction to be committed.
If the user applies different locking modes repeatedly to the same resource, the mode shifts to the stronger one.
After locking has been applied using a strong mode, applying a weaker mode does not cause the mode to shift to the weaker one. For example, if EX is used for locking during row updating, the lock mode of the row remains as EX, even if PR is applied subsequently for referencing the updated row.
The following table shows the lock mode transition rules when a lock is applied to the current lock.
Table 3-9 Lock mode transition rules
Mode applied subsequently | Current mode | ||||
---|---|---|---|---|---|
SR | PR | SU | PU | EX | |
SR | -- | -- | -- | -- | -- |
PR | PR | -- | PU | -- | -- |
SU | SU | PU | -- | -- | -- |
EX | EX | EX | EX | EX | -- |
Different lock modes can be combined, depending on the SQL statement and the execution environment.
Tables 3-10 and 3-11 show typical lock mode combinations based on the SQL statement and execution environment for row-level locking. Tables 3-12 and 3-13 show similar combinations for page-level locking, and Tables 3-14 and 3-15 show them for non-locking of index key values. Tables 3-16 and 3-17 show typical lock mode combinations for cases when a table is set to check pending status.
Table 3-10 Typical lock mode combinations (row locking) (1/2)
SQL statement and execution environment | Resource | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | ||||||||||
Inner replica config#6 | Replica group config#8 | RDAREA | Table | Table (NO WAIT search) | ||||||
For tables | For IX | For sequence generator | Last HiRDB file#5 | |||||||
Retrieval | NOWAIT specified | SR | SR | SR | -- | -- | -- | SR | ||
WITH SHARE specified | SR | SR | SR | -- | -- | SR | -- | |||
WITH EXCLUSIVE specified#1 | SR | SR | SU | SR | -- | -- | SU | -- | ||
FOR UPDATE clause specified#1 | SR | SR | SU | SR | -- | -- | SU | -- | ||
None of the above | SR | SR | SR | -- | -- | SR | -- | |||
Updating#1, #12 | NEXT VALUE clause specified | SR | SU | SU | EX | SU | -- | |||
None of the above | SR | SU | -- | EX | SU | -- | ||||
Addition#1 | NEXT VALUE clause specified | SR | SU | SU | EX | SU | -- | |||
None of the above | SR | SU | -- | EX | SU | -- | ||||
Deletion#1 | SR | SR | SU | -- | -- | SU | -- | |||
LOCK statement | SHARE specified#11 | SR | SR | SR | -- | -- | -- | PR | -- | |
EXCLUSIVE specified | Unshared table | SR | SR | SU | -- | -- | -- | EX | -- | |
Shared table#11 | SR | SR | EX | -- | -- | -- | EX | -- | ||
Table deletion#2, #13 | -- | -- | SU | -- | -- | EX | ||||
Index | Definition#13 | -- | -- | SU | -- | -- | EX | -- | ||
Deletion#3, #13 | -- | -- | SR#10 | SU | -- | -- | EX#4 | EX | ||
Deletion of all rows#2, #13, #14 | SR | SR | SU | -- | -- | EX | ||||
Table definition change#13 | SR#9 | SR#9 | SU#7 | -- | -- | EX | ||||
Definition of sequence generator | -- | -- | -- | -- | SU | -- | -- | -- | ||
Deletion of sequence generator | -- | -- | -- | -- | SU | -- | -- | -- |
Table 3-11 Typical lock mode combinations (row locking) (2/2)
SQL statement and execution environment | Resource | ||||||||
---|---|---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | |||||||||
Index | Index information file#4 | Sequence generator | Page | Row | Key value | Logical file | |||
Retrieval | NOWAIT specified | -- | -- | -- | -- | -- | -- | PR | |
WITH SHARE specified | -- | -- | -- | -- | PR | PR | PR | ||
WITH EXCLUSIVE specified#1 | -- | -- | -- | -- | EX | PR | EX | ||
FOR UPDATE clause specified#1 | -- | -- | -- | -- | EX | PR | EX | ||
None of the above | -- | -- | -- | -- | PR | PR | PR | ||
Updating#1, #6 | NEXT VALUE clause specified | -- | EX | SU | -- | EX | EX | EX | |
None of the above | -- | EX | -- | -- | EX | EX | EX | ||
Addition#1 | NEXT VALUE clause specified | -- | EX | SU | -- | EX | EX | EX | |
None of the above | -- | EX | -- | -- | EX | EX | EX | ||
Deletion#1 | -- | -- | -- | -- | EX#8 | EX | EX | ||
LOCK statement | SHARE specified#5 | -- | -- | -- | -- | -- | -- | -- | |
EXCLUSIVE specified | Unshared table | -- | -- | -- | -- | -- | -- | -- | |
Shared table#5 | -- | -- | -- | -- | -- | -- | -- | ||
Table deletion#2, #7 | -- | -- | -- | -- | -- | -- | -- | ||
Index | Definition#7 | -- | -- | -- | -- | -- | -- | -- | |
Deletion#3, #7 | EX | -- | -- | -- | -- | -- | -- | ||
Deletion of all rows#2, #7, #9 | -- | -- | -- | -- | -- | -- | -- | ||
Table definition change#7 | -- | -- | -- | -- | -- | -- | -- | ||
Definition of sequence generator | -- | -- | EX | -- | -- | -- | -- | ||
Deletion of sequence generator | -- | -- | EX | -- | -- | -- | -- |
Table 3-12 Typical lock mode combinations (page locking) (1/2)
SQL statement and execution environment | Resource | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | ||||||||||
Inner replica config#6 | Replica group config#8 | RDAREA | Table | Table (NO WAIT search) | ||||||
For tables | For IX | For sequence generator | Last HiRDB file#5 | |||||||
Retrieval | NOWAIT specified | SR | SR | SR | -- | -- | -- | SR | ||
WITH SHARE specified | SR | SR | SR | -- | -- | SR | -- | |||
WITH EXCLUSIVE specified#1 | SR | SR | SU | SR | -- | -- | SU | -- | ||
FOR UPDATE clause specified#1 | SR | SR | SU | SR | -- | -- | SU | -- | ||
None of the above | SR | SR | SR | -- | -- | SR | -- | |||
Updating#1, #12 | NEXT VALUE clause specified | SR | SR | SU | SU | EX | SU | -- | ||
None of the above | SR | SR | SU | -- | EX | SU | -- | |||
Addition#1 | NEXT VALUE clause specified | SR | SR | SU | SU | EX | SU | -- | ||
None of the above | SR | SR | SU | -- | EX | SU | -- | |||
Deletion#1 | SR | SR | SU | -- | -- | SU | -- | |||
LOCK statement | SHARE specified#11 | SR | SR | SR | -- | -- | -- | PR | -- | |
EXCLUSIVE specified | Unshared table | SR | SR | SU | -- | -- | -- | EX | -- | |
Shared table#11 | SR | SR | EX | -- | -- | -- | EX | -- | ||
Table deletion#2, #13 | -- | -- | SU | -- | -- | EX | EX | |||
Index | Definition#13 | -- | -- | SU | -- | -- | EX | -- | ||
Deletion#3, #13 | -- | -- | SR#10 | SU | -- | -- | EX#4 | EX | ||
Deletion of all rows#2, #13, #14 | SR | SR | SU | -- | -- | EX | EX | |||
Table definition change#13 | SR#9 | SR#9 | SU#7 | -- | -- | EX | EX | |||
Definition of sequence generator | -- | -- | -- | SU | -- | -- | -- | |||
Deletion of sequence generator | -- | -- | -- | SU | -- | -- | -- |
Table 3-13 Typical lock mode combinations (page locking) (2/2)
SQL statement and execution environment | Resource | ||||||||
---|---|---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | |||||||||
Index | Index information file#4 | Sequence generator | Page | Row | Key value | Logical file | |||
Retrieval | NOWAIT specified | -- | -- | -- | -- | -- | -- | PR | |
WITH SHARE specified | -- | -- | -- | PR | -- | PR | PR | ||
WITH EXCLUSIVE specified#1 | -- | -- | -- | EX | -- | PR | EX | ||
FOR UPDATE clause specified#1 | -- | -- | -- | EX | -- | PR | EX | ||
None of the above | -- | -- | -- | PR | -- | PR | PR | ||
Updating#1, #6 | NEXT VALUE clause specified | -- | EX | SU | EX | -- | EX | EX | |
None of the above | -- | EX | -- | EX | -- | EX | EX | ||
Addition#1 | NEXT VALUE clause specified | -- | EX | SU | EX | -- | EX | EX | |
None of the above | -- | EX | -- | EX | -- | EX | EX | ||
Deletion#1 | -- | -- | -- | EX | -- | EX | EX | ||
LOCK statement | SHARE specified#5 | -- | -- | -- | -- | -- | -- | -- | |
EXCLUSIVE specified | Unshared table | -- | -- | -- | -- | -- | -- | -- | |
Shared table#5 | -- | -- | -- | -- | -- | -- | -- | ||
Table deletion#2, #7 | -- | -- | -- | -- | -- | -- | -- | ||
Index | Definition#7 | -- | -- | -- | -- | -- | -- | -- | |
Deletion#3, #7 | EX | -- | -- | -- | -- | -- | -- | ||
Deletion of all rows#2, #7, #8 | -- | -- | -- | -- | -- | -- | -- | ||
Table definition change#7 | -- | -- | -- | -- | -- | -- | -- | ||
Definition of sequence generator | -- | -- | EX | -- | -- | -- | -- | ||
Deletion of sequence generator | -- | -- | EX | -- | -- | -- | -- |
Table 3-14 Typical lock mode combinations (non-locking of index key values) (1/2)
SQL statement and execution environment | Resource | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | ||||||||||
Inner replica config#5 | Replica group config#7 | RDAREA | Table | Table (NO WAIT search) | ||||||
For tables | For IX | For sequence generator | Last HiRDB file#4 | |||||||
Retrieval |
NOWAIT specified | SR | SR | SR | -- | -- | -- | SR | ||
WITH SHARE specified | SR | SR | SR | -- | -- | SR | -- | |||
WITH EXCLUSIVE specified#10 | SR | SR | SU | SR | -- | -- | SU | SU | ||
FOR UPDATE clause specified#10 | SR | SR | SU | SR | -- | -- | SU | SU | ||
None of the above | SR | SR | SR | -- | -- | SR | -- | |||
Updating#10, #12 | NEXT VALUE clause specified | SR | SR | SU | SU | EX | SU | -- | ||
None of the above | SR | SR | SU | -- | EX | SU | -- | |||
Addition#10 | NEXT VALUE clause specified | SR | SR | SU | SU | EX | SU | -- | ||
None of the above | SR | SR | SU | -- | EX | SU | -- | |||
Deletion#10 | SR | SR | SU | -- | -- | SU | -- | |||
LOCK statement | SHARE specified#11 | SR | SR | SR | -- | -- | -- | PR | -- | |
EXCLUSIVE specified | Unshared table | SR | SR | SU | -- | -- | -- | EX | -- | |
Shared table#11 | SR | SR | EX | EX | -- | -- | EX | -- | ||
Table deletion#1, #13 | -- | -- | SU | -- | -- | EX | EX | |||
Index | Definition#13 | -- | -- | SU | -- | -- | EX | -- | ||
Deletion#2, #13 | -- | -- | SR#9 | SU | -- | -- | EX#3 | EX | ||
Deletion of all rows#1, #13, #14 | SR | SR | SU | -- | -- | EX | EX | |||
Table definition change#13 | SR#8 | SR#8 | SU#6 | -- | -- | EX | EX | |||
Definition of sequence generator | -- | -- | -- | SU | -- | -- | -- | |||
Deletion of sequence generator | -- | -- | -- | SU | -- | -- | -- |
Table 3-15 Typical lock mode combinations (non-locking of index key values) (2/2)
SQL statement and execution environment | Resource | ||||||||
---|---|---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | |||||||||
Index | Index information file#3 | Sequence generator | Page | Row | Key value | Logical file | |||
Retrieval | NOWAIT specified | -- | -- | -- | -- | -- | -- | PR | |
WITH SHARE specified | -- | -- | -- | --, PR#4 |
PR, --# 4 |
-- | PR | ||
WITH EXCLUSIVE specified#6 | -- | -- | -- | --, EX#4 |
EX, --# 4 |
-- | EX | ||
FOR UPDATE clause specified#6 | -- | -- | -- | --, EX#4 |
EX, --# 4 |
-- | EX | ||
None of the above | -- | -- | -- | -- PR#4 |
PR, --# 4 |
-- | PR | ||
Updating#6, 8 | NEXT VALUE clause specified | -- | EX | SU | --, EX#4, #5 |
EX, --#4, #5 |
-- | EX | |
None of the above | -- | EX | -- | --, EX#4, #5 |
EX, --#4, #5 |
-- | EX | ||
Addition#6 | NEXT VALUE clause specified | -- | EX | SU | --, EX#4, #5 |
EX, --# 4, #5 |
-- | EX | |
None of the above | -- | EX | -- | --, EX#4, #5 |
EX, --#4, #5 |
-- | EX | ||
Deletion#6 | -- | -- | -- | -- EX#4, #5 |
EX, --#4, #5 |
-- | EX | ||
LOCK statement | SHARE specified#7 | -- | -- | -- | -- | -- | -- | -- | |
EXCLUSIVE specified | Unshared table | -- | -- | -- | -- | -- | -- | -- | |
Shared table#7 | -- | -- | -- | -- | -- | -- | -- | ||
Table deletion#1, #9 | -- | -- | -- | -- | -- | -- | -- | ||
Index | Definition#9 | -- | -- | -- | -- | -- | -- | -- | |
Deletion#2, #9 | EX | -- | -- | -- | -- | -- | -- | ||
Deletion of all rows#1, #9, #10 | -- | -- | -- | -- | -- | -- | -- | ||
Table definition change#9 | -- | -- | -- | -- | -- | -- | -- | ||
Definition of sequence generator | -- | -- | EX | -- | -- | -- | -- | ||
Deletion of sequence generator | -- | -- | EX | -- | -- | -- | -- |
Table 3-16 Typical lock mode combinations (when check pending status is set) (1/2)
SQL statement and utility | Resource#1 | |||||
---|---|---|---|---|---|---|
Higher level --------------------- Lower level | ||||||
RDAREA | Table | Table (NO WAIT search) | ||||
For tables#2 | For indexes | For LOB | Last HiRDB file | |||
Deletion of all rows#4 | SU | -- | -- | -- | EX | EX |
Changing a table's definition (changing the storage partitioning conditions) | SU | -- | -- | -- | EX | EX |
Database load utility (pdload)#3 | SU | -- | -- | -- | EX | EX |
Database reorganization utility (pdrorg)#3 | SU | -- | -- | -- | EX | EX |
Database structure modification utility (pdmod) | SU | -- | -- | -- | EX | EX |
Integrity check utility (pdconstck)#3 | SU | -- | -- | -- | EX | EX |
Reflection command for online reorganization (pdorend)#3 | SU | -- | -- | -- | EX | EX |
Table 3-17 Typical lock mode combinations (when check pending status is set) (2/2)
SQL statement and utility | Resource#1 | |||||
---|---|---|---|---|---|---|
Higher level --------------------- Lower level | ||||||
Index | Index information file | Page | Row | Key value | Logical file | |
Deletion of all rows#3 | -- | -- | -- | -- | -- | -- |
Changing a table's definition (changing the storage partitioning conditions) | -- | -- | -- | -- | -- | -- |
Database load utility (pdload)#2 | -- | -- | -- | -- | -- | -- |
Database reorganization utility (pdrorg)#2 | -- | -- | -- | -- | -- | -- |
Database structure modification utility (pdmod) | -- | -- | -- | -- | -- | -- |
Integrity check utility (pdconstck)#2 | -- | -- | -- | -- | -- | -- |
Reflection command for online reorganization (pdorend)#2 | -- | -- | -- | -- | -- | -- |
Tables 3-18 to 3-21 show the lock release timings in tables for which the WITHOUT ROLLBACK option is specified in CREATE TABLE.
Table 3-18 Lock release timings in tables for which the WITHOUT ROLLBACK option is specified in CREATE TABLE (when an index is not defined) (1/2)
SQL statement and execution environment | Resource | |||||||
---|---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | ||||||||
Inner replica config# | Replica group config# | RDAREA | Table | Table (NO WAIT search) | ||||
For tables | For indexes | Last HiRDB file | ||||||
Retrieval | NOWAIT specified | B | B | B | -- | -- | -- | B |
WITH SHARE specified | B | B | B | -- | -- | B | -- | |
WITH EXCLUSIVE specified# | B | B | B | -- | -- | B | -- | |
FOR UPDATE clause specified# | B | B | B | -- | -- | B | -- | |
None of the above | B | B | B | -- | -- | B | -- | |
Updating | B | B | B | -- | -- | B | -- | |
Addition | B | B | B | -- | -- | B | -- | |
Deletion | B | B | B | -- | -- | B | -- | |
LOCK statement | SHARE specified | B | B | B | -- | -- | B | -- |
EXCLUSIVE specified | B | B | B | -- | -- | B | -- | |
Table deletion | -- | -- | B | -- | -- | B | B | |
Index | Definition | -- | -- | B | -- | -- | B | B |
Deletion | -- | -- | B | -- | -- | B | B | |
Deletion of all rows | B | B | B | -- | -- | B | B | |
Table definition change | -- | -- | -- | -- | -- | B | B |
Table 3-19 Lock release timings in tables for which the WITHOUT ROLLBACK option is specified in CREATE TABLE (when an index is not defined) (2/2)
SQL statement and execution environment | Resource | ||||||
---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | |||||||
Index | Index information file | Page | Row | Key value | Logical file | ||
Retrieval | NOWAIT specified | -- | -- | -- | -- | -- | -- |
WITH SHARE specified | -- | -- | -- | B | -- | -- | |
WITH EXCLUSIVE specified | -- | -- | -- | B | -- | -- | |
FOR UPDATE clause specified | -- | -- | -- | B | -- | -- | |
None of the above | -- | -- | -- | B | -- | -- | |
Updating | -- | -- | -- | R | -- | -- | |
Addition | -- | -- | -- | R | -- | -- | |
Deletion | -- | -- | -- | R | -- | -- | |
LOCK statement | SHARE specified | -- | -- | -- | -- | -- | -- |
EXCLUSIVE specified | -- | -- | -- | -- | -- | -- | |
Table deletion | -- | -- | -- | -- | -- | -- | |
Index | Definition | -- | -- | -- | -- | -- | -- |
Deletion | -- | -- | -- | -- | -- | -- | |
Deletion of all rows | -- | -- | -- | -- | -- | -- | |
Table definition change | -- | -- | -- | -- | -- | -- |
Table 3-20 Lock release timings in tables for which the WITHOUT ROLLBACK option is specified in CREATE TABLE (when an index is defined) (1/2)
SQL statement | Resource | |||||||
---|---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | ||||||||
Inner replica config# | Replica group config# | RDAREA | Table | Table (NO WAIT search) | ||||
For tables | For indexes | Last HiRDB file | ||||||
Retrieval | NOWAIT specified | B | B | B | B | -- | -- | B |
WITH SHARE specified | B | B | B | B | -- | B | -- | |
WITH EXCLUSIVE specified | B | B | B | B | -- | B | -- | |
FOR UPDATE clause specified | B | B | B | B | -- | B | -- | |
None of the above | B | B | B | B | -- | B | -- | |
Updating | B | B | B | B | -- | B | -- | |
Addition | B | B | B | B | -- | B | -- | |
Deletion | B | B | B | B | -- | B | -- | |
LOCK statement | SHARE specified | B | B | B | B | -- | B | -- |
EXCLUSIVE specified | B | B | B | B | -- | B | -- | |
Table deletion | -- | -- | B | B | -- | B | B | |
Index | Definition | -- | -- | B | B | -- | B | B |
Deletion | -- | -- | B | B | -- | B | B | |
Deletion of all rows | B | B | B | B | -- | B | B | |
Table definition change | -- | -- | -- | B | -- | B | B |
Table 3-21 Lock release timings in tables for which the WITHOUT ROLLBACK option is specified in CREATE TABLE (when an index is defined) (2/2)
SQL statement | Resource | ||||||
---|---|---|---|---|---|---|---|
Higher level --------------------- Lower level | |||||||
Index | Index information file | Page | Row | Key value | Logical file | ||
Retrieval | NOWAIT specified | -- | -- | -- | -- | -- | -- |
WITH SHARE specified | -- | -- | -- | B | --#1 | -- | |
WITH EXCLUSIVE specified | -- | -- | -- | B | --#1 | -- | |
FOR UPDATE clause specified | -- | -- | -- | B | --#1 | -- | |
None of the above | -- | -- | -- | B | --#1 | -- | |
Updating | -- | -- | -- | R | --#2 | -- | |
Addition | -- | -- | -- | R | --#3 | -- | |
Deletion | -- | -- | -- | R | --#3 | -- | |
LOCK statement | SHARE specified | -- | -- | -- | -- | -- | -- |
EXCLUSIVE specified | -- | -- | -- | -- | -- | -- | |
Table deletion | -- | -- | -- | -- | -- | -- | |
Index | Definition | -- | -- | -- | -- | -- | -- |
Deletion | -- | -- | -- | -- | -- | -- | |
Deletion of all rows | -- | -- | -- | -- | -- | -- | |
Table definition change | -- | -- | -- | -- | -- | -- |
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.