Using transactions

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.

-- RandalSchwartz

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</tt> in a class to define a dynamic connection means that 'Main' doesn't appear in the Ima::DBI db_names</tt> list, and so it doesn't get committed or rolled back in calls to dbi_commit</tt>/dbi_rollback</tt>.

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</tt> or do_transaction</tt>; 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</tt> on by default:

DBD::Pg prior to version 1.43_1 did not do a ping</tt> correctly. The ping was a simple select</tt> 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</tt>. 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</tt> 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</tt> 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)