DBIx::Transaction::db - Database handle that is aware of nested transactions


See DBIx::Transaction


When you connect to a database using DBIx::Transaction, your database handle will be a DBIx::Transaction::db object. These objects keep track of your transaction state, allowing for transactions to occur within transactions, and only sending "commit" or "rollback" instructions to the underlying database when the outermost transaction has completed. See DBIx::Transaction for a more complete explanation.


Overridden Methods

The following methods are overridden by DBIx::Transaction::db:


Start a transaction.

If there are no transactions currently running, begin_work will check if AutoCommit is enabled. If it is enabled, a begin_work instruction is sent to the underlying database layer. If AutoCommit is disabled, we assume that the database has already started a transaction for us, and do nothing. This means that you must always use begin_work to start a transaction, even if AutoCommit is enabled!

If there is a transaction started, begin_work simply records that a nested transaction has started.

begin_work returns the result of the database's begin_work call if it makes one; otherwise it always returns true.


Abort a transaction.

If there are no sub-transactions currently running, rollback will issue the rollback call to the underlying database layer.

If there are sub-transactions currently running, rollback notes that the nested transaction has been aborted.

If there is no transaction running at all, rollback will raise a fatal error.


If there are sub-transactions currently running, commit records that this transaction has completed successfully and does nothing to the underlying database layer.

If there are no sub-transactions currently running, commit checks if there have been any transaction errors. If there has been a transaction error, commit raises an exception. Otherwise, a commit call is issued to the underlying database layer.

If there is no transaction running at all, commit will raise a fatal error. This error will contain a full stack trace, and should also contain the file names and line numbers where any rollbacks or query failures happened.


Calls do() on your underlying database handle. If an error occurs, this is recorded and you will not be able to issue a commit for the current transaction.

Extra Methods

The following method is provided for convienence in setting up database transactions:

transaction($coderef[, $tries[, $when]])

Execute the code contained inside $coderef within a transaction. $coderef is expected to return a scalar value. If $coderef returns true, the transaction is committed. If $coderef returns false or raises a fatal error, the transaction is rolled back. The return value is the same as what is returned by $coderef.

This method is supplied to make it easier to create nested transactions out of many small transactions. Example:

  sub get_max_id {
    my $dbh = shift;
    # this will roll back if it can't get MAX(num)
    $dbh->transaction(sub {
      if(my($id) = $dbh->selectrow_array("SELECT MAX(num) FROM foo")) {
        return $id;
      } else {
  sub do_something {
    my($dbh, $num) = @_;
    $dbh->transaction(sub {
      $dbh->do("UPDATE foo SET bar = bar + 1 WHERE num = $num");
  sub do_many_things {
    my $dbh = shift;
    # if any of these sub-transactions roll back, the whole thing will roll
    # back. Try repeating the transaction up to 5 times.
    $dbh->transaction(sub {
        do_something($dbh, 1) &&
        do_something($dbh, 2) &&
        (my $id = get_max_id($dbh))
      ) {
        return do_something($dbh, $id);
      } else {
    }, 5);
Re-trying transactions

If $tries is specified, the transaction will be tried up to $tries times before giving up. (Default: 1) Specify a negative value to re-try forever.

Note: only the outermost transaction may attempt retries. This is because if there is one failure within a transaction, the entire transaction fails -- so any retries in nested transactions would have to fail, by virtue of the previous attempt failing. If you try to set up retries from inside a nested transaction, this will die with the error "Transaction retry flow may only be set on the outermost transaction".

$when is an optional code reference that can be used to decide if a transaction should be retried or not. It will be passed the following arguments:

The database handle ($dbh)
The return value of the transaction
The exception raised by the transaction, if any ($@)
How many tries are left

If the code reference returns true, the transaction will be run again. If it returns false, the $dbh-transaction()> will finish, either returning a value, or raising an exception if one was caused by the last execution of $coderef.

The default handler for $when is simply:

  sub {
    my($dbh, $return_value, $return_exception, $tries) = @_;
    return $tries && ($return_exception || !$return_value);

Other Methods

The following methods are used by the overridden methods. In most cases you won't have to use them yourself.


Returns an integer value representing how deeply nested our transactions currently are. eg; if we are in a top-level transaction, this returns "1"; if we are 4 transactions deep, this returns "4", if we are not in a transaction at all, this returns "0". In some extreme cases this may be used to bail out of a nested transaction safely, as in:

  $dbh->rollback while $dbh->transaction_level;

But I would suggest that you structure your code so that each transaction and sub-transaction bails out safely instead, as that's a lot easier to trace and debug with confidence.


Returns a true value if a sub-transaction has rolled back, false otherwise. Again, you could use this to back out of a transaction safely, but I'd suggest just writing your code to handle this case on each transaction level instead.


For debugging; If DBI's trace level is 3 or over, emit the current values of all of the internal variables DBIx::Transaction uses to track it's transaction states.


Indicate that we have started a sub transaction by increasing transaction_level by one. This is used by the begin_work override and should not be called directly.


Indicate that we have finished a sub transaction by decrementing transaction_level by one. If this results in a negative number (meaning more transactions have been commited/rolled back than started), dec_transaction_level throws a fatal error. This is used by the commit and rollback methods and should not be called directly.


Indicate that a sub-transaction has failed and that the entire transaction should not be allowed to be committed. This is done automatically whenever a sub-transaction issues a rollback. Optional parameters are the package, filename, and line where the transaction error occured. If provided, they will be used in error messages relating to the rollback.


Clear the transaction error flag. This flag is set whenever a sub-transaction issues a rollback, and cleared whenever the outermost transaction issues a rollback.


Close the outermost transaction by calling $method ("commit" or "rollback") on the underlying database layer and resetting the DBIx::Transaction state. This method is used by the commit and rollback methods and you shouldn't need to use it yourself, unless you wanted to forcibly bail out of an entire transaction without calling rollback repeatedly, but as stated above, that's a bad idea, isn't it?


DBI, DBIx::Transaction


Tyler "Crackerjack" MacDonald <>


Copyright 2005 Tyler MacDonald This is free software; you may redistribute it under the same terms as perl itself.