3

I have a scenario like below. Where the main method needs to call multiple methods , 1 method for 1 table , and each method runs a set of statements which has to be atomic. So they are enclosed within a begin_work,commit,rollback block.

Also call_method_for_table1,call_method_for_table2,call_method_for_table3 should all succeed or fail together which means they have to be atomic. That is why begin_work,commit,rollback block are added in main method also. But i can see that perl does not allow me . I get exception - "DBD::ODBC::db begin_work failed: Already in a transaction". Now i do not have any way of modifying the call_method_for_table* methods since it is in a library and modifying it is not possible due to many reasons.

Can i use checkpoints to solve this problem

  1. Will Savepoints help (http://msdn.microsoft.com/en-us/library/ms378414%28v=SQL.105%29.aspx)
  2. Will Rolling back till savepoint work even if there are some commits in between (which is usually the case in my example)
  3. Is it fine even when there are parallel runs of the same process ?
  4. Will it cause data inconsistency?
sub main {
        $dbh->begin_work;
           eval {
              call_method_for_table1();
              call_method_for_table2();
              call_method_for_table3();
              $dbh->commit;
              1; 
        };

        if ($@) {
              $dbh->rollback; }
       }

    sub call_method_for_table1 {
    $dbh->begin_work;
           eval {
           $dbh->do($INSERTSTATEMENT_TABLE1);
           $dbh->do($UPDATESTATEMENT_TABLE1);
           $dbh->do($DELETESTATEMENT_TABLE1);
           $dbh->commit; 
           };

           if ($@) {
             $dbh->rollback;
           }
    }
Gopal SA
  • 949
  • 2
  • 17
  • 36

1 Answers1

1

I think it's impossible without modifying the call_method_for_tableX.

Suppose call_method_for_table1 succeeds, then the commit is done and you can't rollback after call_method_for_table2 eventually failed.

Toto
  • 89,455
  • 62
  • 89
  • 125
  • Will Savepoints(http://msdn.microsoft.com/en-us/library/ms378414%28v=SQL.105%29.aspx) help if i call it through the PERL DBI. And is it fine even when there are parallel runs of the same process ? Will it cause data inconsistency? – Gopal SA Jun 06 '11 at 12:55
  • @TGV: Sorry, i don't know SQL-Server but from what i read on the link, it seems Savepoints allow to rollback just a portion of a transaction and you want to rollback more than one commited transaction so it doesn't seem usefull for your problem. – Toto Jun 06 '11 at 13:03
  • Yeah , looks like it does not help when something is committed after the savepoint. – Gopal SA Jun 06 '11 at 13:49