Committing database transactions with DESTROY

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

I have a base class, Generic::Database, which handles all interactions with MS SQL Server. The actual SQL is generated by classes that inherit from Generic::Database. I am using the DBI module and have autocommit set to false. Most operations on the database are going to be simple updates of one record. For these operations, I decided to handle committing the transaction with the DESTROY method in the base class (these are ISAPI scripts and therefore not persistent database connections). The DESTROY method looks like this:

sub DESTROY {
    my $self = shift;
    if ( $self->{ _error } ) {
        cluck "There was a problem with the transaction: $self->{ _err
+_str }";
        $self->{ _dbh }->rollback;
    } else {
        $self->{ _dbh }->commit;
    }
    $self->{ _dbh }->disconnect;
}
[download]

For most of the program, this works fine. However, for some (not all) update sql statements, the DESTROY method is never triggered and I need to explicitly call $dbh->commit for them:

sub update_admin_message {
    my ( $self, $data_hash ) = @_;
    my ( $fields, $values ) = $self->_format_update_data( $data_hash )
+;
    my $sql = "UPDATE adminMessage SET $fields";
    my $data = $self->_update_database( $sql, $values );
    $self->{ _dbh }->commit if ! $self->{ _error };
}
[download]

The update_admin_message is in the Generic::Database::Modify class which inherits from Generic::Database. If I take out the last line of that method, the update silently fails because DESTROY is never called. Any ideas?

Cheers,
Ovid

Vote for paco!

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

Comment on Committing database transactions with DESTROY Select or Download Code

Replies are listed 'Best First'.
Re (tilly) 1: Committing database transactions with DESTROY by tilly (Archbishop) on Oct 16, 2001 at 21:21 UTC
This is probably the same global destruction problem I described at Re (tilly) 1: Tie & Destroy, OOP. In that thread I suggested a general solution which you can find at Class::FlyweightWrapper which (with considerable overhead) allows you to get around the issue in a different way (albeit with considerable overhead).	[reply]
(Ovid) Re(2) 1: Committing database transactions with DESTROY by Ovid (Cardinal) on Oct 16, 2001 at 22:11 UTC
Reading through those threads, what I'm getting is the impression that `Generic::Database::DESTROY` can be garbage collected before `Generic::Database::Modify` objects are being collected, thus ensuring that there is no `DESTROY` method to inherit from. Is this correct? If so, this would explain some of the inconsistent results that I have been getting. Cheers, Ovid Vote for paco! Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.	[reply]
Re (tilly) 3: Committing database transactions with DESTROY by tilly (Archbishop) on Oct 16, 2001 at 23:30 UTC
I think it more likely that your database handle is cleaned up before you destroy $self. Which makes your commit fail.	[reply]
Re: Committing database transactions with DESTROY by AidanLee (Chaplain) on Oct 16, 2001 at 20:40 UTC
I have to wonder: _why_ is DESTROY not being called for this query and others like it? Since these are not persistent db connections, at some point the object will go out of scope and DESTROY should be called. Is it that you have sql calls that need commit()'ing before the object goes out of scope? Or is it that the script may croak before DESTROY gets called? If it's the first case, then explicitly calling commit() (or a sub to do it for you) is the way to go. If it's the latter, perhaps you need to hijack the SIGDIE long enough to make sure any commits that need doing get done.	[reply]
Re: Committing database transactions with DESTROY by perrin (Chancellor) on Oct 16, 2001 at 21:01 UTC
It might work if you undef the objects explicitly, but at that point the DWIM magic is gone. In general, I think it's better to be explicit about hairy things like transactions. I would make the part of the program that calls the data objects handle creating and committing the transaction.	[reply]
Re: Committing database transactions with DESTROY by Hero Zzyzzx (Curate) on Oct 16, 2001 at 20:49 UTC
I'm not really an expert on this, but here goes: DESTROY only gets called when something is about to be garbage-collected, right? Are there references/globals that need to be "undef'd" that aren't undef'd in some cases, that are hanging around and keeping a variable from being garbage collected, and consequently having DESTROY called on it? -Any sufficiently advanced technology is indistinguishable from doubletalk.	[reply]