How can I test if the DBI driver state is within a transaction?

Question

I've got a couple of methods that should be executed only in the case my DBI driver class is currently into a transaction to ensure data integrity. I'm looking to write something like this:

sub m{
  my ($self , $dbh ) = @_ ;
  unless( $dbh->isInTransaction()){
     die "Use this only within a transaction\n" ;
  }
  etc ...
}

From the docs for begin_work, I understand that begin_work will set AutoCommit to off during the time of the transaction and will set it back to 'on' on commit or rollback, but I wonder if testing for the AutoCommit attribute value is a safe way to implement isInTransaction.

Thanks for your help.

J.

Answer 1

Is your code database-independent? If so carefully read the section on AutoCommit because there are some important differences between databases depending on how they handle transactions. But if you already know your database deals with transactions in the way you need, then AutoCommit should be fine.

Answer 2

If you enable AutoCommit and start transactions with $dbh->begin_work, you can test to see if you're in a transaction:

if ($dbh->{BegunWork}) {

If you disable AutoCommit, DBI doesn't help much: you can only check for active statements connected to the database handle:

if ($dbh->{ActiveKids}) {

I've never had to check if there was a transaction active--it surprises me there's no support for it. You should probably track transactions yourself in a wrapper about DBI (or inject methods into DBI). Extending BegunWork to be useful with AutoCommit disabled looks like a core DBI fix.

Answer 3

If you are writing your own wrapper class, you can wrap begin_work and the other transaction methods so you can maintain your own state. Otherwise, you're depending on undocumented features or assumptions that may change, especially if you have to switch to another driver.