Perl Scripts ·
Perl DBI Transactions: AutoCommit, begin_work, Rollback
By Manu Mathew, Technical Lead Engineer · Published 5 November 2014 · Last updated 5 October 2026

When two database changes must succeed or fail together, such as taking stock out of one warehouse and adding it to another, you need Perl DBI transactions. This note shows a safe pattern and the mistakes it avoids.
Quick answer
Connect with RaiseError => 1 and AutoCommit => 1. When several statements must succeed or fail together, call $dbh->begin_work, run them inside eval, and commit at the end. If anything dies, rollback. After commit or rollback, AutoCommit switches back on by itself.
What does AutoCommit do?
With AutoCommit on, every statement is committed as soon as it succeeds. That is fine for single, independent changes. It is not fine when, say, money leaves one account and must arrive in another: a failure between the two updates would leave the data half-changed.
How do I write a safe transaction?
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect('dbi:SQLite:dbname=shop.db', '', '',
{ RaiseError => 1, PrintError => 0, AutoCommit => 1 });
sub transfer {
my ($dbh, $from, $to, $amount) = @_;
$dbh->begin_work; # AutoCommit is off until commit or rollback
my $ok = eval {
$dbh->do('UPDATE accounts SET balance = balance - ? WHERE id = ?', undef, $amount, $from);
my ($balance) = $dbh->selectrow_array('SELECT balance FROM accounts WHERE id = ?', undef, $from);
die "Insufficient balance\n" if $balance < 0;
$dbh->do('UPDATE accounts SET balance = balance + ? WHERE id = ?', undef, $amount, $to);
$dbh->commit;
1;
};
if (!$ok) {
my $error = $@ || 'Unknown error';
eval { $dbh->rollback };
warn "Transfer failed, rolled back: $error";
return 0;
}
return 1;
}
Starting with balances of 100 and 50, a transfer of 30 succeeds and a transfer of 500 is rolled back, leaving 70 and 80. $dbh->{AutoCommit} is true again afterwards.
Why check eval’s return value instead of $@?
Returning 1 from the eval block and testing that is more reliable than testing $@, which can be cleared by code that runs during cleanup. Wrapping rollback in its own eval stops a failed rollback (for example, after a lost connection) from hiding the original error.
Frequently asked questions
Does every database support begin_work and rollback?
Most databases that DBI connects to do, including MySQL and MariaDB with InnoDB tables, PostgreSQL and SQLite. MySQL’s older MyISAM tables ignore transactions, so a rollback leaves their changes in place. Check the table engine before you rely on a rollback.
What is the difference between RaiseError and PrintError?
RaiseError makes a failed DBI call die, so eval can catch it and roll back. PrintError only prints a warning and lets the code carry on, which is how half-finished changes slip through. For transactions, turn RaiseError on and PrintError off.
Can I nest transactions with begin_work?
No. Calling begin_work while a transaction is open fails with “Already in a transaction”. If inner code needs its own rollback point, use the database’s savepoints (SAVEPOINT and ROLLBACK TO) where supported, or keep one transaction at the outer level.
Should a transaction include slow work such as sending email?
No. Keep transactions short and limited to database changes, because an open transaction can hold locks that block other users. Do slow work, such as emails or API calls, after the commit succeeds, so a rollback never leaves an email describing a change that didn’t happen.
Related notes: MySQL query notes; Perl files, folders, CSV and downloads; Perl CGI.pm basics; hiring a remote Perl developer.
Need help with Perl code?
If you have Perl code that needs fixing, extending or upgrading, I’m happy to take a look. I work remotely from Kozhikode, Kerala, with teams across India and elsewhere; Perl development and maintenance explains how I help. If you are deciding how to bring someone in, Hiring a remote Perl developer is a short checklist.
You can reach me through the contact section or email me at manu@manu.co.in. A few lines about the system and what you need is a good start.
An earlier version of this note reproduced part of a Tutorialspoint tutorial on Perl DBI. This version is rewritten in my own words, with new examples.
Sources: DBI documentation: Transactions, begin_work, AutoCommit; Tutorialspoint: Perl database access.