Using transactions
From ClassDBI
I use this subroutine in my base CDBI class:
sub atomically {
my $class = shift;
my $action = shift; # coderef
local $class->db_Main->{AutoCommit}; # turn off AutoCommit for this block
my @result;
eval {
@result = wantarray ? $action->() : scalar($action->());
$class->dbi_commit;
};
if ($@) {
warn "atomically got error: $@";
my $commit_error = $@;
eval { $class->dbi_rollback };
die $commit_error;
}
die $@ if $@;
wantarray ? @result : $result[0];
}
Then I can say things like:
my @result = Some::Class->atomically(sub { ... });
and get automatic transaction wrapping.
A couple of caveats about the above... First, you probably want to use
$class->_croak()
instead of die to be consistent with Class::DBI's error handling. Secondly, I prefer to pass any variables needed by the code reference as arguments to avoid closures, which could maybe leak memory in a mod_perl environment depending on what you are doing. Generally, not a good thing for Web applications. I also just think it is a good practice to be explicit in what variables are needed for a transaction's code reference. I use something like the following implementation:
sub do_transaction {
my($class,$code,@args) = @_;
$class->_invalid_object_method('do_transaction()') if ref($class);
my @return_values = ();
my $dbh = $class->db_Main;
# Localize AutoCommit database handle attribute
# and turn off for this block.
local $dbh->{AutoCommit}; # Note: Leaks memory with Perl 5.6.1. Upgrade!
eval {
@return_values = $code->(@args);
$class->dbi_commit;
};
if ($@) {
my $error = $@;
eval { $class->dbi_rollback; };
if ($@) {
my $rollback_error = $@;
$class->_croak("Transaction aborted: $error; "
. "Rollback failed: $rollback_error\n");
} else {
$class->_croak("Transaction aborted (rollback "
. "successful): $error\n");
}
$class->clear_object_index;
return;
}
return(@return_values);
} #eosub--do_transaction
19 Oct 2005: Code edited above to add a call to clear_object_index() if the transaction fails, as suggested by BillMoseley. Bill also notes that you should call $obj->discard_changes() on any objects changed inside the transaction. To generalize this, it might be possible to add a loop over @args and discard_changes for any objects that can() do so. Comments?
Here's an example of how to use do_transaction()... Let's suppose you have an application which requires user authentication and group-based authorization. In addition to your Class::DBI-based classes for the users and groups tables, one of your (Controller) classes will probably need to create a user account and add the user to any specified groups. Assuming $self is an instance of this class, the following could be an excerpt from that class's method for creating user accounts:
my $user = MyClass::DBI->do_transaction(sub {
my($self) = @_;
my $model = MyClass::DBI::Users->create({
'user_id' => $self->user_id,
'email_address' => $self->email_address,
});
foreach my $group ($self->groups) {
$model->add_to_groups({ 'group_id' => $group });
}
return($model);
},$self);
-- EdwardSabol
22 Oct 04
Defining db_Main() in a class to define a dynamic connection means that 'Main' doesn't appear in the Ima::DBI db_names() list, and so it doesn't get committed or rolled back in calls to dbi_commit()/dbi_rollback().
A work-around (as of Ima::DBI v 0.33) is to put this in the class:
__PACKAGE__->_remember_handle('Main');
-- ChrisHutchinson
16 Aug 05
This code does not support nesting. Be very careful about what you call inside that code reference that you pass to atomically() or do_transaction(); if it calls another one, you will get an early commit.
-- SamVilain
19 Oct 05
Note concerning using PostgreSQL with the above and with AutoCommit on by default:
DBD::Pg prior to version 1.43_1 did not do a ping() correctly. The ping was a simple select which fails in some cases after another PostgreSQL operation fails. This caused Ima::DBI to think that the connection was closed and create a new connection. Then when $class->dbi_rollback is called it's called using a new $dbh. In that case you would see a message something like:
rollback ineffective with AutoCommit enabled at /usr/share/perl5/Ima/DBI.pm line 583.
because, of course, it's calling rollback on a new $dbh with AutoCommit enabled.
-- BillMoseley
20 Oct 05
In my do_transaction() I'm calling:
$class->clear_object_index;
before issuing a rollback. If the abort was caused by column data (e.g. text in an integer column), then you want to force the object to be re-read from the database.
May still need to call $obj->discard_changes to prevent a later "destroyed without saving changes" warning.
-- BillMoseley
08 Dec 05
In order to work around that "rollback ineffective" message when using DBD::Pg, you just need to ensure that you're using the same dbh to rollback with. This is what I'm using.
my $dbh = $class->db_Main;
local $dbh->{ AutoCommit };
my @retval = eval { $code->() };
if ( my $err = $@ ) {
eval { $dbh->rollback() };
die $err;
}
--Happygiraffe 17:17, 8 December 2005 (GMT)
09 Dec 05
I've found that you can make do_transaction() significantly more usable by exporting it and giving it a prototype. So instead of saying this:
My::DB->do_transaction( sub {
My::Table->insert( ... );
My::OtherTable->insert( ... );
} );
You end up with something that looks like this:
use My::DB qw( do_transaction );
do_transaction {
My::Table->insert( ... );
My::OtherTable->insert( ... );
}
Which reads much better. The changes to do_transaction itself are slight:
# Must export do_transaction() now.
use Exporter 'import';
our @EXPORT_OK = qw( do_transaction );
sub do_transaction (&) {
my $class = __PACKAGE__;
# The rest of the function is as before.
}
--Happygiraffe 10:50, 9 December 2005 (GMT)
