WITH HOLD and FOR UPDATE

Hold cursors and not portable.

IBM^® Informix^® supports WITH HOLD cursors using the FOR UPDATE clause. Such cursors can remain open across transactions (when using FOR UPDATE, locks are released at the end of a transaction, but the WITH HOLD cursor is not closed). This kind of cursor is IBM Informix-specific and not portable. The SQL standards recommend closing FOR UPDATE cursors and release locks at the end of a transaction. Most database servers close FOR UPDATE cursors when a COMMIT WORK or ROLLBACK WORK is done. All database servers release locks when a transaction ends.

Table 1. Database server support of WITH HOLD FOR UPDATE
Database Server Type	WITH HOLD FOR UPDATE supported?
IBM DB2^® UDB	No
IBM Informix	Yes
Microsoft™ SQL Server	No
MySQL	No
Oracle Database Server	No
PostgreSQL	No
Sybase ASE	No
SQLite	No

It is mandatory to review code using WITH HOLD cursors with a SELECT statement having the FOR UPDATE clause.

The standard SQL solution is to declare a simple FOR UPDATE cursor outside the transaction and open the cursor inside the transaction:

DECLARE c1 CURSOR FOR SELECT ... FOR UPDATE
BEGIN WORK
  OPEN c1
  FETCH c1 INTO ...
  UPDATE ...
COMMIT WORK

If you need to process a complete result set with many rows including updates of master and detail rows, first fetch the primary keys of all master rows into a program array, declare a cursor with the SELECT FOR UPDATE, then for all rows in the array, start a transaction and perform the UPDATE WHERE CURRENT OF for the current master record and the UPDATE for detail rows, then commit the transaction and continue with the next master record:

DEFINE x, mkeys DYNAMIC ARRAY OF INTEGER
DECLARE c1 CURSOR FOR SELECT key FROM master ...
FOREACH c1 INTO x
    LET mkeys[mkeys.getLength()+1] = x
END FOREACH
DECLARE c2 CURSOR FOR SELECT * FROM master WHERE key=? FOR UPDATE
FOR x = 1 TO mkeys.getLength()
  BEGIN WORK
  OPEN c2 USING mkeys[x]
  FETCH c2 INTO mrec.*
  IF STATUS==NOTFOUND THEN
     ROLLBACK WORK
     CONTINUE FOREACH
  END IF
  UPDATE master SET ... WHERE CURRENT OF c2
  UPDATE detail SET ... WHERE master_key=mkeys[x]
  COMMIT WORK
END FOR