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:
- Single transaction
- Begin transaction
- For each record
- Insert record
- If error, rollback and exit
- Commit
- Multiple transaction
- For each record
- Begin transaction
- Insert record
- Commit/rollback
- Commit in blocks
- Begin transaction
- For each record
- If recordN=100 then
- Insert record
- If error, rollback and exit
- Commit transaction
- Using savepoint
- Begin transaction
- For each record
- Savepoint
- Insert record
- If error, rollback to savepoint and continue processing
- Commit transaction
- 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.
- The use of one transaction for each record is the easiest way to
implement it but it sounds like pretty intensive for the database.
- 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.
- 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
Received on Wed Dec 28 2005 - 02:08:21 CST
