3.4.2 Lock modes

Organization of this subsection
(1) Mode types
(2) Mode transition
(3) Mode combinations
(4) Lock release timings in tables for which the WITHOUT ROLLBACK option is specified in CREATE TABLE

(1) Mode types

You can apply five lock modes to a resource, as explained as follows:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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. Table 3-8 shows when two users can perform execution simultaneously based on lock modes.

Table 3-8 Simultaneous execution by two users based on lock modes

ModeSRPRSUPUEX
SRAAAANA
PRAANANANA
SUANAANANA
PUANANANANA
EXNANANANANA
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.

Table 3-9 shows the lock mode transition rules.

Table 3-9 Lock mode transition rules

Mode applied subsequentlyCurrent mode
SRPRSUPUEX
SR[Figure][Figure][Figure][Figure][Figure]
PRPR[Figure]PU[Figure][Figure]
SUSUPU[Figure][Figure][Figure]
EXEXEXEXEX[Figure]
[Figure]: 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 environmentResource
Higher level --------------------- Lower level
Inner replica config6Replica group config8RDAREATableNO WAIT table
For tablesFor IXLast HiRDB file5
RetrievalNOWAIT specifiedSRSRSR[Figure][Figure]SR
WITH SHARE specifiedSRSRSR[Figure]SR[Figure]
WITH EXCLUSIVE specified1SRSRSUSR[Figure]SU[Figure]
FOR UPDATE clause specified1SRSRSUSR[Figure]SU[Figure]
None of the aboveSRSRSR[Figure]SR[Figure]
Updating1, 12SRSRSUEXSU[Figure]
Addition1SRSRSUEXSU[Figure]
Deletion1SRSRSU[Figure]SU[Figure]
LOCK statementSHARE specified11SRSRSR[Figure][Figure]PR[Figure]
EXCLUSIVE specifiedUnshared tableSRSRSU[Figure][Figure]EX[Figure]
Shared table11SRSREXEX[Figure]EX[Figure]
Table deletion2, 13[Figure][Figure]SU[Figure]EXEX
IndexDefinition13[Figure][Figure]SU[Figure]EX[Figure]
Deletion3, 13[Figure][Figure]SR10SU[Figure]EX4EX
Deletion of all rows2, 13SRSRSU[Figure]EXEX
Table definition change13SR9SR9SU7[Figure]EXEX
[Figure]: 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 8 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.
7 If an RDAREA is added or is altered with the free space reusage facility, the RDAREA is locked.
8 When updatable online reorganization is executed, the replica group containing the RDAREA to be processed is locked.
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.

Table 3-11 Typical lock mode combinations (row locking) (2/2)

SQL statement and execution environmentResource
Higher level --------------------- Lower level
IndexIndex information file4PageRowKey valueLogical file
RetrievalNOWAIT specified[Figure][Figure][Figure][Figure][Figure]PR
WITH SHARE specified[Figure][Figure][Figure]PRPRPR
WITH EXCLUSIVE specified1[Figure][Figure][Figure]EXPREX
FOR UPDATE clause specified1[Figure][Figure][Figure]EXPREX
None of the above[Figure][Figure][Figure]PRPRPR
Updating1, 6[Figure]EX[Figure]EXEXEX
Addition1[Figure]EX[Figure]EXEXEX
Deletion1[Figure][Figure][Figure]EX8EXEX
LOCK statementSHARE specified5[Figure][Figure][Figure][Figure][Figure][Figure]
EXCLUSIVE specifiedUnshared table[Figure][Figure][Figure][Figure][Figure][Figure]
Shared table5[Figure][Figure][Figure][Figure][Figure][Figure]
Table deletion2, 7[Figure][Figure][Figure][Figure][Figure][Figure]
IndexDefinition7[Figure][Figure][Figure][Figure][Figure][Figure]
Deletion3, 7EX[Figure][Figure][Figure][Figure][Figure]
Deletion of all rows2, 7[Figure][Figure][Figure][Figure][Figure][Figure]
Table definition change7[Figure][Figure][Figure][Figure][Figure][Figure]
[Figure]: 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 8 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.

Table 3-12 Typical lock mode combinations (page locking) (1/2)

