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

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.

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 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.

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 config6	Replica group config8	RDAREA			Table	NO WAIT table
					For tables	For IX	Last HiRDB file5
Retrieval	NOWAIT specified		SR	SR	SR				SR
	WITH SHARE specified		SR	SR	SR			SR
	WITH EXCLUSIVE specified1		SR	SR	SU	SR		SU
	FOR UPDATE clause specified1		SR	SR	SU	SR		SU
	None of the above		SR	SR	SR			SR
Updating1, 12			SR	SR	SU		EX	SU
Addition1			SR	SR	SU		EX	SU
Deletion1			SR	SR	SU			SU
LOCK statement	SHARE specified11		SR	SR	SR			PR
	EXCLUSIVE specified	Unshared table	SR	SR	SU			EX
		Shared table11	SR	SR	EX	EX		EX
Table deletion2, 13					SU			EX	EX
Index	Definition13				SU			EX
	Deletion3, 13				SR10	SU		EX4	EX
Deletion of all rows2, 13			SR	SR	SU			EX	EX
Table definition change13			SR9	SR9	SU7			EX	EX

: Locking is not applied.

(code): Lock mode.

IX: indexes

¹ 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.

² All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.

³ All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.

⁴ Plug-in indexes are locked in the EX mode, but B-Tree indexes are not locked.

⁵ 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.

⁶ If the inner replica facility is used, the server containing the RDAREA to be processed is locked.

⁷ If an RDAREA is added or is altered with the free space reusage facility, the RDAREA is locked.

⁸ When updatable online reorganization is executed, the replica group containing the RDAREA to be processed is locked.

⁹ If an RDAREA to be processed is accessed, the RDAREA is locked.

¹⁰ If the inner replica facility is applied, the resource is locked.

¹¹ When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.

¹² 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.

¹³ 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 environment			Resource
			Higher level --------------------- Lower level
			Index	Index information file4	Page	Row	Key value	Logical file
Retrieval	NOWAIT specified							PR
	WITH SHARE specified					PR	PR	PR
	WITH EXCLUSIVE specified1					EX	PR	EX
	FOR UPDATE clause specified1					EX	PR	EX
	None of the above					PR	PR	PR
Updating1, 6				EX		EX	EX	EX
Addition1				EX		EX	EX	EX
Deletion1						EX8	EX	EX
LOCK statement	SHARE specified5
	EXCLUSIVE specified	Unshared table
		Shared table5
Table deletion2, 7
Index	Definition7
	Deletion3, 7		EX
Deletion of all rows2, 7
Table definition change7

: Locking is not applied.

(code): Lock mode.

¹ 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.

² All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.

³ All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.

⁴ 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.

⁵ When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.

⁶ 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.

⁷ 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.

⁸ 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 environment			Resource
			Higher level --------------------- Lower level
			Inner replica config6	Replica group config8	RDAREA			Table	NO WAIT table
					For tables	For IX	Last HiRDB file5
Retrieval	NOWAIT specified		SR	SR	SR				SR
	WITH SHARE specified		SR	SR	SR			SR
	WITH EXCLUSIVE specified1		SR	SR	SU	SR		SU
	FOR UPDATE clause specified1		SR	SR	SU	SR		SU
	None of the above		SR	SR	SR			SR
Updating1, 12			SR	SR	SU		EX	SU
Addition1			SR	SR	SU		EX	SU
Deletion1			SR	SR	SU			SU
LOCK statement	SHARE specified11		SR	SR	SR			PR
	EXCLUSIVE specified	Unshared table	SR	SR	SU			EX
		Shared table11	SR	SR	EX	EX		EX
Table deletion2, 13					SU			EX	EX
Index	Definition13				SU			EX
	Deletion3, 13				SR10	SU		EX4	EX
Deletion of all rows2, 13			SR	SR	SU			EX	EX
Table definition change13			SR9	SR9	SU7			EX	EX

: Locking is not applied.

(code): Lock mode.

IX: indexes

¹ 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.

² All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.

³ All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.

⁴ Plug-in indexes are locked in the EX mode, but B-Tree indexes are not locked.

⁵ 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.

⁶ If the inner replica facility is used, the server containing the RDAREA to be processed is locked.

⁷ If an RDAREA is added or is altered with the free space reusage facility, the RDAREA is locked.

⁸ When updatable online reorganization is executed, the replica group containing the RDAREA to be processed is locked.

⁹ If an RDAREA to be processed is accessed, the RDAREA is locked.

¹⁰ If the inner replica facility is applied, the resource is locked.

¹¹ When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.

¹² 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.

