Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

The following code when the 'update stock' condition returns 0, executes sql_error()'s sql 'insert into sql_error' AND dosql()'s 'insert into stock_valid' ,despite that the first statement of sql_error() is $dbh->rollback. I know that wrapping do_sql()'s sql statements in $dbh->begin_work would probably make it work, but I'm interested in the background of why it is happening.

I have a theory that every 'do' statement does start a new implicit transaction. So let's say that do(insert into stock_valid) is T1 and do(update stock) is T2. Then, T1 is rolled back when the rollback of sql_error() is hit, while T2 is not rolled back and instead is committed when $dbh->commit; of sql_error() is hit.

My 2cents. What do you think?

$dbh->{AutoCommit}=0;
$dbh->{RaiseError}=1;
$dbh->{HandleError}=\&handle_error;

sub do_sql() {
       $dbh->do(qq{
           insert into stock_valid
           (last_year,orderid,suppliervatno,
           code,executedquantity,productprice)
           values
           (
             $last_year,$orderid,$suppliervatno,
             $code,$executedquantity,$productprice
           )
        });


       my $rowsupdated=$dbh->do(qq{
               update stock 
               set quantity=quantity+$executedquantity
               where code=$code
              });
      if ($rowsupdated==0) {
       sql_error($order,'no rows found','LocalError');
      }
$dbh->commit;
$dbh->disconnect;
}
 
 
sub sql_error() {
  $dbh->rollback;
  my $orderid=$dbh->quote(shift);
  my $resultreason=$dbh->quote(shift);
  my $resultcode=$dbh->quote(shift);
  
  $dbh->do(qq{
      insert into sql_error
      (orderid,resultreason,resultcode)
      values
      (
      $orderid,$resultreason,$resultcode
      )
    });

   $dbh->commit;
   $dbh->disconnect;
   die;
}

[download]

Grandfather replaced pre tags with Code tags and fixed paragraphing.

Replies are listed 'Best First'.
Re: Does each DBI 'do' start a new transaction?
by ikegami (Patriarch) on Mar 19, 2024 at 19:34 UTC

    do doesn't have anything to do with transaction. AutoCommit might use transactions which are automatically ended and restarted, but that's off.

[reply]
[d/l]
[select]
Re: Does each DBI 'do' start a new transaction?
by talexb (Chancellor) on Mar 19, 2024 at 16:30 UTC

    If you look at the DBI documentation, you'll see that the do method returns a success or failure status. In the code you've posted, you're throwing away that information. Developer Tip: Don't Do That.

    Assume that any operation can fail, at any time. As soon as there's a failure, you have to report that error.

    Alex / talexb / Toronto

    Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

[reply]
[d/l]

      They're using an error handler ($dbh->{HandleError}=\&handle_error;)

[reply]
[d/l]

        I missed that. :( Now, what happens when there's a DBI error inside the error handler? Does it call itself until it hits the 'deep recursion' error? That would be a good place to locally delete the error handler and do something different with that error.

        Alex / talexb / Toronto

        Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

[reply]
      I have learned through much pain that the most fun, comfortable way to program actually anything, is to deal with all errors when starting a new thing. Design how you're going to indicate errors (exceptions? return values? if so, as a struct, or as an enum?). Deal with incorrect inputs by returning errors. Then write tests checking all of those errors are being detected. And then start writing the substantive code. Then you have guaranteed-good inputs for the actual program/function, which is surprisingly nice. Any other approach is more painful and takes longer.

      An unexpected (for me) illustration of this approach's value was very recent, when I was using the newly-incorporated Test::PDL to update all the tests of PDL to use is_pdl, which checks types, dimension, bad value patterns, NaNs, numerical values, and reports in some detail any failures. The tests thus modified are SO much simpler, with no need for extra code to dump any incorrect inputs, and have actually caught at least one bug (incorrect handling of converting a negative floating-point number into an unsigned value - on ARM, it gets a 0, even though on Intel it gets the INT_MAX+number+1).

[reply]
[d/l]
Re: Does each DBI 'do' start a new transaction?
by LanX (Saint) on Mar 19, 2024 at 14:34 UTC
    You should better tell us which DB-server and probably which engine you use.

    Not all support transactions, and the according DBI functions will silently fail. This will also depend on the DBD.pm used.

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    see Wikisyntax for the Monastery

[reply]
[reply]

Back to Seekers of Perl Wisdom