SQL statement and execution environmentResource
Higher level --------------------- Lower level
Inner replica config6Replica group config8RDAREATableNO WAIT table
For tablesFor IXLast HiRDB file5
RetrievalNOWAIT specifiedSRSRSR[Figure][Figure]SR
WITH SHARE specifiedSRSRSR[Figure]SR[Figure]
WITH EXCLUSIVE specified1SRSRSUSR[Figure]SU[Figure]
FOR UPDATE clause specified1SRSRSUSR[Figure]SU[Figure]
None of the aboveSRSRSR[Figure]SR[Figure]
Updating1, 12SRSRSUEXSU[Figure]
Addition1SRSRSUEXSU[Figure]
Deletion1SRSRSU[Figure]SU[Figure]
LOCK statementSHARE specified11SRSRSR[Figure][Figure]PR[Figure]
EXCLUSIVE specifiedUnshared tableSRSRSU[Figure][Figure]EX[Figure]
Shared table11SRSREXEX[Figure]EX[Figure]
Table deletion2, 13[Figure][Figure]SU[Figure]EXEX
IndexDefinition13[Figure][Figure]SU[Figure]EX[Figure]
Deletion3, 13[Figure][Figure]SR10SU[Figure]EX4EX
Deletion of all rows2, 13SRSRSU[Figure]EXEX
Table definition change13SR9SR9SU7[Figure]EXEX
[Figure]: 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 8 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.
7 If an RDAREA is added or is altered with the free space reusage facility, the RDAREA is locked.
8 When updatable online reorganization is executed, the replica group containing the RDAREA to be processed is locked.
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.

Table 3-13 Typical lock mode combinations (page locking) (2/2)

SQL statement and execution environmentResource
Higher level --------------------- Lower level
IndexIndex information file4PageRowKey valueLogical file
RetrievalNOWAIT specified[Figure][Figure][Figure][Figure][Figure]PR
WITH SHARE specified[Figure][Figure]PR[Figure]PRPR
WITH EXCLUSIVE specified1[Figure][Figure]EX[Figure]PREX
FOR UPDATE clause specified1[Figure][Figure]EX[Figure]PREX
None of the above[Figure][Figure]PR[Figure]PRPR
Updating1, 6[Figure]EXEX[Figure]EXEX
Addition1[Figure]EXEX[Figure]EXEX
Deletion1[Figure][Figure]EX[Figure]EXEX
LOCK statementSHARE specified5[Figure][Figure][Figure][Figure][Figure][Figure]
EXCLUSIVE specifiedUnshared table[Figure][Figure][Figure][Figure][Figure][Figure]
Shared table5[Figure][Figure][Figure][Figure][Figure][Figure]
Table deletion2, 7[Figure][Figure][Figure][Figure][Figure][Figure]
IndexDefinition7[Figure][Figure][Figure][Figure][Figure][Figure]
Deletion3, 7EX[Figure][Figure][Figure][Figure][Figure]
Deletion of all rows2, 7[Figure][Figure][Figure][Figure][Figure][Figure]
Table definition change7[Figure][Figure][Figure][Figure][Figure][Figure]
[Figure]: 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 8 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.

Table 3-14 Typical lock mode combinations (non-locking of index key values) (1/2)

SQL statement and execution environmentResource
Higher level --------------------- Lower level
Inner replica config5Replica group config7RDAREATableNO WAIT table
For tablesFor IXLast HiRDB file4
RetrievalNOWAIT specifiedSRSRSR[Figure][Figure]SR
WITH SHARE specifiedSRSRSR[Figure]SR[Figure]
WITH EXCLUSIVE specified10SRSRSUSR[Figure]SUSU
FOR UPDATE clause specified10SRSRSUSR[Figure]SUSU
None of the aboveSRSRSR[Figure]SR[Figure]
Updating10, 12SRSRSUEXSU[Figure]
Addition10SRSRSUEXSU[Figure]
Deletion10SRSRSU[Figure]SU[Figure]
LOCK statementSHARE specified11SRSRSR[Figure][Figure]PR[Figure]
EXCLUSIVE specifiedUnshared tableSRSRSU[Figure][Figure]EX[Figure]
Shared table11SRSREXEX[Figure]EX[Figure]
Table deletion1, 13[Figure][Figure]SU[Figure]EXEX
IndexDefinition13[Figure][Figure]SU[Figure]EX[Figure]
Deletion2, 13[Figure][Figure]SR9SU[Figure]EX3EX
Deletion of all rows1, 13SRSRSU[Figure]EXEX
Table definition change13SR8SR8SU6[Figure]EXEX
[Figure]: 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.
6 If an RDAREA is added or is altered with the free space reusage facility, the RDAREA is locked.
7 When updatable online reorganization is executed, the replica group containing the RDAREA to be processed is locked.
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 8 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.

