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

Hi all -

I'm writing a module that receives a DBI database handle as input to my constructor. I'd like to be able to test if the handle is transaction-capable, so that I can conditionally wrap certain code in a transaction like so: 

 if ($self->{_can_do_transactions}) {
     local $dbh->{AutoCommit} = 0;
     eval {
         code_that_touches_db();
         $dbh->commit;
     };
     if ($@) {
         my $msg = $@;
         eval { $dbh->rollback };
         die $msg;
     }
 }
 else
 {
     code_that_touches_db();
 }

[download]

First question, is there a better way to do this?

UPDATE: Thanks to feedback, am now using something like this to handle transactions: 

 if ($self->{_can_do_transactions}) {

     # If RaiseError is true, begin_work() will:
     #     return true if a new transaction was started
     #     croak if already in a transaction
     #     croak if transactions not supported
     #
     local $dbh->{RaiseError} = 1;
     my $started_a_new_transaction = 0;
     eval { $started_a_new_transaction = $dbh->begin_work };

     eval {
         $code->(@_);
         $dbh->commit if $started_a_new_transaction;
     };
     if ($@)
     {
         my $msg = $@;
         eval { $dbh->rollback } if $started_a_new_transaction;
         die $msg;
     }
 }
 else
 {
     code_that_touches_db();
 }

[download]

 

Second, for testing the handle I am doing this in my constructor: 

 eval {
     local $dbh->{AutoCommit} = 0;
 };
 $self->{_can_do_transactions} = $@ ? 0 : 1;

[download]

which seems to work for the DBs that I have tried, but is it safe, e.g. what if the handle is already mid-transaction? Is there a better way?

UPDATE: Thanks to feedback, am now using something like this to test for transaction capability: 

    # If RaiseError is false, begin_work() will:
    #     return true if a new transaction was started
    #     return false if already in a transaction
    #     croak if transactions not supported
    #
    my $started_a_new_transaction = 0;
    eval {
        local $dbh->{RaiseError} = 0;
        $started_a_new_transaction = $dbh->begin_work;
    };
    $self->{_can_do_transactions} = $@ ? 0 : 1;
    eval { $dbh->rollback } if $started_a_new_transaction;

[download]

 

Third, with the AutoCommit test set up as above, I'm occasionally seeing my test harness report errors like: 

 closing dbh with active statement handles during global destruction

[download]

if I croak later on in the constructor. For reasons I can't determine, changing the test just by adding a line like so causes those error messages to go away: 

 eval {
     my $val = $dbh->{AutoCommit};   # makes error go away?
     local $dbh->{AutoCommit} = 0;
 };
 $self->{_can_do_transactions} = $@ ? 0 : 1;

[download]

Any ideas/comments much appreciated!

 

Larry

Replies are listed 'Best First'.
Re: How to safely test if a database handle is capable of transactions?
by ikegami (Patriarch) on Nov 18, 2008 at 21:51 UTC 
    eval {
   local $dbh->{AutoCommit} = 0;
};
$self->{_can_do_transactions} = $@ ? 0 : 1;

    [download]

    can be written more reliably as 

    $self->{_can_do_transactions} = eval {
    local $dbh->{AutoCommit} = 0;
    1
};

    [download]

    But That boils down to the following code, which you might want to use directly. 

    $self->{_can_do_transactions} = eval {
    $dbh->begin_work();
    $dbh->rollback()
};

    [download]

    I don't see the point of determining the capability in advance, so we end up with: 

    my $in_trans = eval { $dbh->begin_work() };
if (!eval {

   code_that_touches_db();

   $dbh->commit() if $in_trans;
   1
}) {
   my $msg = $@;
   eval { $dbh->rollback() } if $in_trans;
   die $msg;
}

    [download]

    Cleaner? 

    use Sub::ScopeFinalizer qw( scope_finalizer );

