Nonstop Database, HiRDB Version 9 UAP Development Guide
![[Contents]](FIGURE/CONTENT.GIF)
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
![[Next]](FIGURE/AFTER.GIF)
(1) Mode types
You can apply five lock modes to a resource, as explained as follows:
- Protected retrieve (PR) mode
In the PR mode, only the first transaction that uses the resource occupies it and can reference, add to, update, and delete data in the locked resource. Other transactions can only reference the locked resource.
- Exclusive (EX) mode
In the EX mode, only the transaction that uses the resource occupies it and can reference, add to, update, and delete data in the locked resource. Other transactions cannot reference, add to, update, or delete the locked resource.
- Shared retrieve (SR) mode
In the SR mode, if a lock is applied in PR mode to a certain resource, the lock is applied to the resource that is located above that resource. Other transactions can also reference, add to, update, and delete the locked resource.
- Shared update (SU) mode
In the SU mode, if a lock is applied in EX mode to a certain resource, the lock is applied to the resource that is located above that resource. Other transactions can also reference, add to, update, and delete the locked resource.
- Protected update (PU) mode
In the PU mode, the first transaction that uses the resource can reference, add to, update, and delete data in it; other transactions can only reference the locked resource.
Unlike the first four modes, the PU mode occurs as a result of locking mode transition.
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 |
- A: Simultaneous execution allowed
- NA: Simultaneous execution not allowed
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.
(2) Mode transition
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 |
-- |
- --: Mode transition does not occur.
- (code): Mode after transition.
(3) Mode combinations
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 |
-- |
-- |
-- |
- --: Locking is not applied.
- (code): Lock mode.
- IX: indexes
- #1: If the database update log is not being collected during UAP execution, a table is locked in the EX mode and kept locked until it is committed; rows and keys are not locked. For details about operations when a database update log is not collected during UAP execution, see the HiRDB Version 9 System Operation Guide.
- #2: All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.
- #3: All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.
- #4: Plug-in indexes are locked in the EX mode, but B-Tree indexes are not locked.
- #5: If automatic extension of the RDAREA is applied, the last HiRDB file that makes up the RDAREA is locked from start to end of the automatic extension processing.
- #6: If the inner replica facility is used, the server containing the RDAREA to be processed is locked. However, if Y is specified in the pd_inner_replica_lock_shift operand, the corresponding server is not locked.
- #7: If an RDAREA is added or is altered with the free space reusage facility, the RDAREA is locked.
- #8: If a change related to the inner replica configuration is made, such as changing the current database, defining or deleting a replica, or performing updatable online reorganization, the replica group containing the RDAREA to be processed is locked. A lock is always applied when Y is specified in the pd_inner_replica_lock_shift operand.
- #9: If an RDAREA to be processed is accessed, the RDAREA is locked.
- #10: If the inner replica facility is applied, the resource is locked.
- #11: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.
- #12: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when an UPDATE statement that does not update the index is executed for a shared table.
- #13: When HiRDB/Parallel Server is used, locking equivalent to an EXCLUSIVE-specified LOCK statement is applied to all back-end servers when the operation is executed for a shared table or a shared index. When HiRDB/Single Server is used, locking equivalent to an EXCLUSIVE-specified LOCK statement is applied when the operation is executed for a shared table or a shared index.
- #14: If USE or nothing is specified in the pd_check_pending operand in the system definition, the data dictionary table (resource type: 3005, type name: DICT) is locked temporarily in the EX mode. The data dictionary RDAREA (resource type: 0001, type name: RDAR) is locked in the SU mode until the transaction terminates.
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 |
-- |
-- |
-- |
-- |
- --: Locking is not applied.
- (code): Lock mode.
- #1: If the database update log is not being collected during UAP execution, a table is locked in the EX mode and kept locked until it is committed; rows and keys are not locked. For details about operations when a database update log is not collected during UAP execution, see the HiRDB Version 9 System Operation Guide.
- #2: All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.
- #3: All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.
- #4: The delayed batch creation facility for plug-in indexes is used to lock a plug-in index when it is updated. The lock is held until a commit statement is executed.
- #5: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.
- #6: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when an UPDATE statement that does not update the index is executed for a shared table.
- #7: When HiRDB/Parallel Server is used, locking equivalent to an EXCLUSIVE-specified LOCK statement is applied to all back-end servers when the operation is executed for a shared table or a shared index.
- #8: The row to be deleted is locked in the EX mode until the transaction is committed or rolled back. However, if another transaction executes retrieval processing while the row is being deleted, the retrieval processing does not wait for lock-release because it cannot apply a lock on the row to be deleted.]
- #9: If USE or nothing is specified in the pd_check_pending operand in the system definition, the data dictionary table (resource type: 3005, type name: DICT) is locked temporarily in the EX mode. The data dictionary RDAREA (resource type: 0001, type name: RDAR) is locked in the SU mode until the transaction terminates.
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 |
-- |
-- |
-- |
- --: Locking is not applied.
- (code): Lock mode.
- IX: indexes
- #1: If the database update log is not being collected during UAP execution, a table is locked in the EX mode and kept locked until it is committed; rows and keys are not locked. For details about operations when a database update log is not collected during UAP execution, see the HiRDB Version 9 System Operation Guide.
- #2: All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.
- #3: All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.
- #4: Plug-in indexes are locked in the EX mode, but B-Tree indexes are not locked.
- #5: If automatic extension of the RDAREA is applied, the last HiRDB file that makes up the RDAREA is locked from start to end of the automatic extension processing.
- #6: If the inner replica facility is used, the server containing the RDAREA to be processed is locked. However, if Y is specified in the pd_inner_replica_lock_shift operand, the server is not locked.
- #7: If an RDAREA is added or is altered with the free space reusage facility, the RDAREA is locked.
- #8: If a change related to the inner replica configuration is made, such as changing the current database, defining or deleting a replica, or performing updatable online reorganization, the replica group containing the RDAREA to be processed is locked. A lock is always applied when Y is specified in the pd_inner_replica_lock_shift operand.
- #9: If an RDAREA to be processed is accessed, the RDAREA is locked.
- #10: If the inner replica facility is applied, the resource is locked.
- #11: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.
- #12: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when an UPDATE statement that does not update the index is executed for a shared table.
- #13: When HiRDB/Parallel Server is used, locking equivalent to an EXCLUSIVE-specified LOCK statement is applied to all back-end servers when the operation is executed for a shared table or a shared index. When HiRDB/Single Server is used, locking equivalent to an EXCLUSIVE-specified LOCK statement is applied when the operation is executed for a shared table or a shared index.
- #14: If USE or nothing is specified in the pd_check_pending operand in the system definition, the data dictionary table (resource type: 3005, type name: DICT) is locked temporarily in the EX mode. The data dictionary RDAREA (resource type: 0001, type name: RDAR) is locked in the SU mode until the transaction terminates.
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 |
-- |
-- |
-- |
-- |
- --: Locking is not applied.
- (code): Lock mode.
- #1: If the database update log is not being collected during UAP execution, a table is locked in the EX mode and kept locked until it is committed; rows and keys are not locked. For details about operations when a database update log is not collected during UAP execution, see the HiRDB Version 9 System Operation Guide.
- #2: All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.
- #3: All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.
- #4: The delayed batch creation facility for plug-in indexes is used to lock a plug-in index when it is updated. The lock is held until a commit statement is executed.
- #5: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.
- #6: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when an UPDATE statement that does not update the index is executed for a shared table.
- #7: When HiRDB/Parallel Server is used, locking equivalent to an EXCLUSIVE-specified LOCK statement is applied to all back-end servers when the operation is executed for a shared table or a shared index.
- #8: If USE or nothing is specified in the pd_check_pending operand in the system definition, the data dictionary table (resource type: 3005, type name: DICT) is locked temporarily in the EX mode. The data dictionary RDAREA (resource type: 0001, type name: RDAR) is locked in the SU mode until the transaction terminates.
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 |
-- |
-- |
-- |
- --: Locking is not applied.
- (code): Lock mode.
- IX: indexes
- #1: All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.
- #2: All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.
- #3: Plug-in indexes are locked in the EX mode, but B-Tree indexes are not locked.
- #4: If automatic extension of the RDAREA is applied, the last HiRDB file that makes up the RDAREA is locked from start to end of the automatic extension processing.
- #5: If the inner replica facility is used, the server containing the RDAREA to be processed is locked. However, if Y is specified in the pd_inner_replica_lock_shift operand, the server is not locked.
- #6: If an RDAREA is added or is altered with the free space reusage facility, the RDAREA is locked.
- #7: If a change related to the inner replica configuration is made, such as changing the current database, defining or deleting a replica, or performing updatable online reorganization, the replica group containing the RDAREA to be processed is locked. A lock is always applied when Y is specified in the pd_inner_replica_lock_shift operand.
- #8: If an RDAREA to be processed is accessed, the RDAREA is locked.
- #9: If the inner replica facility is applied, the resource is locked.
- #10: If the database update log is not being collected during UAP execution, a table is locked in the EX mode and kept locked until it is committed; rows and keys are not locked. For details about operations when a database update log is not collected during UAP execution, see the HiRDB Version 9 System Operation Guide.
- #11: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.
- #12: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when an UPDATE statement that does not update the index is executed for a shared table.
- #13: When HiRDB/Parallel Server is used, locking equivalent to an EXCLUSIVE-specified LOCK statement is applied to all back-end servers when the operation is executed for a shared table or a shared index. When HiRDB/Single Server is used, locking equivalent to an EXCLUSIVE-specified LOCK statement is applied when the operation is executed for a shared table or a shared index.
- #14: If USE or nothing is specified in the pd_check_pending operand in the system definition, the data dictionary table (resource type: 3005, type name: DICT) is locked temporarily in the EX mode. The data dictionary RDAREA (resource type: 0001, type name: RDAR) is locked in the SU mode until the transaction terminates.
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 |
-- |
-- |
-- |
-- |
- --: Locking is not applied.
- (code): Lock mode.
- #1: All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.
- #2: All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.
- #3: The delayed batch creation facility for plug-in indexes is used to lock a plug-in index when it is updated. The lock is held until a commit statement is executed.
- #4: In row locking, resource rows are locked and resource pages are not locked. In page locking, resource rows are not locked, and resource pages are locked.
- #5: If a unique index is defined, resource rows are locked, even in page locking.
- #6: If the database update log is not being collected during UAP execution, a table is locked in the EX mode and kept locked until it is committed; rows and keys are not locked. For details about operations when a database update log is not collected during UAP execution, see the HiRDB Version 9 System Operation Guide.
- #7: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.
- #8: When HiRDB/Parallel Server is used, locking is applied to all back-end servers when an UPDATE statement that does not update the index is executed for a shared table.
- #9: When HiRDB/Parallel Server is used, locking equivalent to an EXCLUSIVE-specified LOCK statement is applied to all back-end servers when the operation is executed for a shared table or a shared index.
- #10: If USE or nothing is specified in the pd_check_pending operand in the system definition, the data dictionary table (resource type: 3005, type name: DICT) is locked temporarily in the EX mode. The data dictionary RDAREA (resource type: 0001, type name: RDAR) is locked in the SU mode until the transaction terminates.
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 |
- --: Locking is not applied.
- (code): Lock mode.
- #1
- This table shows resources for tables in which a referential constraint or a check constraint is defined.
- #2
- Locking is applied to the RDAREAs in which the check pending status is to be set.
- #3
- When a HiRDB/Parallel Server is used, locking equivalent to a LOCK statement with EXCLUSIVE specified is applied to all back-end servers when the utility is executed for a shared table. For details about the lock mode applied during execution of the LOCK statement on a shared table, see the EXCLUSIVE specified rows under LOCK statement in Tables 3-10 to 3-15.
- #4
- If USE or nothing is specified in the pd_check_pending operand in the system definition, the data dictionary table (resource type: 3005, type name: DICT) is locked temporarily in the EX mode. The data dictionary RDAREA (resource type: 0001, type name: RDAR) is locked in the SU mode until the transaction terminates.
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 |
-- |
-- |
-- |
-- |
-- |
-- |
- --: Locking is not applied.
- #1
- This table shows resources for tables in which a referential constraint or a check constraint is defined.
- #2
- When a HiRDB/Parallel Server is used, locking equivalent to a LOCK statement with EXCLUSIVE specified is applied to all back-end servers when the utility is executed for a shared table. For details about the lock mode applied during execution of the LOCK statement on a shared table, see the EXCLUSIVE specified rows under LOCK statement in Tables 3-10 to 3-15.
- #3
- If USE or nothing is specified in the pd_check_pending operand in the system definition, the data dictionary table (resource type: 3005, type name: DICT) is locked temporarily in the EX mode. The data dictionary RDAREA (resource type: 0001, type name: RDAR) is locked in the SU mode until the transaction terminates.
(4) Lock release timings in tables for which the WITHOUT ROLLBACK option is specified in CREATE TABLE
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 |
- --: Locking is not applied or is not applicable (page locking cannot be specified).
- B: Lock is not released when the SQL statement is executed.
- #: If the inner replica facility is being used, the inner replica configuration management is locked. If the updatable online reorganization is being used, the inner replica configuration management or the replica group configuration management is locked.
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 |
-- |
-- |
-- |
-- |
-- |
-- |
- --: Locking is not applied or is not applicable (page locking cannot be specified).
- R: Lock is released when the SQL statement is executed.
- B: Lock is not released when the SQL statement is executed.
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 |
- Legend:
- --: Locking is not applied or is not applicable (page locking cannot be specified).
- B: Lock is not released when the SQL statement is executed.
- #: If the inner replica facility is being used, the inner replica configuration management is locked. If the updatable online reorganization is being used, the inner replica configuration management or the replica group configuration management is locked.
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 |
-- |
-- |
-- |
-- |
-- |
-- |
- Legend:
- --: Locking is not applied or is not applicable (index definition and page locking cannot be specified).
- R: Lock is released when the SQL statement is executed.
- B: Lock is not released when the SQL statement is executed.
- #1: If the pd_indexlock_mode operand of the system definition is KEY (index locking is applied), the lock is released when the key value of the processed target is changed to another key value.
- #2: The lock is released if the resource is a unique key index.
- #3: The lock is not released if the pd_indexlock_mode operand of the system definition is KEY (index locking is applied).
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.