Transactions

Transactions are important part of every database interaction. It allows you to do several queries in an atomic way. If something fails, everything is rolled back. All or nothing.

The following workflow is usually used when working with transactions in DBI:

• Set AutoCommit to 0
• Set RaiseError to 1
• Wrap code into eval
• Commit or rollback when checking $@ variable

Let's see how that looks in practice. The following transaction is going to be successful and all the users will be added.

my $dbh =
  DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});

$dbh->{AutoCommit} = 0;

my @users;

eval {
    my $sth = $dbh->prepare(
        'INSERT INTO `users` (firstname, lastname) VALUES (?, ?)');

    $sth->execute('Tom', 'Jones');
    $sth->execute('Bill', 'Tree');

    $dbh->commit;
    $dbh->{AutoCommit} = 1;

    1;
} or do {
    my $e = $@;

    $dbh->rollback;
    $dbh->{AutoCommit} = 1;

    warn "Error: $e\n";
};

my $sth = $dbh->prepare( 'SELECT firstname, lastname FROM users');
$sth->execute;

while (my @row = $sth->fetchrow_array) {
    my ($firstname, $lastname) = @row;

    say "$firstname $lastname";
}

The next example is going to fail and not even one user will be added.

my $dbh =
  DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});

$dbh->{AutoCommit} = 0;

my @users;

eval {
    my $sth = $dbh->prepare(
        'INSERT INTO `users` (firstname, lastname) VALUES (?, ?)');

    $sth->execute('Tom', 'Jones');
    $sth->execute('Bill', 'Tree');

    die 'Something bad happened';

    $dbh->commit;
    $dbh->{AutoCommit} = 1;

    1;
} or do {
    my $e = $@;

    $dbh->rollback;
    $dbh->{AutoCommit} = 1;

    warn "Error: $e\n";
};

my $sth = $dbh->prepare( 'SELECT firstname, lastname FROM users');
$sth->execute;

while (my @row = $sth->fetchrow_array) {
    my ($firstname, $lastname) = @row;

    say "$firstname $lastname";
}