Use "begin transaction + commit/rollback" or not?
-
Hi
I have an overnight process that takes transactions from an external system & applies updates to a single db table. Other processes may be active on the db but none touch the tables I'm using. I cannot guarantee the volume of source transactions (may vary from 100s to 100,000s).
My question is should I protect the update within a begin+commit/rollback or should I have a recovery procedure to run in the event of failure (that would delete any rows added to my db table)? (My preference is to do the latter - so I'm really looking for any reasons why I shouldn't take this approach).
Thanks.
-
If you have two or more SQL Statements that are dependent on each other then I would wrap them up into a transaction.
If you are just running a single statement then there is an implicit transaction in any case.
Can I presume that wiping out the rows added to the table means just the rows recently added rather than the whole table?
If you do a lot of deletes it may be worth your while defragging any indices on your table once you finish.
-
If you view the update of all transactions as one unit, then yes - you could (or even should) wrap it in an explicit transaction - if for nothing else, explicit control rather than letting the system implicitly do what you want.. (always a bad idea..
)"Trick question": What's the difference between -
BEGIN TRAN
.. do some stuff
if error
ROLLBACK
and:
... do some stuff
if error
try to undo stuff on my own...
Answer (imo) the latter is doomed to fail.
There's no reason to try rolling your own transaction handling when it's already there for you.
/Kenneth
-
If you are:
1. Certain that no other processes may cause concurrency problems, and
2. Certain that you can create a recovery procedure that discovers whether anything has gone wrong (and correct it),
then I would have used the recovery procedure. The problem is largely to be certain that you'll discover whether any small, intermediate step have failed without doing proper transactional programming. This can be very tricky - depending on your business logic.
If you are certain on the two questions above, then a recovery procedure in the event of failure will probably be the faster and better solution.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply