When FETCH is executed, the lock mode that has priority is the lock option specified in the cursor declaration. 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.

Table 3-25 shows the relationships between the lock option specified during cursor declaration or dynamic SELECT statement preprocessing and the lock option specified during table operations.

Table 3-25 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 cursor1	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.
  

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

² The lock option in the SQL statement has priority regardless of the specified contents.

Because HiRDB fetches all rows targeted for retrieval during the first FETCH statement when a cursor is used, a lock buffer shortage may occur. This problem is caused by an increase in the overhead associated with creation of the internal table. Therefore, before a cursor is used, the target rows should be narrowed using a search condition.

If the target rows cannot be narrowed, consider other measures to suppress locking, such as changing the unit of locking. For details about lock suppression, see 3.4.7 Lock and suppression implementable with a UAP.