Just a Theory

By David E. Wheeler

How to Determine Your Transaction ID

Today I had reason to find out what PostgreSQL transaction I was in the middle of at any given moment in Bricolage. Why? I wanted to make sure that a single request was generating multiple transactions, instead of the normal one. It’s a long story, but suffice it to say that lengthy transactions were causing deadlocks. Read this if you’re really interested.

Anyway, here’s how to determine your current transaction using DBI. The query will be the same for any client, of course.

my $sth = $dbh->prepare(qq{
    SELECT transaction
    FROM   pg_locks
    WHERE  pid = pg_backend_pid()
            AND transaction IS NOT NULL
    LIMIT  1
});

$sth->execute;
$sth->bind_columns(\my $txid);
$sth->fetch;
print "Transaction: $txid\n";

Looking for the comments? Try the old layout.

More about…