When a FETCH or a single-row SELECT statement is executed, the lock mode that has priority is based on the lock option specified in the cursor declaration, dynamic SELECT statement, or preprocessing for the single-row SELECT statement. If the cursor declaration does not specify a lock option, the lock mode is determined by the data guarantee level (if a data guarantee level is not specified, the default is 2). The data guarantee level is specified with PDISLLVL in the client environment definitions or with ISOLATION LEVEL in the SQL compile options specified in the procedure or trigger definitions. When the data guarantee level is used, the lock mode is also affected by whether updating (or deletion) using a cursor is specified and whether WITH EXCLUSIVE LOCK is assumed during FOR UPDATE processing.

The specification for assuming WITH EXCLUSIVE LOCK during FOR UPDATE processing requires specifying PDFORUPDATEEXLOCK in the client environment definitions and the data guarantee level (specification of FOR UPDATE EXCLUSIVE) in the SQL compile options specified in the procedure or trigger definitions.

The lock mode that takes effect during execution depends on the lock option specified in the cursor declaration (DECLARE CURSOR), the lock option in the dynamic SELECT statement specified in the cursor declaration (DECLARE CURSOR) and the cursor allocation (ALLOCATE CURSOR), or the lock option specified in the single-row SELECT statement. The following table shows the relationships between the lock option specified during cursor declaration or dynamic SELECT statement preprocessing, and the lock option specified during table operations.

For details about the lock option in the cursor declaration, see the manual HiRDB Version 9 SQL Reference.

Table 3-27 Relationships between the lock option specified during cursor declaration or dynamic SELECT statement preprocessing and the lock option specified during table operations

Lock option in SQL statement		WITH EXCLUSIVE LOCK assumed during FOR UPDATE processing	Data guarantee level	Update permission using cursor#1	Lock option during table manipulation and assumed value in FOR UPDATE clause
Specified	WITH EXCLUSIVE LOCK	--#2	--#2	No	WITH EXCLUSIVE LOCK
				Yes	WITH EXCLUSIVE LOCK FOR UPDATE
	WITH SHARE LOCK			No	WITH SHARE LOCK
				Yes	WITH SHARE LOCK FOR UPDATE
	WITHOUT LOCK WAIT			No	WITHOUT LOCK WAIT
				Yes	WITHOUT LOCK WAIT FOR UPDATE
	WITHOUT LOCK NOWAIT			No	WITHOUT LOCK NOWAIT
				Yes	Error
Not specified		Yes	2	No	WITH SHARE LOCK
				Yes	WITH EXCLUSIVE LOCK FOR UPDATE
			1	No	WITHOUT LOCK WAIT
				Yes	WITHOUT LOCK WAIT FOR UPDATE
			0	No	WITHOUT LOCK NOWAIT
				Yes	WITHOUT LOCK WAIT FOR UPDATE
		No	2	No	WITH SHARE LOCK
				Yes	WITH EXCLUSIVE LOCK FOR UPDATE
			1	No	WITHOUT LOCK WAIT
				Yes	WITHOUT LOCK WAIT FOR UPDATE
			0	No	WITHOUT LOCK NOWAIT
				Yes	WITHOUT LOCK WAIT FOR UPDATE

Legend:

--: Does not apply.

Notes

Depending on which lock option is specified, the following conditions may occur during execution:

• When WITH SHARE LOCK is specified
  Because rows in the table will be changed from the protected retrieve mode to the exclusive mode during updating, deadlock may occur.
  
• When WITHOUT LOCK WAIT is specified
  Incorrect updating (double updating) or a deletion error may occur depending on other transactions.
  
• When WITHOUT LOCK NOWAIT is specified
  If an SQL statement updates a table retrieved with WITHOUT LOCK NOWAIT specified, an error will occur.
  

#1: Update using a cursor is permitted in the following cases:

• The FOR UPDATE clause is specified.
• The FOR UPDATE clause is not specified, but there is an UPDATE or DELETE statement that specifies the same cursor (the cursor specified in the cursor declaration).

Update using a cursor is not permitted in the following case:

• The FOR UPDATE clause is not specified, and there is also no UPDATE or DELETE statement that specifies the same cursor (the cursor specified in the cursor declaration).

#2: The lock option in the SQL statement has priority regardless of the specified contents.

HiRDB might have pre-read rows targeted for retrieval by the FETCH statement. Because pre-read rows are locked, more rows might be locked than the rows acquired by the FETCH statement. In such a case, if the number of resources to be locked is estimated based on the number of rows to be retrieved by the FETCH statement, rather than on the actual number of rows retrieved, a shortage of lock buffer space might occur. If you use a cursor, estimate the number of resources to be locked based on the actual number of rows retrieved. To limit the number of resources to be locked, you must narrow down the rows by using search conditions. If the target rows cannot be narrowed down, you must consider disabling locking (such as by changing the locking units). For details about disabling locking, see 3.4.8 Locking by UAPs.