Update Cursors

An update cursor is a special kind of cursor that applications can use when the row might potentially be updated. To use an update cursor, execute SELECT FOR UPDATE in your application. Update cursors use promotable locks; that is, the database server places an update lock (meaning other users can still view the row) on the row when the application fetches the row, but the lock is changed to an exclusive lock when the application uses an update cursor and UPDATE...WHERE CURRENT OF to update the row.

In some cases, the database server might place locks on rows that the database server has examined but not actually fetched. Whether this behavior occurs depends on how the database server executes the SQL statement.

The advantage of an update cursor is that you can view the row with the confidence that other users cannot change it or view it with an update cursor while you are viewing it and before you update it.

If you do not update the row, the default behavior of the database server is to release the update lock when you execute the next FETCH statement or close the cursor. However, if you execute the SET ISOLATION statement with the RETAIN UPDATE LOCKS clause, the database server does not release any currently existing or subsequently placed update locks until the end of the transaction.

The pseudocode in Figure 37 shows when the database server places and releases update locks with a cursor. At fetch row 1, the database server places an update lock on row 1. At fetch row 2, the server releases the update lock on row 1 and places an update lock on row 2. However, after the database server executes the SET ISOLATION statement with the RETAIN UPDATE LOCKS clause, it does not release any update locks until the end of the transaction. At fetch row 3, it places an update lock on row 3. At fetch row 4, it places an update lock on row 4. At commit work, the server releases the update locks for rows 2, 3, and 4.

Figure 37. When Update Locks Are Released

declare update cursor
begin work
open the cursor
fetch row 1
fetch row 2
SET ISOLATION TO COMMITTED READ
    RETAIN UPDATE LOCKS
fetch row 3
fetch row 4
commit work

In an ANSI-compliant database, update cursors are usually not needed because any select cursor behaves the same as an update cursor without the RETAIN UPDATE LOCKS clause.

The pseudocode in Figure 38 shows the database server promoting an update lock to an exclusive lock. At fetch the row, the server places an update lock on the row being fetched. At update the row, the server promotes the lock to exclusive. At commit work, it releases the lock.

Figure 38. When Update Locks Are Promoted

declare update cursor
begin work
open the cursor
fetch the row
do work
update the row (use WHERE CURRENT OF)
commit work