Nonstop Database, HiRDB Version 9 UAP Development Guide
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 |
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.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.