Re: HELP: Long (lasting) transactions?

In article <3makt8$sv@hustle.rahul.net> billb@lore.kla.com "Bill Baloglu" writes: > We are designing a C++ application which will initially > run on ORACLE (and subsequently may run on Sybase and > Informix). In this application, there will be scenarios > under which a database transaction may be open for a few > days. > > We are told that under ORACLE, having a transaction open > for long durations (as in days) does not present any > significant impact on performance and other database > resources. Is this correct? What about with Sybase and > Informix? Is there anyone who may wish to caution us on > any potential problems that such long lasting > transcations would present. I for one, thought that > there could be problems with tranaction log files. > > Thanks in advance > > --bill > > The log file containing the start of transaction (and following log files) cannot be archived until the transaction has ended. If, after the start but before the end of the long transaction, enough other transactions occur to fill the remaining logs then the long transaction would be rolled back. Also, any rows you update, insert, or delete within the transaction will remain locked until the end of transaction. I would be interested to know how Oracle would handle this. If Oracle starts a long transaction in a log file then fills all remaining log files what do you do? If you archive the logs how do you rollback the transaction? How does it handle locking during these transactions? It might be an idea to go back to the people who told you Oracle would be ok and get confirmation. -- -------------------------------------------------------------------------- paul sellars,humberside,u.k. ==========================================================================