Using Cursors in a Transaction Processing

From: Michael Russell <mrussell_at_beeb.net>
Date: 29 Jan 2002 05:01:33 -0800
Message-ID: <c69419da.0201290501.2c9685e3_at_posting.google.com>



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?

Perhaps there's something simple I've yet to read about, but ....

Once again, I'll welcome your advice, suggestions or educative comments.

Regards

Michael Russell Received on Tue Jan 29 2002 - 14:01:33 CET

Original text of this message