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. Long transactions should be avoided at all costs. As well as the reasons Paul has given, how would you feel about losing all related work from the previous few days if it got rolled back? Long transactions are *really* bad news. I can't see that it could be any different in Sybase or Oracle. Nor can I imagine any justification for them. Sounds like you need to rethink your design. For example, if the problem is transmitting data to a remote site that could be unreachable for a time, store the details of the update in an intermediate table and use flags to monitor the status of the transmission. akent@cix.compulink.co.uk (Andy Kent) ------------------------------------------------ Freelance Informix Database Specialist, Redland, Bristol, England