A Minimal Perl DBI Transaction Layer: Design, Boundaries, and Failure Modes

The problem this design solves

Most Perl scripts that talk to a database start as a few ad-hoc $dbh->do(...) calls and end up with half-committed writes, interpolated SQL, and credentials sitting in version control. This note describes the smallest design that avoids those three failure classes: a single module that owns the connection, exposes one narrow entry point, and makes every write an explicit transaction. It assumes DBI 1.6x with a modern driver such as DBD::Pg or DBD::mysql; the patterns are driver-neutral but the error text is not.

Requirements

• Every logical write is atomic: it commits fully or not at all.
• No SQL string is ever built by concatenating user input.
• Credentials live outside the source tree and outside the process's command line.
• Callers cannot reach the raw $dbh, so they cannot bypass the transaction wrapper.
• Failures are observable: a health check and error logging exist from day one.

The smallest suitable design

One module, one connection per process, one public function. Anything smaller loses the trust boundary; anything larger (an ORM, a connection pool manager) is premature for a single-process script or a preforking worker that opens one handle per child.

package App::DB;
use strict;
use warnings;
use DBI;

my $dbh;

sub _connect {
    my $dsn  = $ENV{APP_DSN}  or die "APP_DSN not set";
    my $user = $ENV{APP_USER} or die "APP_USER not set";
    my $pass = $ENV{APP_PASS} // '';
    $dbh = DBI->connect($dsn, $user, $pass, {
        RaiseError           => 1,
        PrintError           => 0,
        AutoCommit           => 0,
        pg_enable_utf8       => 1,   # driver-specific; drop for mysql
    });
}

sub run_txn {
    my ($coderef) = @_;
    _connect() unless $dbh && $dbh->ping;
    my @result;
    eval {
        @result = $coderef->($dbh);
        $dbh->commit;
    };
    if (my $err = $@) {
        eval { $dbh->rollback };
        die "transaction failed: $err";
    }
    return @result;
}

sub healthcheck {
    _connect() unless $dbh;
    return eval { $dbh->selectrow_array('SELECT 1') } ? 1 : 0;
}

sub DESTROY { $dbh->disconnect if $dbh }
1;

Callers use it like this:

use App::DB;
App::DB::run_txn(sub {
    my $dbh = shift;
    my $sth = $dbh->prepare(
        'INSERT INTO orders (customer_id, total) VALUES (?, ?)'
    );
    $sth->execute($customer_id, $total);
    $dbh->do('UPDATE stock SET qty = qty - 1 WHERE sku = ?', undef, $sku);
});

The key decisions:

• AutoCommit = 0 forces explicit commit. If the process dies mid-transaction, the database rolls back on disconnect, which is the safe default.
• RaiseError = 1, PrintError = 0 turns DB errors into exceptions the eval can catch, instead of warnings that let execution continue in a half-failed state.
• Placeholders everywhere. The ? bind values are sent separately from the SQL text, so input cannot alter the statement structure. As a side benefit, the driver can cache the prepared statement per connection.
• Reconnect via $dbh->ping handles idle connections dropped by the server or a firewall. ping is cheap but not free; calling it once per transaction is acceptable, calling it per statement is not.

Trust and data boundaries

The boundary is the run_txn signature. Callers supply a coderef and bind values; they never see connection parameters and cannot issue a COMMIT early. Credentials arrive through environment variables (APP_DSN, APP_USER, APP_PASS), which keeps them out of source control and out of ps output — unlike command-line arguments, environment of other users' processes is not world-readable on Linux. For stricter setups, point the module at a 0600-permission config file readable only by the service account instead.

One boundary this design deliberately does not enforce: SQL correctness. Callers still write their own SQL inside the coderef. That is a conscious trade — a full query builder would double the module's size for little integrity gain, since placeholders already cover injection.

Operational checks

Three checks cover the realistic failure surface:

1. Health check. Expose App::DB::healthcheck() behind your application's /healthz route (or a cron-run one-liner: perl -MApp::DB -e 'exit App::DB::healthcheck ? 0 : 1'). Alert on consecutive failures, not single ones — a single dropped packet should not page anyone.
2. Rollback verification. In a test database, run a transaction that inserts a row and then dies. Confirm the row is absent afterwards. This proves the eval/rollback path actually works, which the happy path never exercises.
3. Leak check. In a long-running process, watch the database's connection count (pg_stat_activity or SHOW PROCESSLIST) while the app runs. It should hold steady at one per worker. Growth means a code path is connecting without going through this module.

Failure modes and when to change the design

• Nested transactions. If a caller's coderef itself calls run_txn, the inner commit releases the outer transaction's work early. The fix is either a documented "no nesting" rule or savepoints (SAVEPOINT/RELEASE in Postgres) once nesting becomes a real need.
• Deadlocks under concurrency. Two workers updating the same rows in different orders will deadlock; the database kills one and RaiseError surfaces it. The minimal response is a bounded retry (two or three attempts with jitter) around run_txn for idempotent operations. Non-idempotent writes should not be retried blindly.
• Many short-lived processes. If the workload becomes CGI-style (new process per request), per-process connect cost dominates. That is the point to add connection pooling — for example Apache::DBI under mod_perl or a proxy like PgBouncer — not before.
• Multi-statement reads needing consistency. This design gives each transaction the driver's default isolation level. If a report must see a stable snapshot across several SELECTs, raise the level explicitly (SET TRANSACTION ISOLATION LEVEL REPEATABLE READ) inside that transaction rather than globally.

The design holds as long as one process needs one connection and transactions are independent. Crossing either line — pooling, nesting, or distributed writes — is a signal to grow the module, not to patch around it at call sites.