Database transactions help ensure the integrity and consistency of your data. In brief, a transaction wraps a group of related data operations which must function together or the entire group will fail. The classic example is a purchase made by check: the amount of the payment must be added to the recipient's bank account and removed from the payer's bank account together. If the database crashes, or if either operation fails, the entire transaction will fail, and the database will be unaffected.

Here I will present a few (contrived) examples where they are useful.

Often a Model operation, in the sense of business logic, requires a group of closely related (or dependent) database operations. These should be atomic. That is, if any single operation in the group fails, then the whole group of operations will fail and your data remains consistent.

Suppose you wrote some code such as:

    sub create_user : Local {
        my ($self, $c) = @_;

        # we love hash slices
        my ($username, $first_name, $last_name, $token_id) =
            @{ $c->req->params }{qw/username first_name last_name token_id/};

        unless ($c->model('Token')->validate($token_id)) {
            $c->flash->{error} = "Bad token: $token_id";
            $c->detach('/error');
        }

        my $user = $c->model('DB::User')->create({
            username => $username,
            first_name => $first_name,
            last_name => $last_name,
        });

        $user->add_to_tokens({ token_id => $token_id });

        $c->flash->{message} = "Created user ${username}!";
    }

This looks perfectly reasonable, but there are two operations here, one which creates a user entry, and one that creates a token entry for it.

What could possibly go wrong between them? Lots of things, such as the web server going down in flames, the database server crashing, alien invasion, and so on. While the chances of this are small, repairing erroneous data in a database is a painful, expensive, and hazardous process.

Also, we had to use an unnatural control flow here: normally we'd validate the token before it is created, rather than before the user is created.

Enter txn_do, from DBIx::Class::Schema.

    $c->model('DB')->txn_do(sub {
        my $user = $c->model('DB::User')->create({
            username => $username,
            first_name => $first_name,
            last_name => $last_name,
        });

        $c->model('Token')->validate($token_id) or die "Bad token: $token_id";

        $user->add_to_tokens({ token_id => $token_id });
    });

Now that the operation is atomic, your user entries as well as any related entries are guaranteed to be consistent, and we are using a more natural control flow.

Any exceptions from txn_do are re-thrown and a ROLLBACK is issued, leaving the database untouched by the code enclosed by the transaction.

Exceptions can be cleaned up in your end handler, by getting them from $c->error, or using something like Catalyst::Action::RenderView::ErrorHandler. However, in some cases you may want to wrap them in an eval (or one of Try::Catch, Try::Tiny) to detach in order to short-circuit a chain for example.

It is also recommended to factor out database code into the relevant DBIx::Class::ResultSet methods. ResultSet classes go into the lib/MyApp/Schema/ResultSet directory (for example), next to the Result directory, and inherit from DBIx::Class::ResultSet.

A different control flow for transactions is possible with txn_scope_guard, from DBIx::Class::Storage.

With this method, an object is created and a transaction is started; if the object goes out of scope before ->commit is called on it, via an exception or any other means, a ROLLBACK will be issued.

This can be useful if you don't want to deal with catching exceptions from txn_do. Another reason you might choose to use txn_scope_guard over txn_do is that txn_do will attempt to reconnect to the database and re-run your code if you lose your connection; txn_scope_guard does not have this overhead.

Here's the previous example using txn_scope_guard:

    sub create_user : Local {
        my ($self, $c) = @_;

        my ($username, $first_name, $last_name, $token_id) =
            @{ $c->req->params }{qw/username first_name last_name token_id/};

        my $scope_guard = $c->model('DB')->txn_scope_guard;

        my $user = $c->model('DB::User')->create({
            username => $username,
            first_name => $first_name,
            last_name => $last_name,
        });

        unless ($c->model('Token')->validate($token_id)) {
            $c->flash->{error} = "Bad token: $token_id";
            $c->detach('/error');
        }

        $user->add_to_tokens({ token_id => $token_id });

        $c->flash->{message} = "Created user ${username}!";

        $scope_guard->commit;
    }

Here on $c->detach the $scope_guard goes out of scope, and a ROLLBACK is issued, as well as on any exception. The changes are committed at the end of the action.

Many databases support a feature called "savepoints", which are nested transactions. They allow you to roll back a group of operations that are part of a larger group of operations.

DBIx::Class supports savepoints for the MySQL, Oracle, Postgres, MSSQL and Sybase databases.

You have to add auto_savepoint => 1 to your connect_info to enable this feature: see DBIx::Class::Storage::DBI for details.

    sub validate_batch : Local {
        my ($self, $c) = @_;
        my @urls = split "\n", $c->req->param('urls');

        $c->model('DB')->txn_do(sub {
            my $batch = $c->user->add_to_batches({});
            my $validated_count = 0;

            for my $url (@urls) {
                try {
                    $c->model('DB')->txn_do(sub {
                        my $url = $c->model('DB::Url')->find_or_create({ url => $url });
                        my $result = $c->model('Validator')->validate($url);
                        $batch->add_to_urls({ url => $url->id, result => $result });
                        $validated_count++;
                    });
                } catch {
                    push @{ $c->flash->{errors} }, $_;
                }
            }

            die "Could not validate any URLs" unless $validated_count;

            $batch->update({ url_count => $validated_count });
        });
    }

In this example, we are validating a batch of URLs. If any single URL fails, it will not be added to any tables in the database; if they all fail, nothing will be added to the database.

As you can see, the DBIx::Class facilities for database transactions can make your code safer and simpler.

Caelum: Rafael Kitover <rkitover@cpan.org>