Re: Managing transations involving thousands of records

On 28 Dec 2005 00:08:21 -0800, "jortizclaver" <jortizclaver@xxxxxxxxx>
wrote:

>A. Process takes too long to complete so a rollback at the end would
>imply to restart the process. Also, I suppose a commit of thousands of
>change could be really painfull for the database.
>

A commit means just writing a commit marker in the redo log, and
releasing locks. Nothing painful about that.
Also there is something like a *logical* transaction. A logical
transaction should either be complete or incomplete. If the
transaction is big, so be it, or split it in smaller *logical*
transactions.

>B. The use of one transaction for each record is the easiest way to
>implement it but it sounds like pretty intensive for the database.
>
It is a horrible approach, as it will make your application
unscalable.
However, this is exactly the approach most database independent
software chooses, and it will *always* kill Oracle perfromance

>C. This option allows me to make a commit each 100 records, so it seems
>like it improves the performance of the process. In the the other hand,
>if a record fails, I'd reject some other records valid for my system.
>
It is an equally horrible approach as it will easily end-up in
ora-1555, snapshot too old errors. Starting from Oracle 9i, ora-1555
ends up in the alert.
>D. I perceive this last one, or at least a combination between C and D,
>like the best one but I have some concerns about the performance of the
>use of the savepoints feature.
>
>Well, someone could think I already wrote the question and the answer
>but I'd really appreciate any comment about this reflexions. I'm not a
>DBA so probably I'm wrong in more than one assumption.

Implement A and forget about the workarounds B, C, and D
They don't scale.

--
Sybrand Bakker, Senior Oracle DBA
.

Relevant Pages

  • Re: Driver AutoCommit issue
    ... I am using the container managed transaction and I expect the container to handle that for me. ... Is there different driver class that i have to use in the connection pool configuration. ... Why I am saying the DML always gets committed is when I step thought the code I can see the updated data in the database immediately after the callable statement is executed Even before the EJB method that invoked the call is completed. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: Slow connection to Oracle 9i
    ... A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. ... Slow connection to Oracle 9i ... do not get a transaction too long error (can't remember exactly what its ...
    (perl.dbi.users)
  • Re: commit
    ... I am using SQLite, and I want to select the maxfrom a certain ... gives an error telling that the database is locked if in that moment ... allowing an update until the "transaction" finishes. ... Only then does process A commit its transaction and the rowA becomes ...
    (perl.beginners)
  • Re: Support for XA
    ... I have a need to share an oracle ... but not directly at the database level. ... UR's object cache / Context acts as a software transaction ... it tells each DB transaction to commit. ...
    (perl.dbi.users)
  • Re: Managing transations involving thousands of records
    ... Where are they before they are in the database? ... This statement indicates that the logical transaction is the individual ... > C. Commit in blocks ... Often that intensity is worth it. ...
    (comp.databases.oracle.server)