Re: Locking outside COMMIT WORK

In <325431FF.52D0@centrum.is> "John H. Frantz" <frantz@centrum.is> writes: >A CURSOR WITH HOLD can be combined with FOR UPDATE, but be aware >that after a COMMIT WORK, all locks are released. You then have >to begin a new transaction to be able to fetch the next row >which will be locked in that transaction, but all previously >fetched rows have been released. >I agree that being able to lock outside of transactions would >be a useful feature. Is there any theoretical reason why it >shouldn't exist? Apart from being ANSI compliant, I think it might muddy the water of what a transaction is s'posed to mean. If you can be "sure" of full committment of data in a transaction (versus roll it back if it fails), what does a "commit" inside a transaction mean, if a subsequent catastrophic failure leaves locks (with possibly modified data) outside of the transaction? An interesting idea might be nested transactions, where you could put together code like begin work <tansaction_name> do stuff select things foreach things begin work <transaction_sub_name> do morestuff if morestuff_ok commit work <transaction_sub_name> else rollback work <transaction_sub_name> end if end foreachif stuff_ok commit work <transaction_name> else rollback work <transaction_name> end if In this instance, by the power of the black box :), locks and cursors opened in transaction_name remain until the final commit/rollback. Locks and cursors in the sub-transactions live only as long as that transaction lasts. Finally, all sub-tranactions must "commit", or the master transaction will roll back the whole lot. Bryan Tonnet batonnet@zeta.org.au