What's the disadvantage of WITH HOLD cursor?
Carl Y. Wu asked why cursors close at COMMIT WORK and whether declaring all cursors WITH HOLD was a problem. Chris Brauer quoted the manual: a scroll cursor with hold outlives the transaction's locks, so its temporary-table data can become inconsistent; use it only in special cases. Manel Falco i Aige and Art S. Kagel use hold cursors over a driver table with per-row transactions.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
A cursor will be closed by COMMIT WORK unless you declare it "WITH HOLD". What's the real advantage of this default behaviour? Is there any problem if I declare all cursors "WITH HOLD" to avoid this default behaviour? Regards, Carl Wu
Hi Carl, the manual says: If you use a scroll cursor with hold in a transaction, you cannot force consistency between your temporary table and the database table. A table-level lock or locks that are set by Repeatable Read are released when the transaction is completed, but the scroll cursor with hold remains open beyond the end of the transaction. You can modify released rows as soon as the transaction ends, but the retrieved data in the temporary table might be inconsistent with the actual data. That sounds for me not to use this feature as default but in special cases. Hth, Chris Carl Y. Wu schrieb: > A cursor will be closed by COMMIT WORK unless you declare it "WITH HOLD". > What's the real advantage of this default behaviour? > Is there any problem if I declare all cursors "WITH HOLD" to avoid this > default behaviour? > > Regards, > Carl Wu
I use them like here: foreach ... hold cursor begin work; sql, etc commit work; end foreach any problem ? Manel
That's how I use them also, to maintain a cursor on a driver table and commit individual transactions. This is one of the special purposes Chris indicated. Art S. Kagel "Manel Falcó i Aige" wrote: > > I use them like here: > > foreach ... hold cursor > > begin work; > > sql, etc > > commit work; > > end foreach > > any problem ? > Manel