¹³ 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 environment			Resource
			Higher level --------------------- Lower level
			Index	Index information file4	Page	Row	Key value	Logical file
Retrieval	NOWAIT specified							PR
	WITH SHARE specified				PR		PR	PR
	WITH EXCLUSIVE specified1				EX		PR	EX
	FOR UPDATE clause specified1				EX		PR	EX
	None of the above				PR		PR	PR
Updating1, 6				EX	EX		EX	EX
Addition1				EX	EX		EX	EX
Deletion1					EX		EX	EX
LOCK statement	SHARE specified5
	EXCLUSIVE specified	Unshared table
		Shared table5
Table deletion2, 7
Index	Definition7
	Deletion3, 7		EX
Deletion of all rows2, 7
Table definition change7

: Locking is not applied.

(code): Lock mode.

¹ 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.

² All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.

³ All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.

⁴ 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.

⁵ When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.

⁶ 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.

⁷ 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 environment			Resource
			Higher level --------------------- Lower level
			Inner replica config5	Replica group config7	RDAREA			Table	NO WAIT table
					For tables	For IX	Last HiRDB file4
Retrieval	NOWAIT specified		SR	SR	SR				SR
	WITH SHARE specified		SR	SR	SR			SR
	WITH EXCLUSIVE specified10		SR	SR	SU	SR		SU	SU
	FOR UPDATE clause specified10		SR	SR	SU	SR		SU	SU
	None of the above		SR	SR	SR			SR
Updating10, 12			SR	SR	SU		EX	SU
Addition10			SR	SR	SU		EX	SU
Deletion10			SR	SR	SU			SU
LOCK statement	SHARE specified11		SR	SR	SR			PR
	EXCLUSIVE specified	Unshared table	SR	SR	SU			EX
		Shared table11	SR	SR	EX	EX		EX
Table deletion1, 13					SU			EX	EX
Index	Definition13				SU			EX
	Deletion2, 13				SR9	SU		EX3	EX
Deletion of all rows1, 13			SR	SR	SU			EX	EX
Table definition change13			SR8	SR8	SU6			EX	EX

: Locking is not applied.

(code): Lock mode.

IX: indexes

¹ All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.

² All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.

³ Plug-in indexes are locked in the EX mode, but B-Tree indexes are not locked.

⁴ 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.

⁵ If the inner replica facility is used, the server containing the RDAREA to be processed is locked.

⁶ If an RDAREA is added or is altered with the free space reusage facility, the RDAREA is locked.

⁷ When updatable online reorganization is executed, the replica group containing the RDAREA to be processed is locked.

⁸ If an RDAREA to be processed is accessed, the RDAREA is locked.

⁹ If the inner replica facility is applied, the resource is locked.

¹⁰ 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.

¹¹ When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.

¹² 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.

¹³ 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 environment			Resource
			Higher level --------------------- Lower level
			Index	Index information file3	Page	Row	Key value	Logical file
Retrieval	NOWAIT specified							PR
	WITH SHARE specified				, PR4	PR, 4		PR
	WITH EXCLUSIVE specified6				, EX4	EX, 4		EX
	FOR UPDATE clause specified6				, EX4	EX, 4		EX
	None of the above				PR4	PR, 4		PR
Updating6, 8				EX	, EX4, 5	EX, 4, 5		EX
Addition6				EX	, EX4, 5	EX, 4, 5		EX
Deletion6					EX4, 5	EX, 4, 5		EX
LOCK statement	SHARE specified7
	EXCLUSIVE specified	Unshared table
		Shared table7
Table deletion1, 9
Index	Definition9
	Deletion2, 9		EX
Deletion of all rows1, 9
Table definition change9

: Locking is not applied.

(code): Lock mode.

¹ All segments being used for the table and associated indexes are locked in the EX mode and kept locked until the transaction is committed.

² All segments being used for the index are locked in the EX mode and kept locked until the transaction is committed.

³ 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.

⁴ 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.

⁵ If a unique index is defined, resource rows are locked, even in page locking.

⁶ 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.

⁷ When HiRDB/Parallel Server is used, locking is applied to all back-end servers when a shared table is accessed.

⁸ 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.

⁹ 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 utility	Resource#1
	Higher level --------------------- Lower level
	RDAREA				Table	NO WAIT table
	For tables#2	For indexes	For LOB	Last HiRDB file
Deletion of all rows	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.

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
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.

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	NO WAIT table
				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	NO WAIT table
				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.

¹ 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.

² The lock is released if the resource is a unique key index.

³ The lock is not released if the pd_indexlock_mode operand of the system definition is KEY (index locking is applied).