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?
Genero db	Yes (if primary key or unique index)
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, you can declare a normal cursor and do a SELECT FOR UPDATE inside each transaction, as in this example:

DECLARE c1 CURSOR FOR SELECT key FROM master ...
DECLARE c2 CURSOR FOR SELECT * FROM master WHERE key=? FOR UPDATE
FOREACH c1 INTO mrec.key
  BEGIN WORK
  OPEN c2 USING mrec.key
  FETCH c2 INTO rec.*
  IF STATUS==NOTFOUND THEN
     ROLLBACK WORK
     CONTINUE FOREACH
  END IF
  UPDATE master SET ... WHERE CURRENT OF c2
  UPDATE detail SET ... WHERE mkey=mrec.key
  COMMIT WORK
END FOREACH