Transactions, eval, local and confusion

From: Wiggins D Anconia (wiggins_at_danconia.org)
Date: 11/24/03 

Date: Mon, 24 Nov 2003 14:34:13 -0700
To: dbi-users@perl.org

I have read through the DBI docs and DBD::mysql docs a couple times and
think I have a grasp of this whole deal but thought I would see what the
gurus have to say on the matter. For reference I am at Perl 5.8.1, DBI
1.38, DBD::mysql 2.9002, mysql 3.23.56. I normally handle my own errors
rather than using RaiseError and have AutoCommit set to the default. To
prevent having to rewrite a bunch of other code at the moment my goal is
to leave it that way and "just do the right thing" where I need
transaction support. My code model (pseudo) at this point looks like:

$dbh holds an already initialized database handle

# localize the setting of dbh handle attributes
{
    local $dbh->{AutoCommit} = 0;
    local $dbh->{RaiseError} = 1;

    # now perform our steps inside of an eval, allowing us to catch the
    # exceptions triggering a rollback

    my $sth;
    eval {
        my $st = "SELECT * ....";
        my @bind = ( bind values );

        $sth = $dbh->prepare($st);
        $sth->execute(@bind);

        $dbh->commit;
    }
    if ($@) {
        $dbh->rollback;
        # throw my normal exception stuffs here
    }
}

Questions:
1) Can/should I do the 'local' setting of the attributes within the eval
block without causing any other issues? I can't think of reasons not
to, but maybe you can?

2) If the answer to number 1 is no, then presumably 'rollback' throws an
exception itself so I would have to catch it too with an 'eval'? Can
rollback fail? If I can move the settings inside of the 'eval' then
rollback failing should do "normal" error reporting correct, is that
returning undef or true?

3) Similar to #2, setting AutoCommit to 0 before setting the RaiseError
would mean I need to check that it succeeds (which is just checking the
boolean context of the attribute) moving it into the eval should mean I
can put it after the RaiseError and let the eval catch its exception as
well, correct?

Just trying to get this whole thing clear, thanks for your time,

http://danconia.org

Relevant Pages

  • [Proposal] using statement enhancements
    ... Mostly always you use the using clause you deal with native ressources, ... exception handlinjg is sooner or later inevitable. ... Rollback(); ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Exception handling best practices
    ... The original statement would work but usually is not a good practice - all ... sensitive information in the original exception that you do not want exposed ... additional context information to aid users in figuring out why it didn't ... Rollback(); ...
    (microsoft.public.dotnet.general)
  • Re: Another question regarding exceptions and loops
    ... In this case I define a transaction as saving a single Contact not the List ... > I think that having separate exceptions for when the list is partially ... > rollback for this kind of thing? ... if there is an exception that is thrown (and I hope you are ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Error when calling SqlTransaction.Rollback method
    ... I've seen this when there was an open data reader on the same connection as ... before rolling back the transaction. ... To get the exception, just debug into your code, or alternatively, ... temporarily remove the rollback code or place it in its own try catch. ...
    (microsoft.public.dotnet.framework.adonet)