Cursor Updates
Unless the cursor is explicitly opened in readonly mode or if table level lock granularity is in effect or if the transaction isolation level is read uncommitted, an update mode lock is obtained at the row or page level granularity, as appropriate. The granularity of locking used depends on many factors, including the effects of several set options, the estimated selectivity of the select criteria, various lock configuration parameters, and the page size used by the table. For a complete explanation, see the chapter on the locking system in the Database Administrator Guide.
If an update is performed, this lock is converted to an exclusive lock. If the cursor moves off the page or row without performing an update, the lock is converted to share mode if the isolation level is repeatable read or serializable, or the lock is released if the isolation level is read committed.
If the isolation level is read uncommitted, updates are implicitly forbidden, and no logical locks are taken on the table.
If isolation level is not read, uncommitted and table level lock granularity is used and the cursor was not opened in readonly, a single exclusive lock at the table level is taken.
If updates are not to be performed with the cursor, cursor performance can be improved by specifying for readonly when the cursor is
opened (see page
OPEN). (If the SELECT statement of the cursor contains one or more aggregate functions, the cursor is read-only.)
For details about updating or deleting table rows using a cursor, see
Update (see page
UPDATE) and
Delete (see page
DELETE).
A cursor cannot be declared FOR UPDATE if its SELECT statement:
• Refers to more than one table.
For example, the following cursor declaration causes a compile-time error, and is illegal because two tables are used in the SELECT statement:
/* illegal join on different tables for update */
EXEC SQL DECLARE c1 CURSOR FOR
SELECT employee.id, accounts.sal
FROM employee, accounts
WHERE employee.salno = accounts.accno
FOR UPDATE OF sal;
In the following example, if empdept is a read-only view, the following code generates a runtime error when the OPEN statement is executed. No preprocessor error is generated, because the preprocessor does not know that empdept is a view.
/* empdept is a read-only view */
EXEC SQL DECLARE c2 CURSOR FOR
SELECT name, deptinfo
FROM empdept
FOR UPDATE OF deptinfo;
EXEC SQL OPEN c2;
• Includes a DISTINCT, GROUP BY, HAVING, ORDER BY, or UNION clause.
• Includes a column that is a constant or is based on a calculation.
For example, the following cursor declaration causes an error when attempting to update the column named constant:
/* "constant" cannot be declared for update */
EXEC SQL DECLARE c3 CURSOR FOR
SELECT constant = 123, ename
FROM employee
FOR UPDATE OF constant;
If an updatable column has been assigned a result column name using the syntax:
result_name = column_name
or:
column_name as result_name
the column referred to in the FOR UPDATE list must see the table column name, and not the result column name.