Transactions with DBIC

Overview

Transactions are an important feature of many database management systems. Transactions allow one to make changes to a data store in a reliable way that can gracefully handle failure. In short you are guaranteed that the changes take place in their entirety or not at all. In addition, transactions provide isolation for concurrent transactions to avoid data from being changed out from under you. This petit article demonstrates transactions using DBIx::Class, a kickass ORM for Perl.

Context

I was recently tasked with reserving phone numbers (DIDs) for people who would like to order PBXtra phone systems from Fonality. The operation must be atomic & handle multiple customers attempting to reserve DIDs simultaneously. This is a context ripe for using transactions. In order to achieve this goal, I chose is to use DBIx::Class and its txn_do method.

In short, I’ll define a reserve_dids method that will do one one of two things:

1. reserve all DIDs given
2. rollback the reservation and return a list of DIDs that caused the rollback

Here is the definition of reserve_dids (complete with POD):

       Args: ( $customer_id [$did1, $did2, .. $did3] )

    Returns: An ArrayRef of the DIDs that could NOT be Reserved.
             This may happen if the number is not in our database, 
             has already been purchased or if the number has been 
             reserved by another customer 
             (and the reservation has not expired). 
            
             An empty return list means a successful transaction
             Otherwise the transaction was rolled back and the list
             contains the troublesome DIDs.

sub reserve_dids {
    my $self         = shift;
    my $customer_id  = shift;
    my $dids         = shift;
    my $expiry_hours = shift;

    # Set expiry hours if given.  Will be used to compute the reservation_expiration.
    $self->expiry_hours($expiry_hours) if $expiry_hours;

    # Attempt to reserve each did. tracking unsuccessful dids
    my @dids_not_reserved = ();
    eval {
        $self->schema->txn_do(
            sub {
                foreach my $did (@{$dids}) {
                    my $provider_did_rs =
                      $self->schema->resultset('ProviderDid')
                      ->select_did_to_reserve($self->provider, $did, $customer_id);

                    # If we can't find the did in the proper state
                    # then we store it for later and move on to the next did.
                    if (!$provider_did_rs->count) {
                        push @dids_not_reserved, $did;
                        next;
                    }
                    
                    # We found the did in a reservable state. Let's now reserve it.
                    $provider_did_rs->update(
                        {
                            status      => 'reserved',
                            customer_id => $customer_id,
                            expiration  => $self->reservation_expiration,
                        }
                    );
                }

                die \@dids_not_reserved if @dids_not_reserved;
            }
        );
    };
    if ($@ =~ /Rollback failed/) {

        # Rollback failed
        die "ERROR: Rollback failed!\n";
    }

    # An empty return list means a successful transaction
    # Otherwise the transaction was rolledback and the list
    # contains the troublesome dids.
    return \@dids_not_reserved;
}

txn_do

The meat of the code is found within txn_do which wraps a transaction around the code. Let’s break down the parts of txn_do:

Attempt to SELECT a DID to Reserve

In the first part, we want to select a DID in a reservable state (if possible).

    my $provider_did_rs =  $self->schema->resultset('ProviderDid')
      ->select_did_to_reserve($self->provider, $did, $customer_id);

In this SELECT part, the resultset method select_did_to_reserve does the work. It is defined as:

sub select_did_to_reserve {
    my $self        = shift;
    my $provider    = shift;
    my $did         = shift;
    my $customer_id = shift;

    return $self->search(
        {
            provider => $provider,
            did      => $did,
            '-or'    => [
                { status => 'available' },
                {
                    status     => 'reserved',
                    expiration => { '<' => \"now()" }
                },
                {
                    status      => { '<>' => 'purchased' },
                    customer_id => $customer_id,
                },
            ],
        },
        { for => 'update' }
    );
}

Thus, $provider_did_rs is the result of selecting reservable dids. It will have 0 or 1 rows.

Check if we got a non-empty result

We check the count of the resultset which tells us if we were able to select the did to reserve. If our count is 0 then we put the DID on a list of unreservable dids for use later (to rollback the transaction).

    if (!$provider_did_rs->count) {
        push @dids_not_reserved, $did;
        next;
    }

Update if we have a result

If instead we were able to select the DID in a reservable state then we proceed to reserve it via the DBIC update statement.

    $provider_did_rs->update(
        {
            status      => 'reserved',
            customer_id => $customer_id,
            expiration  => $self->reservation_expiration,
        }
    );

Die if we couldn’t select any did

If we were not able to find a DID in a reservable state then it will show up in the list @dids_not_reserved. If this is non-empty we want to roll the transaction back so we die in that case.

    die \@dids_not_reserved if @dids_not_reserved;

Lock In

It’s worth pointing out that the { for => 'update' } portion of code locks the table on SELECT of DIDs. The intent is to lock them for reservation which is attempted on the subsequent UPDATE. Once the UPDATE has been performed, the lock will be released.

Rollback

A rollback is performed if at least one of the DIDS requested could be reserved. Note that no DID is reserved when a rollback takes place. i.e. it’s all or nothing.

When a rollback fails the variable $@ will contain the regex:

/Rollback failed/

In the case the transaction was successfully rolled back the eval message variable $@ will contain the information that caused the rollback.

Conclusion

In summary, one can wrap both database and non-database code in a transaction using txn_do of DBIx::Class. Further, one can trigger a rollback with a die statement which allows one to finely control rollback. In the end, a more robust API can be developed since we have some sort of guarantee of success or failure with a mixture of model and controller code.

¹ Shout out to: ribasushi, mst, frew, robkinyon, jnap etc on #dbix-class for guidance. All (mis)interpretations are mine.

My tags:

 

Popular tags: