Total rollback on nested transactions

Question

I really like this NestedPDO solution for Yii but I need some different transaction handling.

I want to commit my nested transactions only if all nested transactions could be commited and if ONE transaction does a rollback all transactions should be rolled back.

How can I do that?

My try of changing the rollBack function which didn't work:

public function rollBack() {
    $this->transLevel--;

    if($this->transLevel == 0 || !$this->nestable()) {
        parent::rollBack();
    } else {

        $level = $this->transLevel;
        for($level; $level>1; $level--){
            $this->exec("ROLLBACK TO SAVEPOINT LEVEL{$this->constantlevel}");
        }
        //parent::rollBack();
    }
}

I was thinking of adapting the NestedPDO: In function commit() do a commit only on the outermost transaction and in function rollBack() do a rollback to the outermost transaction no matter which sub transaction caused the rollback. But I could not get it done...

I'm using MySQL and InnoDB tables and I'm not sure about autocommit but when echoing the value of autocommit within a transaction I always get the value 1 which should mean autocommit is on but within a transaction autocommit should be set to 0. I'm not sure whether this is the cause why a whole rollback does not work for me?

Answer 1

IMHO, the idea of simulating "nested transactions" in application code is an anti-pattern. There are numerous anomaly cases that are impossible to solve in the application (see my answer to https://stackoverflow.com/a/319939/20860).

In PHP, it's better to keep it simple. Work is organized naturally into requests, so use the request as the transaction scope.

• Start a transaction at the controller level, before you call any model classes.
• Let models throw exceptions if anything goes wrong.
• Catch the exception at the controller level, and rollback if necessary.
• Commit if no exception is caught.

Forget about all the nonsense about transaction levels. Models should not be starting, committing, or rolling back any transactions.

Answer 2

Based on the answer of @RandomSeed I've created a 'drop in' for default Yii transaction handling:

$connection = Yii::app()->db;
$transaction=$connection->beginTransaction();
try
{
   $connection->createCommand($sql1)->execute();
   $connection->createCommand($sql2)->execute();
   //.... other SQL executions
   $transaction->commit();
}
catch(Exception $e)
{
   $transaction->rollback();
}

This is my SingleTransactionManager class:

class SingleTransactionManager extends CComponent 
{
    // The current transaction level.
    private $transLevel = 0;

    // The CDbConnection object that should be wrapped
    public $dbConnection;

    public function init()
    {
        if($this->dbConnection===null)
            throw new Exception('Property `dbConnection` must be set.');

        $this->dbConnection=$this->evaluateExpression($this->dbConnection);
    }
    // We only start a transaction if we're the first doing so
    public function beginTransaction() {
        if($this->transLevel == 0) {
            $transaction = parent::beginTransaction();
        } else {
            $transaction = new SingleTransactionManager_Transaction($this->dbConnection, false);
        }
        // always increase transaction level:
        $this->transLevel++;

        return $transaction;
    }

    public function __call($name, $parameters)
    {
        return call_user_func_array(array($this->dbConnection, $name), $parameters);
    }
}

class SingleTransactionManager_Transaction extends CDbTransaction
{
    // boolean, whether this instance 'really' started the transaction
    private $_startedTransaction;

    public function __construct(CDbConnection $connection, $startedTransaction = false)
    {
        $this->_startedTransaction = $startedTransaction;
        parent::__construct($connection);
        $this->setActive($startedTransaction);
    }

    // We only commit a transaction if we've started the transaction
    public function commit() {
        if($this->_startedTransaction)
            parent::commit();
    }

    // We only rollback a transaction if we've started the transaction
    // else throw an Exception to revert parent transactions/take adquate action
    public function rollback() {
        if($this->_startedTransaction)
            parent::rollback();
        else
            throw new Exception('Child transaction rolled back!');
    }
}

This class 'wraps' the main database connection, you should declare it as component like this in your config:

'components'=>array(

    // database
    'db'=>array(
        'class' => 'CDbConnection',
        // using mysql
        'connectionString'=>'....',
        'username'=>'...',
        'password'=>'....',
    ),

    // database
    'singleTransaction'=>array(
        'class' => 'pathToComponents.db.SingleTransactionManager',
        'dbConnection' => 'Yii::app()->db'
    )

Note that the dbConnection property should be an expression to the master database connection. Now, when nesting transactions in nested try catch blocks, you can create an error in for example nested transaction 3, and the ones on 1 and 2 are rolled back also.

Test code:

$connection = Yii::app()->singleTransaction;

$connection->createCommand('CREATE TABLE IF NOT EXISTS `test_transactions` (
  `number` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;')->execute();

$connection->createCommand('TRUNCATE TABLE `test_transactions`;')->execute();

testNesting(4, 3, 1);

echo '<br>';
echo 'Rows:';
echo '<br>';
$rows = $connection->createCommand('SELECT * FROM `test_transactions`')->queryAll();
if($rows)
{
    foreach($rows as $row)
    {
        print_r($row);
    }
}
else
    echo 'Table is empty!';

function testNesting(int $total, int $createErrorIn = null, int $current = 1)
{
    if($current>=$total)
        return;

    $connection = Yii::app()->singleTransaction;
    $indent = str_repeat('&nbsp;', ($current*4));

    echo $indent.'Transaction '.$current;
    echo '<br>';
    $transaction=$connection->beginTransaction();
    try
    {
        // create nonexisting columnname when we need to create an error in this nested transaction
        $columnname = 'number'.($createErrorIn===$current ? 'rr' : '');
        $connection->createCommand('INSERT INTO `test_transactions` (`'.$columnname.'`) VALUES ('.$current.')')->execute();

        testNesting($total, $createErrorIn, ($current+1));

        $transaction->commit();
    }
    catch(Exception $e)
    {
        echo $indent.'Exception';
        echo '<br>';
        echo $indent.$e->getMessage();
        echo '<br>';
        $transaction->rollback();
    }
}

Results in the following output:

    Transaction 1
        Transaction 2
            Transaction 3
            Exception
            CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'numberrr' in 'field list'. The SQL statement executed was: INSERT INTO `test_transactions` (`numberrr`) VALUES (3)
        Exception
        Child transaction rolled back!
    Exception
    Child transaction rolled back!

Rows:
Table is empty! 

Answer 3

If you want the whole transaction be rolled back automatically as soon as an error occurs, you could just re-throw the exception from B's exception handler when called from some specific locations (eg. from A()):

function A(){
   ...
   $this->B(true);
   ...
}

/*
* @param B boolean Throw an exception if the transaction is rolled back
*/
function B($rethrow) {
    $transaction=Yii::app()->db->beginTransaction();
    try {
        //do something
        $transaction->commit();
    } catch(Exception $e) {
        $transaction->rollBack();
        if ($rethrow) throw $e;
    }
}

Now I understand you actually just want your wrapper to detect if a transaction is already in progress, and in this case not start the transaction.

Therefore you do not really need the NestedPDO class. You could create a class like this instead:

class SingleTransactionManager extends PDO {
    private $nestingDepth = 0;

    public function beginTransaction() {
        if(!$this->nestingDepth++ == 0) {
            parent::beginTransaction();
        } // else do nothing
    }
    public function commit() {
        $this->nestingDepth--;
        if (--$this->nestingDepth == 0) {
            parent::commit();
        } // else do nothing
    }

    public function rollback() {
        parent::rollback();
        if (--$this->nestingDepth > 0) {
            $this->nestingDepth = 0;
            throw new Exception(); // so as to interrupt outer the transaction ASAP, which has become pointless
        }

    }
}