Cursors WITH HOLD
Programming WITH HOLD
cursors using SELECT
with and
without FOR UPDATE
clause.
Type of WITH HOLD cursors
Make sure to distinguish cursors declared WITH HOLD
for regular
SELECT
statements, and WITH HOLD
cursors declared for a
SELECT
statements using the FOR UPDATE
clause.
Regular WITH HOLD cursors (without FOR UPDATE clause in SELECT)
SELECT
statement is not defined with a FOR UPDATE
clause.DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM customers
Therefore, a cursor declared WITH HOLD
with a SELECT
statement
not using the FOR UPDATE
clause can be used with most databases. However, some
databases close any cursor (even declared without FOR UPDATE
), when a transaction
is rolled back, or when the cursor is opened inside the transaction block.
Database Server Type | WITH HOLD support |
---|---|
IBM® DB2® LUW | Partial, see details |
IBM Informix® | Yes, native SQL feature |
IBM Netezza | No, see details |
Microsoft™ SQL Server | Yes, see details |
Oracle® MySQL / MariadDB | Yes, see details |
Oracle Database Server | Yes, see details |
PostgreSQL | Partial, see details |
SAP HANA® | Yes, see details |
SQLite | Yes, see details |
Cursors WITH HOLD using SELECT FOR UPDATE
WITH HOLD
cursors using the FOR UPDATE
clause:DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM customers FOR UDPATE
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).
Not all database brands allow "for update" cursors to remain open across transactions: 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.
Database Server Type | WITH HOLD FOR UPDATE support |
---|---|
IBM DB2 LUW | No, see details |
IBM Informix | Yes, native SQL feature |
IBM Netezza | No, see details |
Microsoft SQL Server | No, see details |
Oracle MySQL / MariadDB | No, see details |
Oracle Database Server | No, see details |
PostgreSQL | No, see details |
SAP HANA | No, see details |
SQLite | No, see details |
It is mandatory to review code using WITH HOLD
cursors with a
SELECT
statement having the FOR UPDATE
clause.
FOR UPDATE
cursor (without
WITH HOLD
clause) 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
SELECT
FOR UPDATE
, for each row 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 FOR
END IF
UPDATE master SET ... WHERE CURRENT OF c2
UPDATE detail SET ... WHERE master_key=mkeys[x]
COMMIT WORK
END FOR
WHERE CURRENT OF
clause by WHERE pkey-column =
?
, which is a more portable syntax. With some types of databases, the WHERE CURRENT
OF
clause has to be emulated, or is simply unsupported. For more details, see Positioned UPDATE/DELETE.