There are two cursor modes: read-only and update. As the names suggest, read-only cursors can only display data from a select statement; update cursors can be used to perform positioned updates and deletes.
Read-only mode uses shared page or row locks. If read committed with lock is set to 0, and the query runs at isolation level 1, it uses instant duration locks, and does not hold the page or row locks until the next fetch.
Read-only mode is in effect when you specify for read only or when the cursor’s select statement uses distinct, group by, union, or aggregate functions, and in some cases, an order by clause.
Update mode uses update page or row locks. It is in effect when:
You specify for update.
The select statement does not include distinct, group by, union, a subquery, aggregate functions, or the at isolation read uncommitted clause.
You specify shared.
If column_name_list is specified, only those columns are updatable.
For more information on locking during cursor processing, see System Administration Guide.
Specify the cursor mode when you declare the cursor. If the select statement includes certain options, the cursor is not updatable even if you declare it for update.
