Re: HELP: Long (lasting) transactions?

In article: <3makt8$sv_at_hustle.rahul.net> billb_at_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
>

There are potentially two problems you would encounter with Oracle. First, the rollback segment to which the long transaction is allocated will probably grow *very* large! Rollback segments cannot reuse extents until all transaction entries in those extents have been released. What other work do you plan to run alongside these long transactions?

Secondly, the rows locked by the transaction would be unavailable for other transactions.

The redo logs are *not* a problem, and they can happily run through several cycles during a single transaction, archiving or not.

I would suggest that unless you have adequately addressed the above, it would be very unwise to attempt what you suggest. But then, it might just work...

Good luck!

-- 
---------------------------------------------------------------------------
| Keith Bremer    EMail kbremer_at_lecky.demon.co.uk                         |
---------------------------------------------------------------------------