|
|
|
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 "Understanding 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.
For details, see 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 and 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:
/* 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;
This declaration is illegal because two tables were used in the select statement.
For example, if empdept is a read-only view, the following example 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;
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 updateable 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.
|
|
|
There are two update modes for cursors: deferred and direct.
In deferred mode, cursor updates take effect when the cursor is closed. Only thereafter are the updates visible to the program that opened the cursor. The actual committal of the changes does not override or interfere with commit or rollback statements that can be executed subsequently in the program. Transaction semantics, such as the release of locks and external visibility to other programs, are not changed by using the deferred mode of update.
Only one update or delete against a row fetched by a cursor opened in the deferred mode can be executed. If an attempt to update such a row is made more than once, or if the row is updated and deleted, the DBMS Server returns an error indicating that an ambiguous update operation was attempted.
Only one cursor can be open at a time in the deferred mode.
In direct mode, updates associated with the cursor take effect on the underlying table when the statement is executed, and can be seen by the program before the cursor is closed. The actual committal of the changes does not override or interfere with commit or rollback statements subsequently executed in the program. Because changes take effect immediately, avoid updating keys that cause the current row to move forward with respect to the current position of the cursor, because this can result in fetching the same row again at a later point.
Multiple update statements can be issued against a row that was fetched from a cursor opened in the direct mode. This enables a row to be updated and deleted.
Note: The default cursor mode is specified at the time the DBMS Server is started. For compliance with the ANSI/ISO SQL-92 standard, the default cursor mode must be direct mode.