{
   my $in_trans = eval { $dbh->begin_work() };
   my $pending_rollback = scope_finalizer {
      local $@;
      eval { $dbh->rollback() } if $in_trans;
   };

   code_that_touches_db();

   $pending_rollback->disable();
   $dbh->commit() if $in_trans;
}

    [download]

    Cleaner still? 

    BEGIN {
   package TransactionMaybe;

   sub begin {
      my ($class, $dbh) = @_;
      my $in_trans = eval { $dbh->begin_work() };
      return bless([$dbh, $in_trans], $class);
   }

   sub commit {
      my ($self) = @_;
      my  $dbh                       =   $self->[0];
      our $in_trans; local *in_trans = \($self->[1]);
      $dbh->commit() if $in_trans;
      $in_trans = 0;
   }

   sub DESTROY {
      my ($dbh, $in_trans) = @$self;
      local $@;
      eval { $dbh->rollback() } if $in_trans;
   }

   $INC{'TransactionMaybe.pm'} = 1;
}

{
   my $transaction = TransactionMaybe->begin($dbh);
   code_that_touches_db();
   $transaction->commit();
}

    [download]

    However, it seems to me it's a bug to call call the same code_that_touches_db() whether transactions are available or not.

[reply]
[d/l]
[select]

      Thanks for the input ikegami, much appreciated - I will be making some changes as a result...

      Re: "I don't see the point of determining the capability in advance", that depends on whether "it's a bug to call call the same code_that_touches_db() whether transactions are available or not":

      I'm working under the assumption that if a user has a DB that is not transaction-capable (not sure what DBs those might be these days...) that they may decide to proceed at their own risk, assuming the risks are clearly laid out in the documentation. If that's the case, then there's a benefit to checking one time in the constructor, so I don't have to do any extra work while attempting (possibly many) transactions later on.

      Not entirely sure if that is the best approach... Maybe I could have the constructor die() if transactions are not possible, unless the user supplies a "no_transactions_are_ok" parameter...

[reply]
      ikegami: I'm just wondering:

      Why do you say: 
      eval {
  local $dbh->{AutoCommit} = 0;
};
$self->{_can_do_transactions} = $@ ? 0 : 1;

      [download]
      can be written more reliably as 
      $self->{_can_do_transactions} = eval {
  local $dbh->{AutoCommit} = 0;
  1
};

      [download]
      ?
[reply]
[d/l]
[select]
        If there's an eval without a local $@; in the code that handles $dbh->{AutoCommit} = $previous_value;, then $@ will get clobbered. Keep in mind that $dbh->{AutoCommit} is a tied variable.
[reply]
[d/l]
[select]
Re: How to safely test if a database handle is capable of transactions?
by fmerges (Chaplain) on Nov 18, 2008 at 21:19 UTC

    Hi,

    While it's not breaking encapsulation, as Limbic~Region corrected, using 'begin_work' is the recommended way, as you get blamed by the driver if it's not supported

    eval {
    $dbh->begin_work;
    # ...
    $dbh->commit;
};
if ($@) {
    $dbh->rollback;
    # If you want to be paranoid, check if it rolled back.
    # Look at manual: man DBI
}

    [download]

    Hope this helps you a bit

    Regards,

    Update: corrected about the encapsulation, thanks Limbic~Region

    fmerges at irc.freenode.net
[reply]
[d/l]
      fmerges,
      Where is encapsulation being broken? 
      $dbh->{AutoCommit} = 0;

      [download]
      The DBI is a tied interface and you are not really breaking encapsulation by doing this. This is a perfectly acceptable way of doing business with this module - just see the docs.

      You did indirectly answer the OP's question (begin_work). According to the docs - If the driver does not support transactions then when begin_work attempts to set AutoCommit off the driver will trigger a fatal error. You just need to work it a bit differently since the OP wants to know if transactions are supported prior to trying a transaction. This means checking the actual contents of $@ rather than just seeing if it is set.

      Cheers - L~R

[reply]
[d/l]
      The driver will die (or return an error if RaiseError => 0) whichever interface you use if transactions aren't unsupported.
[reply]

Back to Seekers of Perl Wisdom