Table 3-15 Typical lock mode combinations (non-locking of index key values) (2/2)

SQL statement and execution environmentResource
Higher level --------------------- Lower level
IndexIndex information file3PageRowKey valueLogical file
RetrievalNOWAIT specified[Figure][Figure][Figure][Figure][Figure]PR
WITH SHARE specified[Figure][Figure][Figure],
PR4
PR,
[Figure]4
[Figure]PR
WITH EXCLUSIVE specified6[Figure][Figure][Figure],
EX4
EX,
[Figure]4
[Figure]EX
FOR UPDATE clause specified6[Figure][Figure][Figure],
EX4
EX,
[Figure]4
[Figure]EX
None of the above[Figure][Figure][Figure]
PR4
PR,
[Figure]4
[Figure]PR
Updating6, 8[Figure]EX[Figure],
EX4, 5
EX,
[Figure]4, 5
[Figure]EX
Addition6[Figure]EX[Figure],
EX4, 5
EX,
[Figure]4, 5
[Figure]EX
Deletion6[Figure][Figure][Figure]
EX4, 5
EX,
[Figure]4, 5
[Figure]EX
LOCK statementSHARE specified7[Figure][Figure][Figure][Figure][Figure][Figure]
EXCLUSIVE specifiedUnshared table[Figure][Figure][Figure][Figure][Figure][Figure]
Shared table7[Figure][Figure][Figure][Figure][Figure][Figure]
Table deletion1, 9[Figure][Figure][Figure][Figure][Figure][Figure]
IndexDefinition9[Figure][Figure][Figure][Figure][Figure][Figure]
Deletion2, 9EX[Figure][Figure][Figure][Figure][Figure]
Deletion of all rows1, 9[Figure][Figure][Figure][Figure][Figure][Figure]
Table definition change9[Figure][Figure][Figure][Figure][Figure][Figure]
[Figure]: 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 8 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.

Table 3-16 Typical lock mode combinations (when check pending status is set) (1/2)

SQL statement and utilityResource#1
Higher level --------------------- Lower level
RDAREATableNO WAIT table
For tables#2For indexesFor LOBLast HiRDB file
Deletion of all rowsSU[Figure][Figure][Figure]EXEX
Changing a table's definition (changing the storage partitioning conditions)SU[Figure][Figure][Figure]EXEX
Database load utility (pdload)#3SU[Figure][Figure][Figure]EXEX
Database reorganization utility (pdrorg)#3SU[Figure][Figure][Figure]EXEX
Database structure modification utility (pdmod)SU[Figure][Figure][Figure]EXEX
Integrity check utility (pdconstck)#3SU[Figure][Figure][Figure]EXEX
Reflection command for online reorganization (pdorend)#3SU[Figure][Figure][Figure]EXEX
[Figure]: 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.

Table 3-17 Typical lock mode combinations (when check pending status is set) (2/2)

SQL statement and utilityResource#1
Higher level --------------------- Lower level
IndexIndex information filePageRowKey valueLogical file
Deletion of all rows[Figure][Figure][Figure][Figure][Figure][Figure]
Changing a table's definition (changing the storage partitioning conditions)[Figure][Figure][Figure][Figure][Figure][Figure]
Database load utility (pdload)#2[Figure][Figure][Figure][Figure][Figure][Figure]
Database reorganization utility (pdrorg)#2[Figure][Figure][Figure][Figure][Figure][Figure]
Database structure modification utility (pdmod)[Figure][Figure][Figure][Figure][Figure][Figure]
Integrity check utility (pdconstck)#2[Figure][Figure][Figure][Figure][Figure][Figure]
Reflection command for online reorganization (pdorend)#2[Figure][Figure][Figure][Figure][Figure][Figure]
[Figure]: 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.

