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.