3.5.3 Cursor declarations and locks

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 statementWITH EXCLUSIVE LOCK assumed during FOR UPDATE processingData guarantee levelUpdate permission using cursor1Lock option during table manipulation and assumed value in FOR UPDATE clause
SpecifiedWITH EXCLUSIVE LOCK[Figure]2[Figure]2NoWITH EXCLUSIVE LOCK
YesWITH EXCLUSIVE LOCK FOR UPDATE
WITH SHARE LOCKNoWITH SHARE LOCK
YesWITH SHARE LOCK FOR UPDATE
WITHOUT LOCK WAITNoWITHOUT LOCK WAIT
YesWITHOUT LOCK WAIT FOR UPDATE
WITHOUT LOCK NOWAITNoWITHOUT LOCK NOWAIT
YesError
Not specifiedYes2NoWITH SHARE LOCK
YesWITH EXCLUSIVE LOCK FOR UPDATE
1NoWITHOUT LOCK WAIT
YesWITHOUT LOCK WAIT FOR UPDATE
0NoWITHOUT LOCK NOWAIT
YesWITHOUT LOCK WAIT FOR UPDATE
No2NoWITH SHARE LOCK
YesWITH EXCLUSIVE LOCK FOR UPDATE
1NoWITHOUT LOCK WAIT
YesWITHOUT LOCK WAIT FOR UPDATE
0NoWITHOUT LOCK NOWAIT
YesWITHOUT LOCK WAIT FOR UPDATE
Legend:
[Figure]: 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.

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.