(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 environmentResource
Higher level --------------------- Lower level
Inner replica config*Replica group config*RDAREATableNO WAIT table
For tablesFor indexesLast HiRDB file
RetrievalNOWAIT specifiedBBB[Figure][Figure][Figure]B
WITH SHARE specifiedBBB[Figure][Figure]B[Figure]
WITH EXCLUSIVE specified*BBB[Figure][Figure]B[Figure]
FOR UPDATE clause specified*BBB[Figure][Figure]B[Figure]
None of the aboveBBB[Figure][Figure]B[Figure]
UpdatingBBB[Figure][Figure]B[Figure]
AdditionBBB[Figure][Figure]B[Figure]
DeletionBBB[Figure][Figure]B[Figure]
LOCK statementSHARE specifiedBBB[Figure][Figure]B[Figure]
EXCLUSIVE specifiedBBB[Figure][Figure]B[Figure]
Table deletion[Figure][Figure]B[Figure][Figure]BB
IndexDefinition[Figure][Figure]B[Figure][Figure]BB
Deletion[Figure][Figure]B[Figure][Figure]BB
Deletion of all rowsBBB[Figure][Figure]BB
Table definition change[Figure][Figure][Figure][Figure][Figure]BB
[Figure]: 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 environmentResource
Higher level --------------------- Lower level
IndexIndex information filePageRowKey valueLogical file
RetrievalNOWAIT specified[Figure][Figure][Figure][Figure][Figure][Figure]
WITH SHARE specified[Figure][Figure][Figure]B[Figure][Figure]
WITH EXCLUSIVE specified[Figure][Figure][Figure]B[Figure][Figure]
FOR UPDATE clause specified[Figure][Figure][Figure]B[Figure][Figure]
None of the above[Figure][Figure][Figure]B[Figure][Figure]
Updating[Figure][Figure][Figure]R[Figure][Figure]
Addition[Figure][Figure][Figure]R[Figure][Figure]
Deletion[Figure][Figure][Figure]R[Figure][Figure]
LOCK statementSHARE specified[Figure][Figure][Figure][Figure][Figure][Figure]
EXCLUSIVE specified[Figure][Figure][Figure][Figure][Figure][Figure]
Table deletion[Figure][Figure][Figure][Figure][Figure][Figure]
IndexDefinition[Figure][Figure][Figure][Figure][Figure][Figure]
Deletion[Figure][Figure][Figure][Figure][Figure][Figure]
Deletion of all rows[Figure][Figure][Figure][Figure][Figure][Figure]
Table definition change[Figure][Figure][Figure][Figure][Figure][Figure]
[Figure]: 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 statementResource
Higher level --------------------- Lower level
Inner replica config*Replica group config*RDAREATableNO WAIT table
For tablesFor indexesLast HiRDB file
RetrievalNOWAIT specifiedBBBB[Figure][Figure]B
WITH SHARE specifiedBBBB[Figure]B[Figure]
WITH EXCLUSIVE specifiedBBBB[Figure]B[Figure]
FOR UPDATE clause specifiedBBBB[Figure]B[Figure]
None of the aboveBBBB[Figure]B[Figure]
UpdatingBBBB[Figure]B[Figure]
AdditionBBBB[Figure]B[Figure]
DeletionBBBB[Figure]B[Figure]
LOCK statementSHARE specifiedBBBB[Figure]B[Figure]
EXCLUSIVE specifiedBBBB[Figure]B[Figure]
Table deletion[Figure][Figure]BB[Figure]BB
IndexDefinition[Figure][Figure]BB[Figure]BB
Deletion[Figure][Figure]BB[Figure]BB
Deletion of all rowsBBBB[Figure]BB
Table definition change[Figure][Figure][Figure]B[Figure]BB
Legend:
[Figure]: 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 statementResource
Higher level --------------------- Lower level
IndexIndex information filePageRowKey valueLogical file
RetrievalNOWAIT specified[Figure][Figure][Figure][Figure][Figure][Figure]
WITH SHARE specified[Figure][Figure][Figure]B[Figure]1[Figure]
WITH EXCLUSIVE specified[Figure][Figure][Figure]B[Figure]1[Figure]
FOR UPDATE clause specified[Figure][Figure][Figure]B[Figure]1[Figure]
None of the above[Figure][Figure][Figure]B[Figure]1[Figure]
Updating[Figure][Figure][Figure]R[Figure]2[Figure]
Addition[Figure][Figure][Figure]R[Figure]3[Figure]
Deletion[Figure][Figure][Figure]R[Figure]3[Figure]
LOCK statementSHARE specified[Figure][Figure][Figure][Figure][Figure][Figure]
EXCLUSIVE specified[Figure][Figure][Figure][Figure][Figure][Figure]
Table deletion[Figure][Figure][Figure][Figure][Figure][Figure]
IndexDefinition[Figure][Figure][Figure][Figure][Figure][Figure]
Deletion[Figure][Figure][Figure][Figure][Figure][Figure]
Deletion of all rows[Figure][Figure][Figure][Figure][Figure][Figure]
Table definition change[Figure][Figure][Figure][Figure][Figure][Figure]
Legend:
[Figure]: 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).