CODEWITHSUNDEEP
perlsql-server-2008transactionsnesteddbi
Gopal SA
Gopal SA

Reputation: 959

Perl DBI begin_work and nested transactions interacting with SQL server 2008

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;
           }
    }

Upvotes: 3

Views: 2948

Answers (1)

Toto
Toto

Reputation: 91518

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.

Upvotes: 1

Related Questions