Re: Using Cursors in a Transaction Processing

From: Heinz Huber <hhuber_at_racon-linz.at>
Date: Tue, 29 Jan 2002 15:04:01 +0100
Message-ID: <3C56ABD1.4030106_at_racon-linz.at>


Michael Russell wrote:

> I'm converting a "legacy" application from ISAM-based files to an
> RDBMS. Currently, parts of the application would have an "update-loop"
> based on the sequence of records returned via an index; records will
> be re-written or deleted, then the next record in the path of the
> index will be processed, and so on. Always within a begin-end
> transaction.
>
> To emulate the keyed sequence's retrieval via an ISAM index, I'd
> expected to use "cursors" from within the application programs using
> the RDBMS. However, my primary reference text at the moment, "SQL: The
> Complete Reference" by Groff & Weinberg, says p.540: "COMMIT and
> ROLLBACK statements automatically close all open cursors".
>
> Not knowing any better, I have to suppose that this is true. In which
> case there are some consequent questions:
>
> Does opening a cursor entail quite a bit of overhead -- i.e. is it an
> operation that one should not re-do following each (un)completed
> transaction? It seems obvious that the answer must be a "yes - don't
> do it!", but I need to ask.
>
> If this closing of cursors means they (cursors) cannot be used
> effectively in transaction processing for more than a trivial-sized
> population, what alternatives might one consider?

There should be some option when declaring the cursor. I don't know whether it's standard, therefore you'll have to look up in the documentation of your RDMBS.

The one I know is ON COMMIT PRESERVE.

hth,
Heinz Received on Tue Jan 29 2002 - 15:04:01 CET

Original text of this message