Managing transations involving thousands of records

Hi,

My application needs to upload some thousands of records into a
database. Each record insertion involves some validation process and
more than one insert operation into the database. Each record is
independent of each other so if one insertion fails, process can go on
with the rest of the records.

In this scenario, four different ways of implementing transaction
management came to my mind:

A. Single transaction

- Begin transaction
- For each record
- Insert record
- If error, rollback and exit
- Commit

B. Multiple transaction
- For each record
- Begin transaction
- Insert record
- Commit/rollback

C. Commit in blocks
- Begin transaction
- For each record
- If recordN=100 then
- Commit
- Begin transaction
- Insert record
- If error, rollback and exit
- Commit transaction

D. Using savepoint
- Begin transaction
- For each record
- Savepoint
- Insert record
- If error, rollback to savepoint and continue processing
- Commit transaction

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.

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.

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.

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.

Thanks in advance
Jorge

.

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: 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: 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)
  • Re: Behavior of Connection.commit()
    ... we would not throw an exception: ... transaction under the covers. ... //COMMIT will no longer throw an exception, ... >>> database. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Delete one line out of a text file?
    ... transaction number? ... with another table in the database (part of the same atomic ... Then if the commit is successful, ...
    (comp.soft-sys.matlab)
Loading